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