Oracle Coding Best Practices: Making a CASE for the CASE statement

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.

Oracle documentation:https://docs.oracle.com/en/database/oracle/oracle-database/12.2/index.html

CASE Syntax as per Oracle12 documentation

1:  DECLARE
2:
3:  v_hours_worked  NUMBER(3) := &Hours_Worked;
4:
5:  BEGIN
6:
7:  CASE
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');
16:   ELSE
17:      dbms_output.put_line('You did not enter a valid number of hours');
18: END CASE;
19:
20: END;
21: /

***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.

DECODE Example

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
from EMP;

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.

Summary

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.