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 expressionWHEN result1 THEN statements1WHEN result2 THEN statements2...ELSE statements_elseEND 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_typeWHEN '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.