Oracle PL/SQL Control Structures: A Comprehensive Guide

Control structures in Oracle PL/SQL let you control the flow of your program: making decisions, looping through data, and exiting loops. These are essential for writing flexible, efficient, and readable code. Below, we’ll explore the main types of control structures:

1. Conditional Statements – IF-THEN, IF-THEN-ELSE, CASE
2. Looping Constructs – LOOP, WHILE, FOR, CONTINUE
3. EXIT and EXIT WHEN statements

1. Conditional Statements

A. IF-THEN

Use IF-THEN when you want code to execute only if a condition is true. If it’s false, PL/SQL simply skips the block.

Syntax:

IF condition THEN
    statements;
END IF;

Example: Check if a salary exceeds a threshold.

DECLARE
    v_salary NUMBER := 60000;
BEGIN
    IF v_salary > 50000 THEN
        DBMS_OUTPUT.PUT_LINE('High salary: ' || v_salary);
    END IF;
END;
/

B. IF-THEN-ELSE

Adds an alternative block if the condition is false.

Syntax:

IF condition THEN
    statements_if_true;
ELSE
    statements_if_false;
END IF;

Example: Categorizing employees.

DECLARE
    v_experience NUMBER := 3;
BEGIN
    IF v_experience >= 5 THEN
        DBMS_OUTPUT.PUT_LINE('Senior employee');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Junior employee');
    END IF;
END;
/

C. CASE Statement

Use CASE for multiple conditions. It’s cleaner than chaining multiple IF-ELSE blocks.

Syntax (searched CASE):

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result_default
END;

Example: Grade evaluation.

DECLARE
    v_score NUMBER := 85;
    v_grade VARCHAR2(2);
BEGIN
    v_grade := CASE 
                 WHEN v_score >= 90 THEN 'A'
                 WHEN v_score >= 80 THEN 'B'
                 WHEN v_score >= 70 THEN 'C'
                 ELSE 'F'
               END;
    DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);
END;
/

2. Looping Constructs

Loops execute a block of code multiple times until a condition is met. Oracle provides several types of loops.

A. Basic LOOP

The simplest form. You must explicitly EXIT or it runs infinitely.

Syntax:

LOOP
    statements;
    EXIT WHEN condition;
END LOOP;

Example: Print numbers from 1 to 5.

DECLARE
    i NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Value: ' || i);
        i := i + 1;
        EXIT WHEN i > 5;
    END LOOP;
END;
/

B. WHILE Loop

Checks the condition before each iteration.

Syntax:

WHILE condition LOOP
    statements;
END LOOP;

Example: Countdown.

DECLARE
    counter NUMBER := 5;
BEGIN
    WHILE counter > 0 LOOP
        DBMS_OUTPUT.PUT_LINE('Countdown: ' || counter);
        counter := counter - 1;
    END LOOP;
END;
/

C. FOR Loop

Automatically handles the loop counter and increments. You can loop forward or backward using REVERSE.

Syntax:

FOR counter IN [REVERSE] lower_bound..upper_bound LOOP
    statements;
END LOOP;

Example: Print even numbers between 2 and 10.

BEGIN
    FOR i IN 2..10 LOOP
        IF MOD(i,2)=0 THEN
            DBMS_OUTPUT.PUT_LINE('Even: ' || i);
        END IF;
    END LOOP;
END;
/

Reverse Example:

BEGIN
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Reverse: ' || i);
    END LOOP;
END;
/

D. CONTINUE Statement

Skips the current loop iteration and moves to the next one (available in Oracle 11g+).

Example: Skip odd numbers.

BEGIN
    FOR i IN 1..6 LOOP
        CONTINUE WHEN MOD(i,2) = 1;
        DBMS_OUTPUT.PUT_LINE('Even number: ' || i);
    END LOOP;
END;
/

3. EXIT and EXIT WHEN Statements

EXIT

Exits the loop immediately.

Example: Stop processing early.

DECLARE
    v_num NUMBER := 0;
BEGIN
    LOOP
        v_num := v_num + 1;
        DBMS_OUTPUT.PUT_LINE('Number: ' || v_num);
        IF v_num = 3 THEN
            EXIT; -- Leave the loop at 3
        END IF;
    END LOOP;
END;
/

EXIT WHEN

Combines EXIT with a condition in one line—cleaner syntax.

Example: Exit after reaching 5.

DECLARE
    v_counter NUMBER := 0;
BEGIN
    LOOP
        v_counter := v_counter + 1;
        DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
        EXIT WHEN v_counter >= 5;
    END LOOP;
END;
/

Best Practices

• Use CASE instead of multiple IF-THEN-ELSE for readability.
• Use FOR loops when you know the range in advance.
• Use EXIT WHEN to simplify loop exit conditions.
• Always include an EXIT condition in LOOP to avoid infinite loops.
• Use CONTINUE to skip unnecessary iterations and improve performance.

Summary Table

Final Takeaway

Oracle control structures are the decision-making and repetition backbone of PL/SQL. They make your programs smarter—able to choose paths (IF, CASE), repeat tasks (LOOP, WHILE, FOR), and terminate loops safely (EXIT, EXIT WHEN). By combining these tools, you can handle everything from payroll calculations to automated data migrations with clean, efficient code.