Control Statements and Looping Mechanisms in Oracle PL/SQL

PL/SQL provides control statements to control the flow of execution and loops to execute a block of statements repeatedly. In this blog, we will cover IF statements, nested conditions, and looping mechanisms with examples.

1. IF THEN Statement

The IF THEN statement allows execution of a block only if a condition is true.

Syntax

IF (condition) THEN
    statement;
END IF;

Example

DECLARE
   no INTEGER(2) := 14;
BEGIN
   IF (no = 14) THEN
      DBMS_OUTPUT.PUT_LINE('Condition true');  
   END IF;
END;
/

Result

Condition true
PL/SQL procedure successfully completed.

2. IF THEN ELSE Statement

The IF THEN ELSE statement allows execution of one block if the condition is true, otherwise another block is executed.

Syntax

IF (condition) THEN
    statement;
ELSE
    statement; 
END IF;

Example

DECLARE
   no INTEGER(2) := 14;
BEGIN
   IF (no = 11) THEN
      DBMS_OUTPUT.PUT_LINE(no || ' is same');  
   ELSE
      DBMS_OUTPUT.PUT_LINE(no || ' is not same');
   END IF;
END;
/

Result

14 is not same
PL/SQL procedure successfully completed.

3. IF THEN ELSIF Statement

The IF THEN ELSIF statement allows multiple conditions to be checked sequentially.

Syntax

IF (condition-1) THEN
    statement-1;
ELSIF (condition-2) THEN
    statement-2;
ELSIF (condition-3) THEN
    statement-3;   
ELSE
    statement;
END IF;

Example: (Student grade example)

DECLARE
   result CHAR(20) := 'second';
BEGIN
   IF (result = 'distinction') THEN
      DBMS_OUTPUT.PUT_LINE('First Class with Distinction');  
   ELSIF (result = 'first') THEN
      DBMS_OUTPUT.PUT_LINE('First Class'); 
   ELSIF (result = 'second') THEN
      DBMS_OUTPUT.PUT_LINE('Second Class'); 
   ELSIF (result = 'third') THEN
      DBMS_OUTPUT.PUT_LINE('Third Class');        
   ELSE
      DBMS_OUTPUT.PUT_LINE('Fail');
   END IF;
END;
/

Result

Second Class
PL/SQL procedure successfully completed.

4. Nested IF THEN ELSE Statement

Nested IF THEN ELSE statements are logically similar to IF THEN ELSIF. Each condition is checked inside the else block.

Syntax

IF (condition-1) THEN
    statement-1;
ELSE
    IF (condition-2) THEN
        statement-2;
    ELSE
        IF (condition-3) THEN
            statement-3;
        END IF;
    END IF;
END IF;

Example: (Gender and grade check)

DECLARE
    gender CHAR(20) := 'female';
    result CHAR(20) := 'second';
BEGIN
   IF (gender = 'male') THEN
      DBMS_OUTPUT.PUT_LINE('Gender Male Record Skip!'); 
   ELSE
       IF (result = 'distinction') THEN
          DBMS_OUTPUT.PUT_LINE('First Class with Distinction');  
       ELSIF (result = 'first') THEN
          DBMS_OUTPUT.PUT_LINE('First Class'); 
       ELSIF (result = 'second') THEN
          DBMS_OUTPUT.PUT_LINE('Second Class'); 
       ELSIF (result = 'third') THEN
          DBMS_OUTPUT.PUT_LINE('Third Class');        
       ELSE
           DBMS_OUTPUT.PUT_LINE('Fail');
       END IF;
   END IF;
END;
/

Result

Second Class
PL/SQL procedure successfully completed.

Note: Once a condition evaluates TRUE, the corresponding block executes and the rest are skipped.

PL/SQL Loops

Loops in PL/SQL allow you to repeat a block of statements. There are three types of loops:

Basic LOOP
WHILE LOOP
FOR LOOP

Oracle recommends using labels for loops to improve readability (optional).

1. Basic LOOP

Syntax

[ label_name ] LOOP
   statements;
END LOOP [ label_name ];

Example

DECLARE
   no NUMBER := 5;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Inside value: no = ' || no);
        no := no - 1;
        IF no = 0 THEN
            EXIT;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Outside loop end');   
END;
/

Result

Inside value: no = 5
Inside value: no = 4
Inside value: no = 3
Inside value: no = 2
Inside value: no = 1
Outside loop end
PL/SQL procedure successfully completed.

2. WHILE LOOP

Syntax

[ label_name ] WHILE condition LOOP
   statements;
END LOOP [ label_name ];

Example

DECLARE
   no NUMBER := 0;
BEGIN
    WHILE no < 10 LOOP
        no := no + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Sum :' || no);   
END;
/

Result

Sum : 10
PL/SQL procedure successfully completed.

3. FOR LOOP

Syntax

[ label_name ] FOR current_value IN [ REVERSE ] lower_value..upper_value LOOP
   statements;
END LOOP [ label_name ];

Example

BEGIN
    FOR no IN 1 .. 5 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration : ' || no);
    END LOOP;
END;
/

Result

Iteration : 1
Iteration : 2
Iteration : 3
Iteration : 4
Iteration : 5
PL/SQL procedure successfully completed.

✅ Conclusion

IF statements help control flow based on conditions.
Nested IF and ELSIF statements allow multiple checks.
Loops (BASIC, WHILE, FOR) help repeat statements efficiently.
Labels are optional but improve readability.

This forms the foundation of control flow and looping in Oracle PL/SQL.