IF statement

The PL/SQL IF statements comes in three flavours:

IF THEN (...) ELSE (...) END IF;
IF THEN (...) ELSIF (...) ELSE (...) END IF;

From Oracle 9 onwards, there's no much use for the third form, since the CASE statement is available and preferred.

As an example of the first form, we write an anonymous PL/SQL block that performs a select on the country_name table putting the found country_name in the local variable l_country. Then we use the IF statement to check if the found country is the expected one. If so, a message is sent to the output buffer:

l_country varchar2(40);
l_id varchar2(2) := 'IT';
select country_name
into l_country
from countries
where country_id = l_id;

if(l_country = 'Italy')
dbms_output.put_line(l_id || ' stands for Italy');
end if;
when no_data_found
dbms_output.put_line('No ' || l_id || ' among the country ids.');
when value_error
dbms_output.put_line('Check your local variables...');

What if the country_id specified does not exist in the table? Well, an exception is raised (no_data_found). Another exception could be raised if case we have a mismatch between our local variable types and the actual data we put in them. So, for instance, if we put 'ITA' in l_id we should have a value_error exception.

As example for the second form, consider a variation on the previous example. Now we want to output something also in the case we don't find the expected match. We change just the IF block, that now is:

if(l_country = 'Italy')
dbms_output.put_line(l_id || ' stands for Italy');
dbms_output.put_line(l_id || ' does not stand for Italy');
end if;

No example for the third form, let's use a CASE instead.

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