IF/THEN/ELSIF/ELSE

In the example below, a number of IF statements are used.

if n_numb = 1 then
   v_status := ‘very small’; 
end if;

if n_numb <= 4  then
   v_status := ‘small’; 
end if;

if n_numb = 5 then
   v_status := ‘even’; 
end if;

if n_numb > 5 then
   v_status := ‘large’; 
end if;

if n_numb = 10 then
   v_status := ‘very large’; 
end if;

The set of statements has a number of problems, not the least of which is that some values will satisfy multiple statements.  You could fix that problem by reordering the statements.  However, all five statements will still have to be evaluated for each run. 

By using the ELSIF  statement, the program will jump past the remaining evaluations once a true condition is reached.

if n_numb = 1 then
   v_status := ‘very small’;
   elsif
      n_numb < 4 then v_status := ‘small’;
      elsif
         n_numb = 5 then v_status := ‘even’;
         elsif
            n_numb < 4 then v_status := ‘large’;
         else
            v_status := ‘very large’;
end if;

Notice that once the condition evaluates to true, the THEN clause is executed and the control will jump over all other statements to the END IF clause.  This method is called a ‘short circuit’ evaluation.  Also note these features of the syntax:

• The ELSE clause at the end is optional.  If we include the final ELSE, the ELSE clause is executed when the conditions above it all evaluate to false. 
   

• Each ELSIF clause must have a condition evaluation and a THEN clause . 
   

• The ELSE clause has no condition evaluation and the THEN key word is omitted.

The THEN clause  can contain any series of valid PL/SQL statements including calling procedures, functions, nested blocks of code, or nested IF statements.

SQL> declare
  2    n_temp number(8,2) := &Temp_f;
  3    v_results varchar2(40);
  4  begin
  5    -- Change from Fahrenheit to Celsius
  6    n_temp := (5/9)*(n_temp -32);
  7
  8    if n_temp > 40
  9      then v_results := 'WARM';
 10    elsif n_temp <= 40 and n_temp > 0
 11      then v_results := 'Cold';
 12    else v_results := 'Very Cold';
 13    end if;
 14
 15    dbms_output.put_line ('The Temp Outside is '||v_results);
 16    dbms_output.put_line ('It is '||n_temp||' C.');
 17  end;
 18  /
Enter value for temp_f: 55
The Temp Outside is Cold
It is 12.78 C.

PL/SQL procedure successfully completed.

SQL> /

Enter value for temp_f: 5
The Temp Outside is Very Cold
It is -15 C.

In the example above, a SQL*Plus variable is passed to the anonymous block (verify was turned off with the SQL*Plus command ‘set verify off’).  The temperature contained in the variable Temp_f  is in Fahrenheit.  It is converted on line 6 to degrees Celsius.   Lines 8 through 13 use the IF statement to define how cold it is.  The results are then printed out using the dbms_output.put_line procedure.  Notice that the ELSIF  clause on line 10 uses a compound condition with the AND keyword.  The compound condition can contain as many evaluations as needed as long as the entire evaluate ends up either true or false.   Now, let’s look at some more complex Boolean  operations with multiple logical operators.