8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
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; /
For more information see:
- CASE Expressions and CASE Statements
- Oracle9iSQL Reference Release 2 (9.2) : SQL CASE Expression
- PL/SQL User's Guide and Reference Release 2 (9.2) : PL/SQL CASE Expression
- PL/SQL User's Guide and Reference Release 2 (9.2) : PL/SQL CASE Statement
Hope this helps. Regards Tim...