Oracle PL/SQL Tutorial - PL/SQL IF

The IF statement allows you to implement conditional branching logic.

The IF statement comes in three flavors

IF THEN END IF;
IF THEN ELSE END IF;
IF THEN ELSIF ELSE END IF;

IF-THEN Statement

The simplest form of IF statement has a condition with a sequence of statements enclosed by the keywords THEN and END IF.

The sequence of statements is executed only if the condition is TRUE.

If the condition is FALSE or NULL, the IF statement does nothing.

The general format of the IF-THEN syntax is as follows:


     IF condition 
     THEN 
         ... sequence of executable statements ... 
     END IF;

The condition is a Boolean variable, constant, or expression that evaluates to TRUE, FALSE, or NULL.

If condition evaluates to TRUE, the executable statements between the THEN keyword and the matching END IF statement are executed.

If condition evaluates to FALSE or NULL, those statements are not executed.

The following IF condition compares two different numeric values.

If one of these two values is NULL, then the entire expression returns NULL.


     IF salary > 400
     THEN 
        raise_salary(employee_id,5); 
     END IF;

We can use IS NULL to test for the presence of a NULL:


     IF salary > 400 OR salary IS NULL 
     THEN 
        raise_salary (employee_id,5); 
     END IF;

Example.


DECLARE -- from   w w w.j a  v  a 2  s.  co m
  n_val1  NUMBER(4,2) := 1; 
  n_val2  NUMBER(4,2) := 2; 
  n_val3  NUMBER(4,2) := 0; 
  emp_id NUMBER(6) := 120; 
BEGIN 
  IF n_val1 > (n_val2 + 2) THEN 
     n_val3 := (n_val1 - n_val2)/4; 
  END IF; 
END; 
/

IF-THEN-ELSE Statement

IF-THEN-ELSE format chooses between two mutually exclusive actions.

The format of this either/or version of the IF statement is as follows:


     IF condition 
     THEN 
         ... TRUE sequence of executable statements ... 
     ELSE 
         ... FALSE/NULL sequence of executable statements ... 
     END IF;

Following is an example of the IF-THEN-ELSE construct.


     IF salary <= 400 
     THEN 
        raise_salary (employee_id, 0); 
     ELSE 
        raise_salary (employee_id, 500); 
     END IF;

If the salary could be NULL, you can protect yourself against this problem using the NVL function:


     IF NVL(salary,0) <= 400
     THEN 
        raise_salary (employee_id, 0); 
     ELSE 
        raise_salary (employee_id, 500); 
     END IF;

IF statement can have the keyword ELSE followed by an alternative sequence of statements.

The statements in the ELSE clause are executed only if the condition is FALSE or NULL.


DECLARE --  w  w w  . j  a  v a  2 s . c om
  n_val1  NUMBER(8,2) := 1; 
  n_val2  NUMBER(8,2) := 2; 
  n_val3  NUMBER(6,2); 
  emp_id NUMBER(6) := 2; 
BEGIN 
  IF n_val1 > (n_val2 + 2) THEN 
    n_val3 := (n_val1 - n_val2)/4; 
  ELSE 
    n_val3 := 50; 
  END IF; 
  DBMS_OUTPUT.PUT_LINE(n_val3); 
END; 
/

The code above generates the following result.

Nested IF

IF statements can be nested.

You can nest any IF statement within any other IF statement.

The following IF statement shows several layers of nesting:


     IF condition1 
     THEN -- from   w ww  . java  2  s  .  com
        IF condition2 
        THEN 
           statements2 
        ELSE 
           IF condition3 
           THEN 
              statements3 
           ELSIF condition4 
           THEN 
              statements4 
           END IF; 
        END IF; 
     END IF;

The following code shows nested IF-THEN-ELSE statements.


DECLARE -- from   w  w w . j  a  va2 s  . c o m
  n_val1  NUMBER(8,2) := 1; 
  n_val2  NUMBER(8,2) := 2; 
  n_val3  NUMBER(6,2); 
BEGIN 
  IF n_val1 > (n_val2 + 2) THEN 
    n_val3 := (n_val1 - n_val2)/4; 
  ELSE 
    IF n_val1 > n_val2 THEN 
      n_val3 := 50; 
    ELSE 
      n_val3 := 0; 
    END IF; 
  END IF; 
END; 
/

IF-THEN-ELSIF Statement

IF-THEN-ELSIF Combination allows us to implement logic that has many alternatives.

The general format for this variation of IF is:


     IF condition-1 
     THEN 
        statements-1 
     ELSIF condition-N 
     THEN 
        statements-N 
     [ELSE 
        else_statements] 
     END IF;

To choose between several alternatives, use the keyword ELSIF to check additional conditions.

If the first condition is FALSE or NULL, the ELSIF clause tests another condition.

The final ELSE clause is optional.

Conditions are evaluated one by one from top to bottom.


DECLARE -- from ww  w  .  ja v  a2  s  . c om
  n_val1  NUMBER(8,2) := 2; 
  n_val3  NUMBER(6,2); 
  emp_id NUMBER(6)   := 1; 
BEGIN 
  IF n_val1 > 5 THEN 
    n_val3 := 5; 
  ELSIF n_val1 > 3 THEN 
    n_val3 := 3; 
  ELSE 
    n_val3 := 1; 
  END IF; 
 
END; 
/

The following code has many branches.


DECLARE -- www. j a  va2  s.  c o  m
  grade CHAR(1); 
BEGIN 
  grade := 'A'; 
 
  IF grade = 'A' THEN 
    DBMS_OUTPUT.PUT_LINE('Excellent'); 
  ELSIF grade = 'B' THEN 
    DBMS_OUTPUT.PUT_LINE('Very Good'); 
  ELSIF grade = 'C' THEN 
    DBMS_OUTPUT.PUT_LINE('Good'); 
  ELSIF grade = 'D' THEN 
    DBMS_OUTPUT. PUT_LINE('Fair'); 
  ELSIF grade = 'F' THEN 
    DBMS_OUTPUT.PUT_LINE('Poor'); 
  ELSE 
    DBMS_OUTPUT.PUT_LINE('No such grade'); 
  END IF; 
END; 
/

The code above generates the following result.

Short-Circuit Evaluation

PL/SQL uses short-circuit evaluation.

PL/SQL need not evaluate all of the expression in an IF statement.

For example, when evaluating the expression in the following IF statement, PL/SQL stops evaluation and immediately executes the ELSE branch if the first operand is either FALSE or NULL:


     IF condition1 AND condition2 
     THEN 
        ... 
     ELSE 
        ... 
     END IF;

PL/SQL can stop evaluation of the expression when condition1 is FALSE or NULL, because the THEN branch is executed only when the result of the expression is TRUE.

If the first operand of an OR operation in an IF statement is TRUE, PL/SQL immediately executes the THEN branch:


     IF condition1 OR condition2 
     THEN 
        ... 
     ELSE 
        ... 
     END IF;