IF statement

The PL/SQL IF statements comes in three flavours:

IF THEN (...) END IF;
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:

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

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

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')
then
  dbms_output.put_line(l_id || ' stands for Italy');
else
  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.