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:

Possibly related:

Tagged | Post a Comment