This article will discuss the CASE statement. PL/SQL has had the CASE statement since Oracle9 I believe (an eternity for most of today’s coders). I will also illustrate a little-known fact: The CASE statement also exists for SQL! I believe this showed up around Oracle10.
The CASE statement is conditional flow control syntax. Many languages have this feature. Oracle has implemented it in both PL/SQL and into the SQL engine.
In PL/SQL, there are two flavors. First, the CASE statement evaluates the contents of a variable and returns a value (implemented as a function). I refer to this version of the CASE statement as Format 1; Oracle calls it a Simple CASE statement.
The Searched CASE statement (that I refer to as Format 2) is more like the IF/THEN/ELSE logic in that the selector is evaluated and a command or calculation can be performed.
Oracle documentation: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/index.html
Logic flow continues the END clause when a selection criteria has been successfully evaluated.
***Tip*** a performance tip is to put the evaluations in this CASE syntax in the order that the criteria will be found first. Using the example below, if the student usually gets a ‘C’, then put the evaluation for grade = ‘C’ first in the CASE statement.
PL/SQL CASE Statement
This CASE syntax is evaluating the contents of the selector and returning a result. The result can also be a calculation, columns, concatenated columns/text, etc. The output of the result will be stored in the variable in front of this code, so make sure the data type is correct to hold your result
CASE Syntax as per Oracle10 documentation
For some reason, this version of the CASE statement didn’t appear in the Oracle12 documentation.
Simple CASE Syntax
***Note*** These examples came from the Oracle10 documentation Part #B10807
Notice above that the variable ‘appraisal’ will be populated with the various variables depending on the contents of the ‘grade’ variable. Also notice the END; clause, slightly different than in Format 2.
This feature is a function and we will see a version of this again when we visit the inline CASE statement for SQL.
Simple CASE Syntax with compound selection criteria
This is an example showing a compound conditional test.
Format 2, or the Simple CASE statement as Oracle refers to it, runs statements depending on the contents of the selector. Notice that this flavor of the CASE statement ends in an ‘END CASE;’ syntax.
CASE Syntax as per Oracle12 documentation
3: v_hours_worked NUMBER(3) := &Hours_Worked;
8: WHEN v_hours_worked > 40 THEN
9: dbms_output.put_line('You worked overtime');
10: WHEN v_hours_worked = 40 THEN
11: dbms_output.put_line('you worked a full week');
12: WHEN v_hours_worked BETWEEN 20 and 40 THEN
13: dbms_output.put_line('have you been sick?');
14: WHEN v_hours_worked < 20 THEN
15: dbms_output.put_line('you are part-time help');
17: dbms_output.put_line('You did not enter a valid number of hours');
18: END CASE;
***Note*** CASE Format 1 ends with an END, CASE Format 2 ends with an END CASE
This Format 2 CASE statement runs commands instead of returning values. This version of the CASE statement is definitely PL/SQL-only kind of code. This can run several statements (like what is found on lines 9, 11, 13, 15, and 17), delimited by ‘;’. Again, once the evaluation is true, the CASE runs the statements and bails to line 20.
SQL CASE Statement
SQL has had the DECODE statement for a very long time. This DECODE is much like the IF/THEN/ELSE and CASE to SQL.
***Note*** DECODE is unique to Oracle.
I like to format the DECODE as illustrated above to be able to clearly see its association.
This one reads if DEPTNO is a 10, it returns ‘New York’, if 20 then returns ‘Dallas’…if it’s not one of these (including 30 and 40, then it returns ‘Des Moines’. If DECODE didn’t have the optional ‘else’ syntax (i.e., ‘Des Moines’) then this function would return a null value.
select initcap(ENAME) Name, DEPTNO Dept, SAL Salary,
decode(DEPTNO,10,SAL * 1.1,
20,SAL * 1.2,
SAL * 1.15) NEW_Salary
Just since we were on the topic of DECODE: Like CASE, DECODE can return the result of a calculation as well. This example, people in dept 10 get a 10% increase, dept 20, gets a 20% increase, and everyone else gets a 15% increase.
SQL CASE Syntax as per Oracle12 documentation
Notice that this CASE statement is a function and it ends with an ‘END’, not an ‘END CASE’.
SQL CASE Working Example
This CASE statement is the above illustrated DECODE converted to CASE.
***Tip*** for you power users…sometimes there is a dropdown menu that allows you to select ‘List of Values’ from a table. Depending on the number of values, this can take a long time to produce results…you can use the CASE or DECODE in your query to manually display descriptions. Upside…this code is very fast…downside…it is not flexible as more data options are added to your list of values.
1: set pagesize 30
2: set linesize 300
3: spool index_info.txt
4: SELECT i.table_name, …, i.clustering_factor,
5: case when nvl(i.clustering_factor,0) = 0 then 'No Stats'
6: when nvl(t.num_rows,0) = 0 then 'No Stats'
7: when (i.clustering_factor / t.num_rows) * 100 < 6 then 'Excellent '
8: when (i.clustering_factor / t.num_rows) * 100 between 6 and 11 then 'Good'
9: when (i.clustering_factor / t.num_rows) * 100 between 12 and 21 then 'Fair'
10: else 'Poor'
11: end Index_Quality
12: from user_indexes i, user_objects o, user_tables t
The above SQL statement is from my SQL Performance Tuning class…the chapter on indexing (I think this chapter is worth the cost of the entire course!!!). This SQL gives useful information about indexes and their relationship to the underlying table. Anyway, back to topic…I used a CASE statement at lines 5 thru 11 to drive a point home. The evaluation can also be based on the result of a calculation. This CASE statement is basically doing the math for you and giving you the quality of the index, based on the topic of the index unit.
Let me know if you would like the entire script. I shortened it because all I needed here was to illustrate a working example and the various ways you can code a CASE statement.
The CASE statement has been around the Oracle RDBMS for quite a while. PL/SQL can two versions of the CASE statement (I call these Format 1 and Format 2) where Format 1 is very similar to the SQL version of the CASE statement.
CASE is flexible, tunable, fast, and compatible with CASE found in other computer languages.
I hope you find these tips useful in your day to day use of the Oracle RDBMS.
Toad Code Analysis currently provides two rules which may help you converting to CASE:
1/ Rule 5816 (Use CASE expression rather than DECODE) will just locate DECODE functions.
2/ Rule 4213 (Use CASE statement rather than a complex conditional IF statement) will detect IF statements with one or more ELSIFs and with ELSE, or with multiple ELSIFs without ELSE. Those are easy enough to convert to CASE statements.
There is one important thing that shoud always favor CASE against DECODE which is:
CASE statement, when used with its ELSE part, can not only make DDL predicate treats FALSE and NULL alike, but it behaves the same way during DML and DDL predicate.
I wrote a entire article explaining this subtle difference
Mohamed, great article, this is an eye-opener.
IMHO it could be meaningful to add to Toad a new rule flagging such "check (v1 in ('a','b','c')" kind of constructs in DDL. Not sure that we should hint a CASE encapsulating an IN() in DML, as the effect doesn't change here.
Agree? Or did I miss something?
BTW, we have Rule 2757 : A CASE expression should have an ELSE clause to avoid it returning NULL.
Thanks for your comment.
All suggestions that clearly helps people understanding the difference between CASE and DECODE will certainly be of a great help