Quick Search:
 
 The Oracle CASE Expression      [Return To Index] Jump to:  

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;
/


Related Links:

Related Code Snippets:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org