Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

CASE Expressions And Statements in Oracle

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

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 more information see:

Hope this helps. Regards Tim...

Back to the Top.