Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle CASE Expression      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 103 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?