Oracle PL/SQL Programming, Third Edition

CASE Statements

New to PL/SQL in Oracle9i, the CASE statement allows you to select one sequence of statements to execute out of many possible sequences. CASE statements themselves are not new; they have long been implemented in other programming languages. They’ve been part of the SQL standard since 1992, although Oracle SQL didn’t support CASE until the release of Oracle8i, and PL/SQL didn’t support CASE until Oracle9i.

Oracle9i (and higher) supports the following two types of CASE statements:

Simple CASE statement

Associates each of one or more sequences of PL/SQL statements with a value. Chooses which sequence of statements to execute based on an expression that returns one of those values.

Searched CASE statement

Chooses which of one or more sequences of PL/SQL statements to execute by evaluating a list of Boolean conditions. The sequence of statements associated with the first condition that evaluates to TRUE is executed.

In addition to CASE statements, PL/SQL also supports CASE expressions. A CASE expression is very similar in form to a CASE statement, and allows you to choose which of one or more expressions to evaluate. The result of a CASE expression is a single value, whereas the result of a CASE statement is the execution of a sequence of PL/SQL statements.

Simple CASE Statements

A simple CASE statement allows you to choose which of several sequences of PL/SQL statements to execute based on the results of a single expression. Simple CASE statements take the following form:

CASE expression
WHEN result1 THEN
   statements1
WHEN result2 THEN
   statements2
...
ELSE 
   statements_else
END CASE;

The ELSE portion of the statement is optional. When evaluating such a CASE statement, PL/SQL first evaluates expression. It then compares the result of expression with result1. If the two results match, statements1 is executed. Otherwise, result2 is checked, and so forth.

Following is an example of a simple CASE statement that uses the employee type as a basis for selecting the proper bonus algorithm:

CASE employee_type
WHEN 'S' THEN
   award_salary_bonus(employee_id);
WHEN 'H' THEN
   award_hourly_bonus(employee_id);
WHEN 'C' THEN
   award_commissioned_bonus(employee_id);
ELSE
   RAISE invalid_employee_type;
END CASE;

This CASE statement has an explicit ELSE clause; however, the ELSE is optional. When you do not explicitly specify an ELSE clause of your own, PL/SQL implicitly uses the following:

ELSE
   RAISE CASE_NOT_FOUND;

In other words, if you do not specify an ELSE clause, and none of the results in the WHEN clauses match the result of the CASE expression, PL/SQL will raise a CASE_NOT_FOUND error. This behavior is different from what we’re used to with IF statements. When an IF statement lacks an ELSE clause, nothing happens when the condition is not met. With CASE, the analogous situation leads to an error.

By now you’re probably wondering how, or even whether, the bonus logic shown earlier in this chapter can be implemented using a simple CASE statement. At first glance, it doesn’t appear possible. However, a bit of creative thought yields the following solution:

CASE TRUE 
WHEN salary >= 10000 AND salary <=20000 THEN
   give_bonus(employee_id, 1500);
WHEN salary > 20000 AND salary <= 40000
   give_bonus(employee_id, 1000);
WHEN salary > 40000
   give_bonus(employee_id, 500);
ELSE
   give_bonus(employee_id, 0);
END CASE;

The point we are trying to make with this solution, aside from the fact that you sometimes need to code creatively, is that the expression and result elements shown in the earlier syntax diagram can be either scalar values or expressions that evaluate to scalar values.

If you look back to the earlier IF-THEN-ELSIF statement implementing this same bonus logic, you’ll see that we specified an ELSE clause for the CASE implementation, whereas we didn’t specify an ELSE for the IF-THEN-ELSIF solution. The reason for the addition of the ELSE is simple: if no bonus conditions are met, the IF statement does nothing, effectively resulting in a zero bonus. A CASE statement, however, will raise an error if no conditions are met—hence the need to code explicitly for the zero bonus case.

While our previous CASE TRUE statement may look like a clever hack, it’s really an explicit implementation of the searched CASE statement, which we talk about in the next section.

Searched CASE Statements

A searched CASE statement evaluates a list of Boolean expressions and, when it finds an expression that evaluates to TRUE, executes a sequence of statements associated with that expression. Essentially, a searched CASE statement is the equivalent of the CASE TRUE statement shown in the previous section.

Searched CASE statements have the following form:

CASE 
WHEN expression1 THEN
   statements1
WHEN expression2 THEN
   statements2
...
ELSE
   statements_else
END CASE;

A searched CASE statement is a perfect fit for the problem of implementing the bonus logic. For example:

CASE
WHEN salary >= 10000 AND salary <=20000 THEN
   give_bonus(employee_id, 1500);
WHEN salary > 20000 AND salary <= 40000 THEN
   give_bonus(employee_id, 1000);
WHEN salary > 40000 THEN
   give_bonus(employee_id, 500);
ELSE
   give_bonus(employee_id, 0);
END CASE;

As with simple CASE statements, the following rules apply:

• Execution ends once a sequence of statements has been executed. If more than one expression evaluates to TRUE, only the statements associated with the first such expression are executed.

• The ELSE clause is optional. If no ELSE is specified and no expressions evaluate to TRUE, then aCASE_NOT_FOUND exception is raised.

• WHEN clauses are evaluated in order, from top to bottom.

Following is an implementation of our bonus logic that takes advantage of the fact that WHEN clauses are evaluated in the order in which we write them. The individual expressions are simpler, but is the intent of the statement as easily grasped?

CASE
WHEN salary > 40000 THEN
   give_bonus(employee_id, 500);
WHEN salary > 20000 THEN
   give_bonus(employee_id, 1000);
WHEN salary >= 10000 THEN
   give_bonus(employee_id, 1500);
ELSE
   give_bonus(employee_id, 0);
END CASE;

If a given employee’s salary is 20,000, then the first expression and second expression will evaluate to FALSE. The third expression will evaluate to TRUE, and that employee will be awarded a bonus of 1500. If an employee’s salary is 21,000, then the second expression will evaluate to TRUE, and the employee will be awarded a bonus of 1000. Execution of the CASE statement will cease with the first WHEN condition that evaluates to TRUE, so a salary of 21,000 will never reach the third condition.

It’s arguable whether you should take this approach to writing CASE statements. You should certainly be aware that it’s possible to write such a statement, and you should watch for such order-dependent logic in programs that you are called upon to modify or debug.

Order-dependent logic can be a subtle source of bugs when you decide to reorder the WHEN clauses in a CASE statement. Consider the following searched CASE statement in which, assuming a salary of 20,000, both WHEN expressions evaluate to TRUE:

CASE
WHEN salary BETWEEN 10000 AND 20000 THEN
   give_bonus(employee_id, 1500);
WHEN salary BETWEEN 20000 AND 40000 THEN
   give_bonus(employee_id, 1000);
...

Imagine the results if a future programmer unthinkingly decides to make the code neater by reordering the WHEN clauses in descending order by salary. Don’t scoff at this possibility! We programmers frequently fiddle with perfectly fine, working code to satisfy some inner sense of order. Following is the CASE statement rewritten with the WHEN clauses in descending order:

CASE
WHEN salary BETWEEN 20000 AND 40000 THEN
   give_bonus(employee_id, 1000);
WHEN salary BETWEEN 10000 AND 20000 THEN
   give_bonus(employee_id, 1500);
...

Looks good, doesn’t it? Unfortunately, because of the slight overlap between the two WHEN clauses, we’ve introduced a subtle bug into the code. Now an employee with a salary of 20,000 gets a bonus of 1000 rather than the intended 1500. There may be cases where overlap between WHEN clauses is desirable, but avoid it when feasible. Always remember that order matters, and resist the urge to fiddle with working code.

Tip

Because WHEN clauses are evaluated in order, you may be able to squeeze some extra efficiency out of your code by listing the most likely WHEN clauses first. In addition, if you have WHEN clauses with “expensive” expressions (e.g., requiring lots of CPU and memory), you may want to list those last in order to minimize the chances that they will be evaluated. See the previous discussion under Section 4.1.4 for an example of this issue.

Use searched CASE statements when you wish to use Boolean expressions as a basis for identifying a set of statements to execute. Use simple CASE statements when you can base that decision on the result of a single expression.

Nested CASE Statements

CASE statements can be nested just as IF statements can. For example, the following rather difficult-to-follow implementation of our bonus logic uses a nested CASE statement:

CASE
WHEN salary >= 10000 THEN
   CASE
   WHEN salary <= 20000 THEN
      give_bonus(employee_id, 1500);
   WHEN salary > 20000 THEN
      give_bonus(employee_id, 1000);
   END CASE;
WHEN salary > 40000 THEN
   give_bonus(employee_id, 500);
WHEN salary < 10000 THEN
   give_bonus(employee_id,0);
END CASE;

Any type of statement may be used within a CASE statement, so we could replace the inner CASE statement with an IF statement. Likewise, any type of statement, including CASE statements, may be nested within an IF statement.

CASE Expressions

CASE expressions do for expressions what CASE statements do for statements. Simple CASE expressions let you choose an expression to evaluate based on a scalar value that you provide as input. Searched CASE expressions evaluate a list of expressions to find the first one that evaluates to TRUE, and then return the results of an associated expression.

CASE expressions take the following two forms:

Simple_Case_Expression := 
   CASE expression
   WHEN result1 THEN
      result_expression1
   WHEN result2 THEN
      result_expression2
   ...
   ELSE 
      result_expression_else
   END;

Searched_Case_Expression :=
   CASE 
   WHEN expression1 THEN
      result_expression1
   WHEN expression2 THEN
      result_expression2
   ...
   ELSE
      result_expression_else
   END;

A CASE expression returns a single value, the result of whichever result expression is chosen. Each WHEN clause must be associated with exactly one expression (no statements). Do not use semicolons or END CASE to mark the end of the CASE expression. CASE expressions are terminated by a simple END.

Following is an example of a simple CASE expression being used with the DBMS_OUTPUT package to output the value of a Boolean variable. Recall thatPUT_LINE is not overloaded to handle Boolean types. In this example, the CASE expression converts the Boolean value in a character string, which PUT_LINE can then handle:

DECLARE
   boolean_true BOOLEAN := TRUE;
   boolean_false BOOLEAN := FALSE;
   boolean_null BOOLEAN;

   FUNCTION boolean_to_varchar2 (flag IN BOOLEAN) RETURN VARCHAR2 IS
   BEGIN
      RETURN 
      CASE flag
      WHEN TRUE THEN 'True'
      WHEN FALSE THEN 'False'
      ELSE 'NULL' END;
   END;

BEGIN
   DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_true));
   DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_false));
   DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_null));
END;

A searched CASE expression can be used to implement our bonus logic, returning the proper bonus value for any given salary:

DECLARE
  salary NUMBER := 20000;
  employee_id NUMBER := 36325;

  PROCEDURE give_bonus (emp_id IN NUMBER, bonus_amt IN NUMBER) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(emp_id);
    DBMS_OUTPUT.PUT_LINE(bonus_amt);
  END;

BEGIN
   give_bonus(employee_id,
              CASE
              WHEN salary >= 10000 AND salary <=20000 THEN 1500
              WHEN salary > 20000 AND salary <= 40000 THEN 1000
              WHEN salary > 40000 THEN 500
              ELSE 0
              END);
END;

You can use a CASE expression anywhere you can use any other type of expression. The following example uses a CASE expression to compute a bonus amount, multiplies that amount by 10, and assigns the result to a variable that is displayed via DBMS_OUTPUT:

DECLARE
  salary NUMBER := 20000;
  employee_id NUMBER := 36325;
  bonus_amount NUMBER;
BEGIN
   bonus_amount := 
      CASE
      WHEN salary >= 10000 AND salary <=20000 THEN 1500
      WHEN salary > 20000 AND salary <= 40000 THEN 1000
      WHEN salary > 40000 THEN 500
      ELSE 0
      END * 10;

   DBMS_OUTPUT.PUT_LINE(bonus_amount);
END;

Unlike with the CASE statement, no error is raised in the event that no WHEN clause is selected in a CASE expression. Instead, when no WHEN conditions are met, a CASE expression will return NULL.

CASE in SQL Statements

Support for CASE in SQL statements preceded support for CASE in PL/SQL; Oracle8i introduced the searched CASE expression in SQL. Unfortunately, much to the frustration of PL/SQL programmers everywhere, SQL statements executed from within PL/SQL could not use CASE expressions. For example, from SQL*Plus you could execute:

SELECT 
   CASE 
      WHEN DUMMY='X' THEN 'Dual is OK'
      ELSE 'Dual is messed up'
   END
FROM DUAL;

However, the following PL/SQL code would not work:

DECLARE
   dual_message VARCHAR2(20);
BEGIN
   SELECT CASE 
            WHEN DUMMY='X' THEN 'Dual is OK'
            ELSE 'Dual is messed up'
            END INTO dual_message
   FROM DUAL;
   DBMS_OUTPUT.PUT_LINE (dual_message);
END;

This frustration goes away in Oracle9i. In Oracle9i, you can have both searched and simple CASE statements, and they can be used equally from SQL, from PL/SQL, and from SQL within PL/SQL.