Multiple Logical Operators

The PL/SQL language is very powerful and any IF condition evaluation can consist of multiple comparisons, joined together by logical operators.  In the next example, the IF condition on line 6 contains three evaluations, joined by the AND/OR logical operators . 

SQL> declare
  2    n_test number := &Entry;
  3    n_10   number := 10;
  4    n_100  number := 100;
  5  begin
  6    if (n_test < n_100 and n_test > n_10 or n_test = 5)
  7      then dbms_output.put_line ('True');
  8    else dbms_output.put_line ('False');
  9    end if;
 10  end;
 11  /
Enter value for entry: 25
old   2:   n_test number := &Entry;
new   2:   n_test number := 25;
True

PL/SQL procedure successfully completed.

SQL> /
Enter value for entry: 5
old   2:   n_test number := &Entry;
new   2:   n_test number := 5;
True

PL/SQL procedure successfully completed.

SQL> /
Enter value for entry: 6
old   2:   n_test number := &Entry;
new   2:   n_test number := 6;
False

When using multiple conditions, the conditions are evaluated in the same order as in an SQL statement’s WHERE clause, with all ANDs, then all ORs are evaluated from left-to-right.  To demonstrate this I made a change in the example below so that the AND evaluation results in a false condition.  Please hand-execute this code:

SQL> declare
  2    n_test number := &Entry;
  3    n_10   number := 10;
  4    n_100  number := 100;
  5  begin
  6    if (n_test > n_100 and n_test > n_10 or n_test = 5)
  7      then dbms_output.put_line ('True');
  8    else dbms_output.put_line ('False');
  9    end if;
 10  end;
 11  /
Enter value for entry: 5
old   2:   n_test number := &Entry;
new   2:   n_test number := 5;
True

In this example we see that the ANDs evaluated to “false”, but the OR was tested last and the overall statement evaluated to “true”.

When evaluating large numbers of conditions, the IF/THEN/ELSIF /ELSE statements can become long and confusing.  Using a CASE statement  may make your code more understandable and less error prone.