CASE Expressions And Statements

The CASE expression was first added to SQL in Oracle8i. Oracle9i extends its support to PL/SQL to allow CASE to be used as an expression or statement:

Value Match CASE Expression
Searched CASE Expression
Value Match CASE Statement
Searched CASE Statement

Value Match CASE Expression

The CASE expression is a more flexible version of the DECODE function. In its simplest form it is used to return a value when a match is found:

SELECT ename, empno, 
  (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:

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

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

Value Match CASE Statement

The CASE statement supported by PL/SQL is very similar to the CASE expression. The main difference is that the statement is finished with an END CASE statement rather than just END. The PL/SQL statements are essentially an alternative to lists of IF .. THEN .. ELSIF statements:

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 || ' : ');
    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 || ' : ');
    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;
/

For further information see:

Hope this helps. Regards Tim...

Back to the Top.