CASE AND DECODE

CASE Expressions And Statements in Oracle

The CASE expression was first added to SQL in Oracle 8i. Oracle 9i extended its support to PL/SQL to allow CASE to be used as an expression or statement.

• Value Match (Simple) CASE Expression
• Searched CASE Expression
• Value Match (Simple) CASE Statement
• Searched CASE Statement

Related articles.

• CASE Expressions and CASE Statements 

Value Match (Simple) CASE Expression

The CASE expression is like a more flexible version of the DECODE function. The value match CASE expression, or simple CASE expression, compares the value of the expression (DEPTNO), with the list of comparison expressions (10 - 40). Once it finds a match, the associated value is returned. The optional ELSE clause allows you to deal with situations where a match is not found. Notice the CASE expression is aliased as "department". This will appear as the column name.

SELECT ename, empno, deptno,
  (CASE deptno
     WHEN 10 THEN 'Accounting'
     WHEN 20 THEN 'Research'
     WHEN 30 THEN 'Sales'
     WHEN 40 THEN 'Operations'
     ELSE 'Unknown'
   END) department
FROM emp
ORDER BY ename;

The value match CASE expression is also supported in PL/SQL. The example below uses it in an assignment.

SET SERVEROUTPUT ON
DECLARE
  deptno     NUMBER := 20;
  dept_desc  VARCHAR2(20);
BEGIN 
  dept_desc := CASE deptno
                 WHEN 10 THEN 'Accounting'
                 WHEN 20 THEN 'Research'
                 WHEN 30 THEN 'Sales'
                 WHEN 40 THEN 'Operations'
                 ELSE 'Unknown'
               END;
  DBMS_OUTPUT.PUT_LINE(dept_desc);
END;
/

All possible values returned by a CASE expression must be of the same data type.

Searched CASE Expression

The searched CASE expression can be more complicated, involving multiple columns in the comparisons. Each comparison is tested in turn and the associated value returned if a match is found. Once again, there is an optional ELSE clause to deal with situations where a match is not found.

SELECT ename, empno, sal,
  (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;
/

All possible values returned by a CASE expression must be of the same data type.

Value Match (Simple) CASE Statement

The CASE statements supported by PL/SQL are very similar to the CASE expressions. Notice the statement is finished with the END CASE keywords rather than just the END keyword. The PL/SQL CASE statements are essentially an alternative to IF .. THEN .. ELSIF statements. They are control structures that conditionally call blocks of code.

The value match CASE statement below runs a different block of code depending the match found.

SET SERVEROUTPUT ON
BEGIN
  FOR cur_rec IN (SELECT ename, empno, deptno FROM emp ORDER BY ename) LOOP
    DBMS_OUTPUT.PUT(cur_rec.ename || ' : ' || cur_rec.empno || ' : ' || cur_rec.deptno || ' : ');
    CASE cur_rec.deptno
      WHEN 10 THEN 
        DBMS_OUTPUT.PUT_LINE('Accounting');
      WHEN 20 THEN 
        DBMS_OUTPUT.PUT_LINE('Research');
      WHEN 30 THEN 
        DBMS_OUTPUT.PUT_LINE('Sales');
      WHEN 40 THEN 
        DBMS_OUTPUT.PUT_LINE('Operations');
      ELSE 
        DBMS_OUTPUT.PUT_LINE('Unknown');
    END CASE;
  END LOOP;
END;
/

Searched CASE Statement

As with its expression counterpart, the searched CASE statement allows multiple comparisons using mulitple variables.

SET SERVEROUTPUT ON
BEGIN
  FOR cur_rec IN (SELECT ename, empno, sal FROM emp ORDER BY ename) LOOP
    DBMS_OUTPUT.PUT(cur_rec.ename || ' : ' || cur_rec.empno || ' : ' || cur_rec.sal || ' : ');
    CASE
      WHEN cur_rec.sal < 1000 THEN 
        DBMS_OUTPUT.PUT_LINE('Low');
      WHEN cur_rec.sal BETWEEN 1000 AND 3000 THEN 
        DBMS_OUTPUT.PUT_LINE('Medium');
      WHEN cur_rec.sal > 3000 THEN 
        DBMS_OUTPUT.PUT_LINE('High');
      ELSE 
        DBMS_OUTPUT.PUT_LINE('Unknown');
    END CASE;
  END LOOP;
END;
/