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:
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.
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.
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
the two results match,
statements1 is executed.
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;
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
elements shown in the earlier syntax diagram can be either
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.
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:
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.
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.
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.
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 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
result_expression_elseEND; Searched_Case_Expression := CASE WHEN
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;
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;