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 statements1WHEN expression2 THEN statements2...ELSE statements_elseEND CASE;A searched CASE statement is a perfect fit for the problem of implementing the bonus logic. For example:
CASEWHEN 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?
CASEWHEN 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:
CASEWHEN 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:
CASEWHEN 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.
|
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.