The CASE statement allows you to select one sequence of statements to execute out of many possible sequences. They have been part of the SQL standard since 1992, although Oracle SQL didn’t support CASE until the release of Oracle8i Database, and PL/SQL didn’t support CASE until Oracle9i Database Release 1. From this release onwards, the following types of CASE statements are supported:
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 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 don’t specify an ELSE clause, and none of the results in the WHEN clauses match the result of the CASE expression, PL/SQL raises a CASE_NOT_FOUND error. This behavior is different from what I’m 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 THEN give_bonus(employee_id, 1000); WHEN salary > 40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END CASE;
The key point to note here 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 I specified an ELSE clause for the CASE implementation, whereas I 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.
To avoid CASE_NOT_FOUND errors, be sure that it’s impossible for one of your conditions not to be met.
While my previous CASE TRUE statement may look like a clever hack, it’s really an explicit implementation of the searched CASE statement, which I 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.
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 a CASE_NOT_FOUND exception is raised.
Following is an implementation of my bonus logic that takes advantage of the fact that WHEN clauses are evaluated in the order in which I 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 $1,500. 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 $1,000. 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, I’ve introduced a subtle bug into the code. Now an employee with a salary of $20,000 gets a bonus of $1,000 rather than the intended $1,500. 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. “If it ain’t broke, don’t fix it.”
Since 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 Nested IF Statements for an example of this issue.
Use searched CASE statements when you want 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 > 40000 THEN give_bonus(employee_id, 500); WHEN salary > 20000 THEN give_bonus(employee_id, 1000); END CASE; WHEN salary < 10000 THEN give_bonus(employee_id,0); END CASE;
Any type of statement may be used within a CASE statement, so I 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 that the PUT_LINE program is not overloaded to handle Boolean types.) In this example, the CASE expression converts the Boolean value into 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 my 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 or value. 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;