Using CASE Statements in PL/SQL programs

While CASE constructs don't offer any fundamentally new semantics, they do allow a more compact notation and some elimination of repetition with respect to what otherwise would be expressed with an IF construct. Consider the implementation of a decision table whose predicate is the value of a particular expression. These two fragments…

case n
  when 1 then Action1;
  when 2 then Action2;
  when 3 then Action3;
  else        ActionOther;
end case;

…and…

if

          n = 1 then Action1;
    elsif n = 2 then Action2;
    elsif n = 3 then Action2;
    else             ActionOther;
end if;

…are semantically almost identical. But coding best practice gurus generally recommend the CASE formulation because it more directly models the idea. By pulling out the decision expression n to the start and by mentioning it only once the programmer's intention is clearer. This is significant both to the proof reader and to the compiler, which therefore has better information from while to generate efficient code. For example, the compiler knows immediately that the decision expression needs to be evaluated just once. And, since the IF formulation repeats the decision expression for each leg, there's a greater risk of typographical error which can be difficult to spot.

Moreover, the CASE formulation makes it explicit that the coded cases are the only ones that need handling (see the discussion of the case_not_found exception below).

CASE constructs are available in most programming languages. Oracle9i introduces them in PL/SQL (and in SQL).

CASE Expressions

A CASE expression selects a result and returns it. To select the result, the CASE expression uses a selector, an expression whose value is used to select one of several alternatives. Consider these two semantically almost identical fragments

text := case n
          when 1 then one
          when 2 then two

          when 3 then three
          else        other
        end case;

…and

if
          n = 1 then text := one;
    elsif n = 2 then text := two;
    elsif n = 3 then text := three;
    else             text := other;
end if;

The CASE formulation makes it explicit that the intention of the fragment is to provide a value for text. This sample illustrates using a CASE expression in PL/SQL.

An alternative to the CASE expression is the CASE statement, where each WHEN clause can be an entire PL/SQL block.

Searched CASE Statement AND Searched CASE Expression

For both the CASE statement and the CASE expression, the searched variant tests each leg on an arbitrary boolean expression, rather than on equality on a single expression common for all legs, thus

case
  when n = 1               then Action1;
  when n = 2               then Action2;
  when n = 3               then Action3;
  when ( n > 3 and n < 8 ) then Action4through7;
  else                          ActionOther;
end case;

…and…

text := case
          when n = 1               then one

          when n = 2               then two
          when n = 3               then three
          when ( n > 3 and n < 8 ) then four_through_seven
          else                          other
        end;

Note: With the CASE formulation as with the IF formulation, the leg which is selected for particular data values will in general depend on the order in which the legs are written. Consider…

case
  when this_patient.pregnant = 'Y'     then Action1;
  when this_patient.unconscious = 'Y'  then Action2;
  when this_patient.age < 5            then Action3;

  when this_patient.gender = 'F'       then Action4;
  else                                      ActionOther;
end case;

An unconscious pregnant woman will receive Action1. This sample illustrates using the Searched CASE constructs in PL/SQL.

CASE_NOT_FOUND Exception

A subtle difference between the CASE construct and the corresponding IF construct occurs when the ELSE leg is omitted. With the IF consruct, if none of the legs is selected then there is no action. But with the CASE construct, if none of the legs is selected then the case_not_found exception (ORA-06592: CASE not found while executing CASE statement) is raised, thus…

  ...
  p:=0; q:=0; r:=0;
  case
    when p = 1 then Action1;
    when r = 2 then Action2;
    when q > 1 then Action3;
  end case;
exception
  when case_not_found
    then Dbms_Output.Put_Line ( 'Trapped: case_not_found' );
    ...

This sample illustrates trapping the exception in PL/SQL.

Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 7-Jan-2003