Simple CASE statement

When in your PL/SQL code the IF statement is not enough, here comes the CASE to help.

In its simple form it looks like this:

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

Actually, we could use it in an even simpler form, with just a branch and no final else.
Here is a first example, where the we write something to the output buffer if the country is in region 1:

declare
  l_id varchar2(2) := 'IT';
  l_region integer;
begin
  select region_id
  into l_region
  from countries
  where country_id = l_id;
  
  case l_region
  when 1 then
    dbms_output.put_line(l_id || ' is in Europe');
  end case;
exception
  when no_data_found then
    dbms_output.put_line('No ' || l_id || ' among the country ids.');
  when case_not_found then
    dbms_output.put_line('case not found');  
  when others then
    dbms_output.put_line('unexpected');  
end;

We catch a case_not_found exeception here, that is what the CASE throw when it happens that no branch is actually selected. If you put 'EG' in l_id you'll what I mean. (I even add a catch-all clause, just to see it at work)

It's not a good idea throw-catching exceptions, if there is way to avoid it. In this case we can make good use of the ELSE clause in our CASE:

case l_region
when 1 then
  dbms_output.put_line(l_id || ' is in Europe');
else
  dbms_output.put_line('I don't know where ' || l_id || ' is');
end case;

This lead to the generic case for the simple case, with a number of WHEN clauses, and a final ELSE:

case l_region
when 1 then
  dbms_output.put_line(l_id || ' is in Europe');
when 2 then
  dbms_output.put_line(l_id || ' is in America');
when 3 then
  dbms_output.put_line(l_id || ' is in East - Far East');
when 4 then
  dbms_output.put_line(l_id || ' is in Middle East - Africa');
else
  dbms_output.put_line('I don''t know where ' || l_id || ' is');
end case;

I'm writing this post while skimming through the fourth chapter (about conditional and sequential control) of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein. It's a good text on PL/SQL, if you are looking for a sort of reference book.