Oracle PL/SQL Tutorial - PL/SQL CASE

There are two types of expressions used in CASE statements: simple and searched.

Simple CASE Expression

A simple CASE statement chooses which of several sequences of PL/SQL statements to execute based on the results of a single expression.

Simple CASE statements take the following form:


     CASE expression -- w ww .j av a 2 s . c o m
     WHEN result1 THEN 
        statements1 
     WHEN result2 THEN 
        statements2 
     ... 
     ELSE 
        statements_else 
     END CASE;

Example

A simple CASE expression selects a result from one or more alternatives, and returns the result.


DECLARE -- from  w w w.ja  va 2  s  .c  om
  grade CHAR(1) := 'A'; 
  v_result VARCHAR2(20); 
BEGIN 
  v_result := 
    CASE grade 
      WHEN 'A' THEN 'Excellent' 
      WHEN 'B' THEN 'Very Good' 
      WHEN 'C' THEN 'Good' 
    END; 
  DBMS_OUTPUT.PUT_LINE (v_result); 
END; 
/

The code above generates the following result.

The optional ELSE clause works the same way to the ELSE clause in an IF statement.

If the value of the selector is 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.

A searched CASE expression has no selector.

A searched CASE statement evaluates a list of Boolean expressions, when it finds an expression that evaluates to TRUE, executes a sequence of statements associated with that expression.

Searched CASE statements have the following form:


     CASE -- from   w w w  . ja  v  a 2s .com
     WHEN expression1 THEN 
        statements1 
     WHEN expression2 THEN 
        statements2 
     ... 
     ELSE 
        statements_else 
     END CASE;

Example 2


-- from ww w.  j a v  a2s . co  m
     CASE 
     WHEN salary >= 100 AND salary <=200 THEN 
        raise_salary(employee_id, 1500); 
     WHEN salary > 200 AND salary <= 400 THEN 
        raise_salary(employee_id, 1000); 
     WHEN salary > 400 THEN 
        raise_salary(employee_id, 500); 
     ELSE 
        raise_salary(employee_id, 0); 
     END CASE;

Nested CASE Statements

CASE statements can be nested just as IF statements can.


     CASE --   w  w  w.j  av a 2 s  .  co m
     WHEN salary >= 100 THEN 
        CASE 
        WHEN salary <= 200 THEN 
           raise_salary(employee_id, 1500); 
        WHEN salary > 400 THEN 
           raise_salary(employee_id, 500); 
        WHEN salary > 200 THEN 
           raise_salary(employee_id, 1000); 
        END CASE; 
     WHEN salary < 100 THEN 
        raise_salary(employee_id,0); 
     END CASE;