case when x < y then a when x = y then b else c end
case XYZ when 'foo' then 'moo' else 'bar' end
The following little SQL script demonstrates the use of CASE WHEN. create table test_case_when (
a varchar2(5),
b varchar2(5)
);
insert into test_case_when values ('*','*');
insert into test_case_when values ('+','+');
insert into test_case_when values ('-','-');
insert into test_case_when values ('.','.');
select a,
case
when b = '*' then 'star'
when b = '+' then 'plus'
when b = '-' then 'minus'
else '????'
end
from test_case_when;This select statement produces the following output:A CASEW
----- -----
* star
+ plus
- minus
. ????drop table test_case_when; ORACLE官方文档说明： CASE
Expressions
There are two types of expressions used in CASE
statements: simple and searched. These expressions correspond to the type of
CASE statement in which they are used. See .
Simple CASE expression
A simple
CASE
expression selects a result from one or more
alternatives, and returns the result. Although it contains a block that might
stretch over several lines, it really is an expression that forms part of a
larger statement, such as an assignment or a procedure call. The
CASE
expression uses a
selector,
an expression whose value determines which alternative to return.
A
CASE
expression has the form illustrated in . The selector (
grade
) is followed
by one or more
WHEN
clauses, which are checked sequentially. The
value of the selector determines which clause is evaluated. The first
WHEN
clause that matches the value of the selector determines the
result value, and subsequent
WHEN
clauses are not evaluated. If
there are no matches, then the optional
ELSE
clause is
performed.
Example 2-26 Using the WHEN Clause With a CASE
Statement
grade CHAR(1) := 'B';
appraisal VARCHAR2(20);
BEGIN
appraisal :=
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
WHEN 'D' THEN 'Fair'
WHEN 'F' THEN 'Poor'
ELSE 'No such grade'
END;
DBMS_OUTPUT.PUT_LINE('Grade ' || grade || ' is ' || appraisal);
END;
/
The optional
ELSE
clause works similarly to the
ELSE
clause in an
IF
statement. If the value of the
selector is not one of the choices covered by a
WHEN
clause, the
ELSE
clause is executed. If no
ELSE
clause is provided
and none of the
WHEN
clauses are matched, the expression returns
NULL
.
Searched CASE Expression
A searched
CASE
expression lets you test different conditions
instead of comparing a single expression to various values. It has the form
shown in .
A searched
CASE
expression has no selector. Each
WHEN
clause contains a search condition that yields a
BOOLEAN
value, so you can test different variables or multiple
conditions in a single
WHEN
clause.
Example 2-27 Using a Search Condition With a CASE
Statement
DECLARE
grade CHAR(1) := 'B';
appraisal VARCHAR2(120);
id NUMBER := 8429862;
attendance NUMBER := 150;
min_days CONSTANT NUMBER := 200;
FUNCTION attends_this_school(id NUMBER) RETURN BOOLEAN IS
BEGIN RETURN TRUE; END;
BEGIN
appraisal :=
CASE
WHEN attends_this_school(id) = FALSE THEN 'N/A - Student not enrolled'
-- Have to test this condition early to detect good students with bad attendance
WHEN grade = 'F' OR attendance < min_days
THEN 'Poor (poor performance or bad attendance)'
WHEN grade = 'A' THEN 'Excellent'
WHEN grade = 'B' THEN 'Very Good'
WHEN grade = 'C' THEN 'Good'
WHEN grade = 'D' THEN 'Fair'
ELSE 'No such grade'
END;
DBMS_OUTPUT.PUT_LINE('Result for student ' || id || ' is ' || appraisal);
END;
/
The search conditions are evaluated sequentially. The
BOOLEAN
value of each search condition determines which
WHEN
clause is
executed. If a search condition yields
TRUE
, its
WHEN
clause is executed. After any
WHEN
clause is executed, subsequent
search conditions are not evaluated. If none of the search conditions yields
TRUE
, the optional
ELSE
clause is executed. If no
WHEN
clause is executed and no
ELSE
clause is
supplied, the value of the expression is
NULL
.
