CASE WHEN 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;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

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 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​​.