Searched CASE statement

The simple CASE statement makes more readable our PL/SQL code when we want to compare the result from an expression evaluation with a bunch of alternatives.

If we have more complex decision to take, it could come in handy a different form of CASE, the so called "searched" one:

WHEN expression THEN (...)
ELSE (...)

The structure is close to the "simple" form, but we have multiple expressions, one for each WHEN, all of them are sequentially executed, until we find one that evaluates to true. If none is found, we execute the code in the ELSE clause. If no ELSE clause is provided, the statement throw a case_not_found exception, just like the "simple" CASE.

Here is an example of searched case, based on the oracle hr test schema. We read a salary of a employee than we print a message accordingly to its value. We have a few cases. A salary is considered "top" if it is greater that 20000; high if in (10000, 20000]; average if in (6000, 10000]; low if in [3000, 6000]; and minimal otherwise, that means less than 3000:

l_id number(6,0) := 100;
l_salary number(8,2);
select salary
into l_salary
from employees
where employee_id = l_id;

when l_salary > 20000 then
dbms_output.put_line('Top salary');
when l_salary > 10000 then
dbms_output.put_line('High salary');
when l_salary > 6000 then
dbms_output.put_line('Average salary');
when l_salary >= 3000 then
dbms_output.put_line('Low salary');
dbms_output.put_line('Minimal salary');
end case;
when no_data_found then
dbms_output.put_line('No ' || l_id || ' among the employees.');
when others then

The chapter 4 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is about conditional and sequential control. There you would find a lot more info on CASE statements, searched or simple.

No comments:

Post a Comment