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

    Jan 26, 2018 2:47:05 PM by Dan Hotka

    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

    3644.c1

    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.

    7357.c3.png-1100x19998

    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

    8424.c4.png-1100x19998

    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.

    2148.c5.png-1100x19998

    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.

    1581.c6.png-1100x19998

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

    4214.c7.png-1100x19998

    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.

    Tags: Oracle Development

    Dan Hotka

    Written by Dan Hotka

    Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari. Dan's most recent book is Toad for Oracle Unleashed