case when x = y then a else b end 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;
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 "Using CASE Statements".
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. TheCASE
expression uses a selector, an expression whose value determines which alternative to return.A
CASE
expression has the form illustrated in Example 2-26. The selector (grade
) is followed by one or moreWHEN
clauses, which are checked sequentially. The value of the selector determines which clause is evaluated. The firstWHEN
clause that matches the value of the selector determines the result value, and subsequentWHEN
clauses are not evaluated. If there are no matches, then the optionalELSE
clause is performed.Example 2-26 Using the WHEN Clause With a CASE Statement
DECLARE 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 anIF
statement. If the value of the selector is not one of the choices covered by aWHEN
clause, theELSE
clause is executed. If noELSE
clause is provided and none of theWHEN
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 Example 2-27.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 singleWHEN
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 whichWHEN
clause is executed. If a search condition yieldsTRUE
, itsWHEN
clause is executed. After anyWHEN
clause is executed, subsequent search conditions are not evaluated. If none of the search conditions yieldsTRUE
, the optionalELSE
clause is executed. If noWHEN
clause is executed and noELSE
clause is supplied, the value of the expression isNULL
.