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

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