Term: CASE

Definition:
The Oracle CASE expression is similar to the IF-THEN-ELSE statement. Each condition is checked starting from the first condition. When a condition is satisfied (the "WHEN" part) the expression returns the associated value (the "THEN" part). If no condition is matched the value in the ELSE part (if present) is returned.

The ELSE part of the expression is optional.
The Oracle CASE expression will return NULL if no condition is satisfied.

By using the CASE function, multiple conditions provided in separate queries can be combined into a single query, avoiding multiple statements on the same table. CASE statements are also evaluated more rapidly and more efficiently than equivalent code written using multiple IF/THEN statements.

Example Usage:

SELECT level, CASE 

  WHEN level < 2000 THEN 'Level ID 1' 

  WHEN level < 3000 THEN 'Level ID 2' 

  WHEN level < 4000 THEN 'Level ID 3' 

         ELSE 'Level 4' 

  END 

FROM levels;

The example above checks the value of 'level' and returns the matching Level ID code (1, 2, 3, or 4).

Searched CASE Expression
A more complex version is the searched CASE expression where a comparison expression is used to find a match. In this form the comparison is not limited to a single column:

SELECT ename, empno, 

      (CASE

  WHEN sal < 1000 THEN 'Low'

  WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'

  WHEN sal > 3000 THEN 'High'

  ELSE 'N/A'

      END) salary

    FROM emp

    ORDER BY ename;

The searched CASE expression is also supported in PL/SQL:

SET SERVEROUTPUT ON

    DECLARE

      sal       NUMBER := 2000;

      sal_desc  VARCHAR2(20);

    BEGIN 

      sal_desc := CASE

  WHEN sal < 1000 THEN 'Low'

  WHEN sal BETWEEN 1000 AND 3000 THEN 'Medium'

  WHEN sal > 3000 THEN 'High'

  ELSE 'N/A'

                  END;

      DBMS_OUTPUT.PUT_LINE(sal_desc);

    END;

    /