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.

No comments:

Post a Comment