msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

What you Ought to Know About CASE in Oracle PL/SQL

Oracle PL/SQL evangelist Steven Feuerstein‘s latest PL/SQL puzzler The Mysteries of CASE in PL/SQL is interesting. It touches on one “less-than-obvious aspect of PL/SQL’s CASE”. So, what is this “mysterious” thing about CASE?

Let’s first do a quick review of CASE in PL/SQL. In general, you have two types, CASE statements and CASE expressions. You can further categorize each as simple or searched:

Simple CASE statements:

CASE expression
WHEN result THEN
   statement;
   statement;
   ...
WHEN result THEN
   statement;
   statement;
   ...
...
ELSE
   statement;
   statement;
   ...
END CASE;

For example:

SQL>     DECLARE
  2         grade   CHAR (1);
  3      BEGIN
  4         grade := 'A';
  5
  6         CASE grade
  7            WHEN 'A'
  8            THEN
  9               DBMS_OUTPUT.put_line ('Excellent');
 10               DBMS_OUTPUT.put_line ('You are smart');
 11            WHEN 'B'
 12            THEN
 13               DBMS_OUTPUT.put_line ('Very Good');
 14            WHEN 'C'
 15            THEN
 16               DBMS_OUTPUT.put_line ('Good');
 17            ELSE
 18               DBMS_OUTPUT.put_line ('Go home');
 19         END CASE;
 20      END;
 21      /
Excellent
You are smart

Searched CASE statements:

CASE
WHEN boolean_expression THEN
   statement;
   statement;
   ...
WHEN boolean_expression THEN
   statement;
   statement;
   ...
...
ELSE
   statement;
   statement;
   ...
END CASE; 

For example:

SQL> DECLARE
2     grade   CHAR (1);
3  BEGIN
4     grade := 'X';
5
6     CASE
7        WHEN grade = 'A'
8        THEN
9           DBMS_OUTPUT.put_line ('Excellent');
10        WHEN grade = 'B'
11        THEN
12           DBMS_OUTPUT.put_line ('Very Good');
13     END CASE;
14  EXCEPTION
15     WHEN case_not_found
16     THEN
17        DBMS_OUTPUT.put_line ('No such grade');
18  END;
19  /
No such grade

Top 3 things to notice about CASE statements:

  1. The CASE statement executes one or many statements based on a certain condition.
  2. If no ELSE is specified, and no expressions evaluate to TRUE, then the exception CASE_NOT_FOUND is raised.
  3. WHEN clauses are evaluated in order, from top to bottom.

Simple CASE expressions:

case_result :=
   CASE expression
   WHEN result THEN
      result_expression;
   WHEN result THEN
      result_expression;
   ...
   ELSE
      result_expression;
   END;

For example:

SQL> DECLARE
  2     grade       CHAR (1)      := 'B';
  3     appraisal   VARCHAR2 (20);
  4  BEGIN
  5     appraisal :=
  6        CASE grade
  7           WHEN 'A'
  8              THEN 'Excellent'
  9           WHEN 'B'
 10              THEN 'Very Good'
 11           ELSE 'No such grade'
 12        END;
 13     DBMS_OUTPUT.put_line ('Grade ' || grade || ' is ' || appraisal);
 14  END;
 15  /
Grade B is Very Good

Searched CASE expressions:

case_result :=
   CASE
   WHEN boolean_expression THEN
      result_expression;
   WHEN boolean_expression THEN
      result_expression;
   ...
   ELSE
      result_expression;
   END;

For example:

SQL> DECLARE
  2     grade       CHAR (1)      := 'Z';
  3     appraisal   VARCHAR2 (20);
  4  BEGIN
  5     appraisal :=
  6        CASE
  7           WHEN grade = 'A'
  8              THEN 'Excellent'
  9           WHEN grade = 'B'
 10              THEN 'Very Good'
 11        END;
 12     DBMS_OUTPUT.put_line
 13      ('Grade ' || grade || ' is ' || NVL(appraisal, 'not graded'));
 14  END;
 15  /
Grade Z is Not graded

Top 3 things to notice about CASE expressions:

  1. The CASE expression returns a value based on a certain condition.
  2. The CASE expression is terminated by END (not END CASE).
  3. When no WHEN conditions are met, no error is raised, the CASE expression will simply return NULL.

Now back to Steven’s puzzler:

SQL> CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2)
  2     RETURN VARCHAR2
  3  IS
  4     retval   VARCHAR2 (100);
  5  BEGIN
  6     -- Searched CASE expression
  7     RETURN CASE
  8        WHEN letter_in = 'A'
  9           THEN 'Apple'
 10        WHEN letter_in = 'B'
 11           THEN 'Banana'
 12     END;
 13  END fruit_translator;
 14  /

Function created.

When you execute the above function, passing a value that is not ‘A’ and not ‘B’, the CASE expression should return NULL:

SQL> BEGIN
  2     DBMS_OUTPUT.put_line (   'Expression good for you = '
  3                           || NVL (fruit_translator ('C'), 'Unknown Fruit')
  4                          );
  5  END;
  6  /
Expression good for you = Unknown Fruit

As expected.

The following function contains a CASE statement:

SQL> CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2)
  2     RETURN VARCHAR2
  3  IS
  4     retval   VARCHAR2 (100);
  5  BEGIN
  6     -- Searched CASE statement
  7     CASE
  8        WHEN letter_in = 'A'
  9        THEN
 10           retval := 'Apple';
 11        WHEN letter_in = 'B'
 12        THEN
 13           retval := 'Banana';
 14     END CASE;
 15
 16     RETURN retval;
 17  END fruit_translator;
 18  /

Function created.

When you execute the above function passing a value that is not ‘A’ and not ‘B’, and because there is no ELSE specified, the CASE statement should raise an (unhandled) exception:

SQL> BEGIN
  2     DBMS_OUTPUT.put_line (   'Statement good for you = '
  3                           || NVL (fruit_translator ('C'), 'Unknown Fruit')
  4                          );
  5  END;
  6  /
BEGIN
*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at "EDDIE.FRUIT_TRANSLATOR", line 7
ORA-06512: at line 2

As expected.

One more thing to note about CASE. In Oracle 8i, CASE statements and expressions are supported only in SQL and not in PL/SQL. In 8i you cannot use CASE in PL/SQL. Starting with Oracle database release 9.0.1, the SQL and PL/SQL parsers were integrated and, as a result, in version 9.0.1 and above CASE works in both SQL and PL/SQL.

Thank you Steven for this nice puzzler. It’s easy to solve (without executing the code ;) ) as long as you know the differences between CASE statements and CASE expressions.

Sources and resources:


Filed in Oracle on 25 Jul 07 | Tags:


Reader's Comments

  1. |

    oh! this was new to me the differnet behavior of a searched case expression and a statement. Great Karl

  2. |

    Nice post Eddie, it would be interesting; if i had any spare time..:-) to see if Duffs device would work with PL/SQL case contructs as they do with C.

    cheers

    Pete

  3. |

    Good one Eddie

    Learned a new thing :)

    Sidhu

  4. |

    Glad you learned something new guys. I also learned something new today thanks to Pete, the Duff’s device. I had to check it out. Very interesting.

    After my limited reading about it, and If I understand it correctly, I do not believe that Duff’s device can be simulated in PL/SQL using CASE because there is no “break” in PL/SQL as there is in C. Moreover, unlike C, CASE breaks automatically at the end of a positive WHEN condition block (continues after the END CASE).

    Now, there is this new feature in 11g, CONTINUE, but I do not see that it will make any difference in this case.

  5. |

    Seems like an unknown subtlety to even Tom:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:67073167580253

  6. |

    Surprise surprise, Tom wrote a snarky response without reading the post.

    This article and the poster on asktom are talking about CASE in Oracle PL/SQL. Tom is only talking about CASE in Oracle SQL, where you never need the ELSE.

    Bottom line is, you’ll only get the CASE_NOT_FOUND exception when omitting the else clause from a case “statement” in PL/SQL.