case expressions and statements in oracle 9i
The CASE expression was introduced by Oracle in version 8i. It was a SQL-only expression that provided much greater flexibility than the functionally-similar DECODE function. The PL/SQL parser didn't understand CASE in 8i, however, which was a major frustration for developers (the workaround was to use views, dynamic SQL or DECODE).
Oracle 9i Release 1 (9.0) extends CASE capabilities with the following enhancements:
- a new simple CASE expression (8i CASE was a "searched" or "switched" expression);
- a new CASE statement; a PL/SQL construct equivalent to IF-THEN-ELSE; and
- full PL/SQL support for both types of CASE expression; in SQL and in PL/SQL constructs (in 9i, the SQL and PL/SQL parsers are the same).
In this article, we will work through each of the new features and show a range of possibilities for the new syntax.
simple case expression
The simple CASE expression is new in 9i. In SQL, it is functionally equivalent to DECODE in that it tests a single value or expression for equality only. This is supposedly optimised for simple equality tests where the cost of repeating the test expression is high (although in most cases it is extremely difficult to show a performance difference over DECODE or the older searched CASE expression).
A simple CASE expression takes the following format. As with all CASE expression and statement formats in this article, it will evaluate from top to bottom and "exit" on the first TRUE condition.
CASE {value or expression}
WHEN {value}
THEN {something}
[WHEN...]
[THEN...]
[ELSE...] --<-- NULL if not specified and no WHEN tests satisfied
END
The following is a contrived example of a simple CASE expression against the EMP table.
SQL> SELECT ename 2 , job 3 , CASE deptno 4 WHEN 10 5 THEN 'ACCOUNTS' 6 WHEN 20 7 THEN 'SALES' 8 WHEN 30 9 THEN 'RESEARCH' 10 WHEN 40 11 THEN 'OPERATIONS' 12 ELSE 'UNKNOWN' 13 END AS department 14 FROM emp;
ENAME JOB DEPARTMENT ---------- --------- ---------- SMITH CLERK SALES ALLEN SALESMAN RESEARCH WARD SALESMAN RESEARCH JONES MANAGER SALES MARTIN SALESMAN RESEARCH BLAKE MANAGER RESEARCH CLARK MANAGER ACCOUNTS SCOTT ANALYST SALES KING PRESIDENT ACCOUNTS TURNER SALESMAN RESEARCH ADAMS CLERK SALES JAMES CLERK RESEARCH FORD ANALYST SALES MILLER CLERK ACCOUNTS 14 rows selected.
searched case expression
The searched CASE expression is the 8i variant. This is much more flexible than a simple CASE expression or DECODE function. It can conduct multiple tests involving a range of different columns, expressions and operators. Each WHEN clause can include a number of AND/OR tests. It takes the following format (note that the expressions to evaluate are included within each WHEN clause).
CASE
WHEN {test or tests}
THEN {something}
[WHEN {test or tests}]
[THEN...]
[ELSE...]
END
For example:
CASE WHEN column IN (val1, val2) AND another_column > 0 THEN something WHEN yet_another_column != 'not this value' THEN something_else END
The following query against EMP shows how we might use searched CASE to evaluate the current pay status of each employee.
SQL> SELECT ename 2 , job 3 , CASE 4 WHEN sal < 1000 5 THEN 'Low paid' 6 WHEN sal BETWEEN 1001 AND 2000 7 THEN 'Reasonably well paid' 8 WHEN sal BETWEEN 2001 AND 3001 9 THEN 'Well paid' 10 ELSE 'Overpaid' 11 END AS pay_status 12 FROM emp;
ENAME JOB PAY_STATUS ---------- --------- -------------------- SMITH CLERK Low paid ALLEN SALESMAN Reasonably well paid WARD SALESMAN Reasonably well paid JONES MANAGER Well paid MARTIN SALESMAN Reasonably well paid BLAKE MANAGER Well paid CLARK MANAGER Well paid SCOTT ANALYST Well paid KING PRESIDENT Overpaid TURNER SALESMAN Reasonably well paid ADAMS CLERK Reasonably well paid JAMES CLERK Low paid FORD ANALYST Well paid MILLER CLERK Reasonably well paid 14 rows selected.
case expressions in pl/sql
As stated earlier, the SQL and PL/SQL parsers are the same from 9i onwards. This means that CASE expressions can be used in static implicit and explicit SQL cursors within PL/SQL. In addition to this, the CASE expression can also be used as an assignment mechanism, which provides an extremely elegant method for IF-THEN-ELSE-type constructs. For example, the following construct...
IF something = something THEN variable := value; ELSE variable := alternative_value; END IF;
...can now be written as a CASE expression as follows.
variable := CASE something
WHEN something
THEN value
ELSE alternative_value
END;
This flexibility is something that DECODE doesn't provide as it is a SQL-only function. Needless to say, both simple and searched CASE expressions can be used as above. The following example shows a simple CASE expression being used to assign a variable.
SQL> DECLARE 2 3 v_dummy VARCHAR2(10) := 'DUMMY'; 4 v_assign VARCHAR2(10); 5 6 BEGIN 7 8 v_assign := CASE v_dummy 9 -- 10 WHEN 'Dummy' 11 THEN 'INITCAP' 12 -- 13 WHEN 'dummy' 14 THEN 'LOWER' 15 -- 16 WHEN 'DUMMY' 17 THEN 'UPPER' 18 -- 19 ELSE 'MIXED' 20 -- 21 END; 22 23 DBMS_OUTPUT.PUT_LINE( 24 'Variable v_dummy is in '||v_assign||' type case.' 25 ); 26 27 END; 28 /
Variable v_dummy is in UPPER type case. PL/SQL procedure successfully completed.
We can take this example a stage further and use the CASE expression directly inside the call to DBMS_OUTPUT as follows.
SQL> DECLARE 2 v_dummy VARCHAR2(10) := 'DUMMY'; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE( 5 'Variable v_dummy is in ' || CASE v_dummy 6 WHEN 'Dummy' 7 THEN 'INITCAP' 8 WHEN 'dummy' 9 THEN 'LOWER' 10 WHEN 'DUMMY' 11 THEN 'UPPER' 12 ELSE 'MIXED' 13 END || ' type case.' ); 14 END; 15 /
Variable v_dummy is in UPPER type case. PL/SQL procedure successfully completed.
Here we have removed the need for an intermediate variable. Similarly, CASE expressions can be used directly in function RETURN statements. In the following example, we will create a function that returns each employee's pay status using the CASE expression from our earlier examples.
SQL> CREATE FUNCTION pay_status ( 2 sal_in IN NUMBER 3 ) RETURN VARCHAR2 IS 4 BEGIN 5 RETURN CASE 6 WHEN sal_in < 1000 7 THEN 'Low paid' 8 WHEN sal_in BETWEEN 1001 AND 2000 9 THEN 'Reasonably well paid' 10 WHEN sal_in BETWEEN 2001 AND 3001 11 THEN 'Well paid' 12 ELSE 'Overpaid' 13 END; 14 END; 15 /
Function created.
SQL> SELECT ename 2 , pay_status(sal) AS pay_status 3 FROM emp;
ENAME PAY_STATUS ---------- -------------------- SMITH Low paid ALLEN Reasonably well paid WARD Reasonably well paid JONES Well paid MARTIN Reasonably well paid BLAKE Well paid CLARK Well paid SCOTT Well paid KING Overpaid TURNER Reasonably well paid ADAMS Reasonably well paid JAMES Low paid FORD Well paid MILLER Reasonably well paid 14 rows selected.
Of course, we need to balance the good practice of rules encapsulation with our performance requirements. If the CASE expression is only used in one SQL statement in our application, then in performance terms we will benefit greatly from "in-lining" the expression directly. If the business rule is used in numerous SQL statements across the application, we might be more prepared to pay the context-switch penalty and wrap it in a function as above.
Note that in some earlier versions of 9i, we might need to wrap the CASE expression inside TRIM to be able to return it directly from a function (i.e. RETURN TRIM(CASE...)). There is a "NULL-terminator" bug similar to a quite-well known variant in 8i Native Dynamic SQL (this would sometimes appear when attempting to EXECUTE IMMEDIATE a SQL statement fetched directly from a table).
ordering data with case expressions
We have already seen that CASE expressions provide great flexibility within both SQL and PL/SQL. CASE expressions can also be used in ORDER BY clauses to dynamically order data. This is especially useful in two ways:
- when we need to order data with no inherent order properties; and
- when we need to support user-defined ordering from a front-end application.
In the following example, we will order the EMP data according to the JOB column but not alphabetically.
SQL> SELECT ename 2 , job 3 FROM emp 4 ORDER BY CASE job 5 WHEN 'PRESIDENT' 6 THEN 1 7 WHEN 'MANAGER' 8 THEN 2 9 WHEN 'ANALYST' 10 THEN 3 11 WHEN 'SALESMAN' 12 THEN 4 13 ELSE 5 14 END;
ENAME JOB ---------- --------- KING PRESIDENT JONES MANAGER BLAKE MANAGER CLARK MANAGER SCOTT ANALYST FORD ANALYST ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN TURNER SALESMAN SMITH CLERK MILLER CLERK ADAMS CLERK JAMES CLERK 14 rows selected.
As stated earlier, the second possibility is for user-defined ordering. This is most common on search screens where users can specify how they want their results ordered. It is quite common for developers to code complicated dynamic SQL solutions to support such requirements. With CASE expressions, however, we can avoid such complexity, especially when the number of ordering columns is low. In the following example, we will create a dummy procedure to output EMP data according to a user's preference for ordering.
SQL> CREATE FUNCTION order_emps( p_column IN VARCHAR2 )
2 RETURN SYS_REFCURSOR AS
3
4 v_rc SYS_REFCURSOR;
5
6 BEGIN
7
8 DBMS_OUTPUT.PUT_LINE('Ordering by ' || p_column || '...');
9
10 OPEN v_rc FOR SELECT ename, job, hiredate, sal
11 FROM emp
12 ORDER BY
13 CASE UPPER(p_column)
14 WHEN 'ENAME'
15 THEN ename
16 WHEN 'SAL'
17 THEN TO_CHAR(sal,'fm0000')
18 WHEN 'JOB'
19 THEN job
20 WHEN 'HIREDATE'
21 THEN TO_CHAR(hiredate,'YYYYMMDD')
22 END;
23
24 RETURN v_rc;
25
26 END order_emps;
27 /
Function created.
CASE expressions can only return a single datatype, so we need to cast NUMBER and DATE columns to VARCHAR2 as above. This can change their ordering behaviour, so we ensure that the format masks we use enable them to sort correctly.
Now we have the function in place, we can simulate a front-end application by setting up a refcursor variable in sqlplus and calling the function with different inputs as follows.
SQL> var rc refcursor;
SQL> set autoprint on
SQL> exec :rc := order_emps('job');
Ordering by job... PL/SQL procedure successfully completed. ENAME JOB HIREDATE SAL ---------- --------- --------- ---------- SCOTT ANALYST 19-APR-87 3000 FORD ANALYST 03-DEC-81 3000 SMITH CLERK 17-DEC-80 800 ADAMS CLERK 23-MAY-87 1100 MILLER CLERK 23-JAN-82 1300 JAMES CLERK 03-DEC-81 950 JONES MANAGER 02-APR-81 2975 CLARK MANAGER 09-JUN-81 2450 BLAKE MANAGER 01-MAY-81 2850 KING PRESIDENT 17-NOV-81 5000 ALLEN SALESMAN 20-FEB-81 1600 MARTIN SALESMAN 28-SEP-81 1250 TURNER SALESMAN 08-SEP-81 1500 WARD SALESMAN 22-FEB-81 1250 14 rows selected.
SQL> exec :rc := order_emps('hiredate');
Ordering by hiredate... PL/SQL procedure successfully completed. ENAME JOB HIREDATE SAL ---------- --------- --------- ---------- SMITH CLERK 17-DEC-80 800 ALLEN SALESMAN 20-FEB-81 1600 WARD SALESMAN 22-FEB-81 1250 JONES MANAGER 02-APR-81 2975 BLAKE MANAGER 01-MAY-81 2850 CLARK MANAGER 09-JUN-81 2450 TURNER SALESMAN 08-SEP-81 1500 MARTIN SALESMAN 28-SEP-81 1250 KING PRESIDENT 17-NOV-81 5000 JAMES CLERK 03-DEC-81 950 FORD ANALYST 03-DEC-81 3000 MILLER CLERK 23-JAN-82 1300 SCOTT ANALYST 19-APR-87 3000 ADAMS CLERK 23-MAY-87 1100 14 rows selected.
The overall benefits of this method are derived from having a single, static cursor compiled into our application code. With this, we do not need to resort to dynamic SQL solutions which are more difficult to maintain and debug but can also be slower to fetch due to additional soft parsing.
filtering data with case expressions
In addition to flexible ordering, CASE expressions can also be used to conditionally filter data or join datasets. In filters, CASE expressions can replace complex AND/OR filters, but this can sometimes have an impact on CBO arithmetic and resulting query plans, so care will need to be taken. We can see this as follows. First we will write a fairly complex set of predicates against an EMP-DEPT query.
SQL> SELECT e.ename 2 , e.empno 3 , e.job 4 , e.sal 5 , e.hiredate 6 , d.deptno 7 FROM dept d 8 , emp e 9 WHERE d.deptno = e.deptno 10 AND NOT ( e.deptno = 10 11 AND e.sal >= 1000 ) 12 AND e.hiredate <= DATE '1990-01-01' 13 AND d.loc != 'CHICAGO';
ENAME EMPNO JOB SAL HIREDATE DEPTNO ---------- ---------- --------- ---------- --------- ---------- SMITH 7369 CLERK 800 17-DEC-80 20 JONES 7566 MANAGER 2975 02-APR-81 20 SCOTT 7788 ANALYST 3000 19-APR-87 20 ADAMS 7876 CLERK 1100 23-MAY-87 20 FORD 7902 ANALYST 3000 03-DEC-81 20 5 rows selected.
We can re-write this using a CASE expression. It can be much easier as a "multi-filter" in certain scenarios, as we can work through our predicates in a much more logical fashion. We can see this below. All filters evaluating as true will be give a value of 0 and we will only return data that evaluates to 1.
SQL> SELECT e.ename 2 , e.empno 3 , e.job 4 , e.sal 5 , e.hiredate 6 , d.deptno 7 FROM dept d 8 , emp e 9 WHERE d.deptno = e.deptno 10 AND CASE 11 WHEN e.deptno = 10 12 AND e.sal >= 1000 13 THEN 0 14 WHEN e.hiredate > DATE '1990-01-01' 15 THEN 0 16 WHEN d.loc = 'CHICAGO' 17 THEN 0 18 ELSE 1 19 END = 1;
ENAME EMPNO JOB SAL HIREDATE DEPTNO ---------- ---------- --------- ---------- --------- ---------- SMITH 7369 CLERK 800 17-DEC-80 20 JONES 7566 MANAGER 2975 02-APR-81 20 SCOTT 7788 ANALYST 3000 19-APR-87 20 ADAMS 7876 CLERK 1100 23-MAY-87 20 FORD 7902 ANALYST 3000 03-DEC-81 20 5 rows selected.
As stated, care needs to be taken with this as it can change the CBO's decision paths. As we are only dealing with EMP and DEPT here, the following example ends up with the same join mechanism, but note the different filter predicates reported by DBMS_XPLAN (this is a 9i Release 2 feature). When costing the predicates, Oracle treats the entire CASE expression as a single filter, rather than each filter separately. With histograms or even the most basic column statistics, Oracle is able to cost the filters when we write them the "AND/OR way". With CASE, Oracle has no such knowledge to draw on.
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'FILTERS' 2 FOR 3 SELECT e.ename 4 , e.empno 5 , e.job 6 , e.sal 7 , e.hiredate 8 , d.deptno 9 FROM dept d 10 , emp e 11 WHERE d.deptno = e.deptno 12 AND NOT ( e.deptno = 10 13 AND e.sal >= 1000 ) 14 AND e.hiredate <= DATE '1990-01-01' 15 AND d.loc != 'CHICAGO';
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','FILTERS'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 360 | 5 |
|* 1 | HASH JOIN | | 10 | 360 | 5 |
|* 2 | TABLE ACCESS FULL | DEPT | 3 | 27 | 2 |
|* 3 | TABLE ACCESS FULL | EMP | 10 | 270 | 2 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
2 - filter("D"."LOC"<>'CHICAGO')
3 - filter(("E"."DEPTNO"<>10 OR "E"."SAL"<1000) AND
"E"."HIREDATE"<=TO_DATE(' 1990-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Note: cpu costing is off
20 rows selected.
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'CASE' 2 FOR 3 SELECT e.ename 4 , e.empno 5 , e.job 6 , e.sal 7 , e.hiredate 8 , d.deptno 9 FROM dept d 10 , emp e 11 WHERE d.deptno = e.deptno 12 AND CASE 13 WHEN e.deptno = 10 14 AND e.sal >= 1000 15 THEN 0 16 WHEN e.hiredate > DATE '1990-01-01' 17 THEN 0 18 WHEN d.loc = 'CHICAGO' 19 THEN 0 20 ELSE 1 21 END = 1;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','CASE'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 5 |
|* 1 | HASH JOIN | | 1 | 36 | 5 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 36 | 2 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 378 | 2 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO")
filter(CASE WHEN ("E"."DEPTNO"=10 AND "E"."SAL">=1000) THEN
0 WHEN "E"."HIREDATE">TO_DATE(' 1990-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') THEN 0 WHEN "D"."LOC"='CHICAGO' THEN 0 ELSE 1 END =1)
Note: cpu costing is off
19 rows selected.
case statements (pl/sql only)
We have spent a lot of time looking at CASE expressions in this article. We will finish with a look at the new CASE statement. Most developers seem to use this term when they are in fact describing CASE expressions. The CASE statement is a PL/SQL-only construct that is similar to IF-THEN-ELSE. Its simple and searched formats are as follows.
CASE {variable or expression}
WHEN {value}
THEN {one or more operations};
[WHEN..THEN]
ELSE {default operation};
END CASE;
CASE
WHEN {expression test or tests}
THEN {one or more operations};
[WHEN..THEN]
ELSE {default operation};
END CASE;
Note the semi-colons. CASE statements do not return values like CASE expressions. CASE statements are IF tests that are used to decide which action(s) or operation(s) to execute. Note also the END CASE syntax. This is mandatory. In the following example, we will return to our dummy test but call a procedure within each evaluation.
SQL> DECLARE
2
3 v_dummy VARCHAR2(10) := 'DUMMY';
4
5 PROCEDURE output (input VARCHAR2) IS
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(
8 'Variable v_dummy is in '||input||' type case.');
9 END output;
10
11 BEGIN
12
13 CASE v_dummy
14
15 WHEN 'Dummy'
16 THEN output('INITCAP');
17
18 WHEN 'dummy'
19 THEN output('LOWER');
20
21 WHEN 'DUMMY'
22 THEN output('UPPER');
23
24 ELSE output('MIXED');
25
26 END CASE;
27
28 END;
29 /
Variable v_dummy is in UPPER type case. PL/SQL procedure successfully completed.
CASE statements can be useful for very simple, compact and repeated tests (such as testing a variable for a range of values). Other than this, it is unlikely to draw many developers away from IF-THEN-ELSE. The main difference between CASE and IF is that the CASE statement must evaluate to something. Oracle has provided a built-in exception for this event; CASE_NOT_FOUND. The following example shows what happens if the CASE statement cannot find a true test. We will trap the CASE_NOT_FOUND and re-raise the exception to demonstrate the error message.
SQL> DECLARE
2
3 v_dummy VARCHAR2(10) := 'dUmMy';
4
5 PROCEDURE output (input VARCHAR2) IS
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(
8 'Variable v_dummy is in '||input||' type case.');
9 END output;
10
11 BEGIN
12
13 CASE v_dummy
14
15 WHEN 'Dummy'
16 THEN output('INITCAP');
17
18 WHEN 'dummy'
19 THEN output('LOWER');
20
21 WHEN 'DUMMY'
22 THEN output('UPPER');
23
24 END CASE;
25
26 EXCEPTION
27 WHEN CASE_NOT_FOUND THEN
28 DBMS_OUTPUT.PUT_LINE('Ooops!');
29 RAISE;
30 END;
31 /
Ooops! DECLARE * ERROR at line 1: ORA-06592: CASE not found while executing CASE statement ORA-06512: at line 29
The workaround to this is simple: add an "ELSE NULL" to the CASE statement.
further reading
This completes our look at CASE expressions and statements. For further reading, see the online SQL Reference and the PL/SQL User's Guide and Reference.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, May 2002
Back to Top