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 "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. The 
CASE 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 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 Example 2-27.

   
   
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.