If Else in PL SQL - Fully Explained with Examples

Learn if else in PL SQL and more conditional statements used in Oracle viz if-then-end if | if-then-else-end if | if-then-elseif-endif | Nested if statements with examples.

PL SQL Conditional Statements

Sequence of statements can be executed using IF statement based on the condition evaluation. This conditions are decision making statements which sequence of statements to execute.

Three forms of conditional statements exists in Oracle PL/SQL.

IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSIF-ENDIF

IF-THEN-END IF

This is a simple IF statement, if CONDITION Evaluates to true then it executes sequence of statements in the IF-THEN-END IF block.
Syntax:

IF [CONDITION] THEN
    ----SEQUENCE OF STATEMENTS
END IF;

Example:

Pl Sql program for displaying if a number is greater than another one.

DECLARE
  a NUMBER:=10;
  b NUMBER:=20;
BEGIN
  IF b > a THEN
    dbms_output.put_line('B is Greater Than A');
  END IF;
END;

Output:
B is Greater Than A

NOTES:

IF-THEN marks the beginning of the IF statement
END IF marks the end of the IF THEN Statement.
IF -THEN -END IF are Reserved keywords.
Conditions are compared to boolean values saying to either true or false.
If the condition is true, then statements in the IF-THEN – END IF is executes
if the condition is false or null then control pass to end of END IF block.

IF-THEN-ELSE-END IF

This is a IF ELSE statement, if CONDITION Evaluates to true then it executes sequence of statements in the IF-THEN- block otherwise it executes sequence of statements that exist in the ELSE block.
Syntax

IF [CONDITION] THEN  --if condition is true then statements in the IF THEN block
        --is executes
  ---SEQUENCE OF STATEMENTS
ELSE –statements in the else block executes if condition fails in IF THEN block
   --SEQUECE OF STATEMENTS
END IF;

If-then-else-end if Examples

Program to check if value of variable a is > that b then print “B is Greater Than A” else print “A is Greater Than B”.

DECLARE
  a NUMBER:=20; --values assigned at compile time
  b NUMBER:=10;--values assigned at compile time
BEGIN
  IF b > a THEN
    dbms_output.put_line('B is Greater Than A');
  ELSE
    dbms_output.put_line('A is Greater Than B');
  END IF;
END;

Output
A is Greater Than B

IF-THEN-ELSIF-ENDIF

This is a IF ELSE lader statement, if CONDITION Evaluates to true then it executes sequence of staements in the IF-THEN- block otherwise it executes sequence of statements that exist in the ELSIF block if the condition in ELSIF block is true.

It verifies all the ELSIF statements until the condition is true otherwise it executes the sequence statements of in the ELSE block.

Syntax:

IF CONDITON1 THEN
       --   SEQUENCE OF STATEMENTS IN  --S1
ELSIF CONDITION2 THEN
       --SEQUENCE OF STATEMENTS  -- S2
ELSIF CONDITION3  THEN
     --SEQUENCE OF STATEMENTS  -- S3
.............

ELSE
        --SEQUENCE OF STATEMENTS  -- SN
END IF

If-Then-Elsif-Endif Example

DECLARE
  a NUMBER:=10;
BEGIN
  IF a > 0 THEN
    dbms_output.put_line('GIVEN NUMBER POSSITIVE');
 ELSIF a = 0 THEN
     dbms_output.put_line('GIVEN NUMBER ZERO');
 ELSIF A < 0 THEN
 dbms_output.put_line('GIVEN NUMBER NEGATIVE');
           
 END IF;
END;

Output
GIVEN NUMBER NEGATIVE

NESTED IF STATEMENTS

IF statement with in the IF statement is called as Nested IF statements. NESTED IF statements are used if we have a series of conditions exist.

Syntax:

IF [CONDITION] THEN
	IF [CONDITION1] THEN
    	  	IF  [CONDITION2] THEN
                   --  STAEMENT1
     	 	ELSE
          	         -- STATEMENT 2
      		END IF;
           ELSE
                 --STATEMENT 3
            END IF;

ELSE
       ---STATEMENT 4
            IF [CONDITION3] THEN
    	  	IF  [CONDITION4] THEN
                   --  STAEMENT1
     	 	ELSE
          	         -- STATEMENT 2
      		END IF;
           ELSE
                 --STATEMENT 3
            END IF;

END IF

Nested if Statements Example

DECLARE
  a NUMBER:=80;

BEGIN
  IF a < 20 THEN IF a >=10 THEN
      dbms_output.put_line('NUMBER IS IN BETWEEN 10 TO 19');
    ELSE
    dbms_output.put_line('NUMBER IS LESS THAN  OR EQUALS TO 9');
    END IF;
 ELSE
     IF a >= 20 and a< 50 THEN
       dbms_output.put_line('NUMBER IS IN BETWEEN 20 TO 49');
      
      ELSE
        dbms_output.put_line('NUMBER IS GREATER THAN  OR EQUALS TO 50');
      END IF;
           
 END IF;

END;

OUTPUT
NUMBER IS GREATER THAN OR EQUALS TO 50

NOTES:

Behaviour of NULL’s In Conditional Statements

If a condition is evaluated to NULL in simple IF statement then the statement in TRUE statement will not be executed, instead it will the control first statement after END IF;
If a condition is evaluated to NULL in IF ELSE statement then the statement in TRUE statement will not be executed, instead it will Execute statements in the ELSE block.

It is good to check whether variable contains NULL value before conditional process execution starts.

MORE ON CONDITIONAL- Logical conditions AND | OR

LOGICAL CONNECTIVITY are AND and OR operators.
Syntax:

IF CONDITION1 AND CONDITION2 THEN
    STATEMENT1;
    STATEMENT2;
ELSE
   STATEMENT3;
   STATEMENT4;
END IF;

Syntax2
IF CONDITION1 OR CONDITION2 THEN
    STATEMENT1;
    STATEMENT2;
ELSE
   STATEMENT3;
   STATEMENT4;
END IF;

Syntax3
IF CONDITION1 AND CONDITION2 OR CONDITION3 THEN
    STATEMENT1;
    STATEMENT2;
ELSE
   STATEMENT3;
   STATEMENT4;
END IF;

If Else in PL SQL – Fully Explained with Examples