CASE WHEN case when x = y then a else b endcase 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 theELSE clause in an IF statement. If the value of the
selector is not one of the choices covered by a WHEN clause, theELSE clause is executed. If no ELSE clause is provided
and none of the WHEN clauses are matched, the expression returnsNULL.
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. EachWHEN clause contains a search condition that yields aBOOLEAN 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 yieldsTRUE, the optional ELSE clause is executed. If noWHEN clause is executed and no ELSE clause is
supplied, the value of the expression is NULL.