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:

CASE
WHEN expression THEN (...)
...
ELSE (...)
END CASE;

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:

declare
  l_id number(6,0) := 100;
  l_salary number(8,2);
begin
  select salary
  into l_salary
  from employees
  where employee_id = l_id;
  
  case
  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');
  else
    dbms_output.put_line('Minimal salary');
  end case;
exception
  when no_data_found then
    dbms_output.put_line('No ' || l_id || ' among the employees.');
  when others then
    dbms_output.put_line('unexpected');  
end;

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.