Oracle PL/SQL – Control Structures

Subject: Information Technology – Database Systems
Grade: 12th Grade – HTL Computer Science
Prerequisites: PL/SQL Introduction, anonymous block, variables
Author: HTL Pinkafeld – IF/IT

1. IF – Simple Branching

1.1 Syntax

IF condition THEN
    -- Statements executed if condition is TRUE
END IF;

1.2 Example

DECLARE
    v_sal emp.sal%TYPE;
BEGIN
    SELECT sal INTO v_sal FROM emp WHERE empno = 7369;

    IF v_sal < 1500 THEN
        DBMS_OUTPUT.PUT_LINE('Salary below 1500 – Level: Entry');
    END IF;
END;
/

1.3 IF–ELSE

DECLARE
    v_grade NUMBER := 3;
BEGIN
    IF v_grade <= 2 THEN
        DBMS_OUTPUT.PUT_LINE('Passed with distinction');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Passed');
    END IF;
END;
/

2. IF–ELSIF–ELSE / END IF – Multiple Branching

2.1 Syntax

IF condition1 THEN
    ...
ELSIF condition2 THEN
    ...
ELSIF condition3 THEN
    ...
ELSE
    ...
END IF;

Attention: Oracle uses ELSIF (not ELSEIF!)

2.2 Example: Grade Assessment

DECLARE
    v_points  NUMBER := 78;
    v_grade   NUMBER;
    v_text    VARCHAR2(20);
BEGIN
    IF    v_points >= 91 THEN v_grade := 1; v_text := 'Excellent';
    ELSIF v_points >= 76 THEN v_grade := 2; v_text := 'Good';
    ELSIF v_points >= 61 THEN v_grade := 3; v_text := 'Satisfactory';
    ELSIF v_points >= 51 THEN v_grade := 4; v_text := 'Sufficient';
    ELSE                       v_grade := 5; v_text := 'Insufficient';
    END IF;

    DBMS_OUTPUT.PUT_LINE(v_points || ' pts → Grade ' || v_grade || ' (' || v_text || ')');
END;
/

2.3 NULL in Conditions

When a variable is NULL, any comparison with it is NULL (neither TRUE nor FALSE):

DECLARE
    v_x NUMBER := NULL;
BEGIN
    IF v_x = 5 THEN         -- NULL = 5 → NULL → branch NOT executed
        DBMS_OUTPUT.PUT_LINE('Equal to 5');
    ELSIF v_x IS NULL THEN   -- Correct way to check for NULL
        DBMS_OUTPUT.PUT_LINE('v_x is NULL');
    END IF;
END;
/

3. CASE – Selection

3.1 Simple CASE

Compares an expression against concrete values:

DECLARE
    v_day  NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'D')); -- 1=Sunday, 2=Monday ...
    v_name VARCHAR2(20);
BEGIN
    CASE v_day
        WHEN 1 THEN v_name := 'Sunday';
        WHEN 2 THEN v_name := 'Monday';
        WHEN 3 THEN v_name := 'Tuesday';
        WHEN 4 THEN v_name := 'Wednesday';
        WHEN 5 THEN v_name := 'Thursday';
        WHEN 6 THEN v_name := 'Friday';
        WHEN 7 THEN v_name := 'Saturday';
        ELSE         v_name := 'Unknown';
    END CASE;

    DBMS_OUTPUT.PUT_LINE('Today is ' || v_name);
END;
/

3.2 Searched CASE

Each WHEN clause contains its own condition:

DECLARE
    v_salary NUMBER := 4500;
    v_class  VARCHAR2(20);
BEGIN
    CASE
        WHEN v_salary < 2000 THEN v_class := 'Low';
        WHEN v_salary < 4000 THEN v_class := 'Medium';
        WHEN v_salary < 7000 THEN v_class := 'High';
        ELSE                       v_class := 'Very high';
    END CASE;

    DBMS_OUTPUT.PUT_LINE('Salary class: ' || v_class);
END;
/

3.3 CASE as Expression (in SQL)

SELECT ename,
       sal,
       CASE
           WHEN sal < 1500 THEN 'Junior'
           WHEN sal < 3000 THEN 'Professional'
           ELSE                  'Senior'
       END AS salary_class
FROM   emp
ORDER  BY sal;

4. Simple LOOP

4.1 Syntax

LOOP
    -- Statements
    EXIT WHEN condition;  -- or: EXIT (unconditional)
END LOOP;

4.2 Example: Multiplication Table

DECLARE
    v_i    NUMBER := 1;
    v_base NUMBER := 7;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(v_base || ' × ' || v_i || ' = ' || (v_base * v_i));
        v_i := v_i + 1;
        EXIT WHEN v_i > 10;
    END LOOP;
END;
/

4.3 Avoid Infinite Loops

Without EXIT WHEN, the loop runs forever! Always define an exit condition:

-- DANGEROUS – runs forever:
LOOP
    NULL;  -- no EXIT!
END LOOP;

-- CORRECT:
LOOP
    v_i := v_i + 1;
    EXIT WHEN v_i > 100;
END LOOP;

5. WHILE Loop

5.1 Syntax

WHILE condition LOOP
    -- Statements (executed as long as condition is TRUE)
END LOOP;

The condition is checked before each iteration. If FALSE on the first check, the block never executes.

5.2 Example: Calculate Factorial

DECLARE
    v_n       NUMBER := 10;
    v_fact    NUMBER := 1;
    v_counter NUMBER := 1;
BEGIN
    WHILE v_counter <= v_n LOOP
        v_fact    := v_fact * v_counter;
        v_counter := v_counter + 1;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(v_n || '! = ' || v_fact);
END;
/

5.3 Comparison: LOOP vs. WHILE

6. Numeric FOR Loop

6.1 Syntax

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

The counter variable is automatically declared and managed. No need to declare it in the DECLARE section!

6.2 Forward and Reverse

BEGIN
    -- Forward: 1, 2, 3, 4, 5
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT(i || ' ');
    END LOOP;
    DBMS_OUTPUT.NEW_LINE;

    -- Reverse: 5, 4, 3, 2, 1
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT(i || ' ');
    END LOOP;
    DBMS_OUTPUT.NEW_LINE;
END;
/

6.3 Example: Compound Interest Table

DECLARE
    v_capital    NUMBER := 10000;
    v_rate       NUMBER := 0.035;  -- 3.5%
BEGIN
    DBMS_OUTPUT.PUT_LINE('Year | Capital');
    DBMS_OUTPUT.PUT_LINE('-----|--------');
    FOR year IN 1..10 LOOP
        v_capital := v_capital * (1 + v_rate);
        DBMS_OUTPUT.PUT_LINE(
            LPAD(year, 4) || ' | ' || TO_CHAR(v_capital, 'FM999,990.00') || ' USD'
        );
    END LOOP;
END;
/

7. Cursor FOR Loop (Preview)

The cursor FOR loop (fully covered in the "Cursors" chapter) automatically iterates over all rows of a query:

BEGIN
    FOR r IN (SELECT ename, sal FROM emp WHERE deptno = 20 ORDER BY sal DESC) LOOP
        DBMS_OUTPUT.PUT_LINE(r.ename || ': ' || r.sal);
    END LOOP;
END;
/

Advantage: No explicit OPEN/FETCH/CLOSE needed – PL/SQL handles this automatically.

8. EXIT and CONTINUE

8.1 EXIT – Leave the Loop Immediately

DECLARE
    v_sum NUMBER := 0;
BEGIN
    FOR i IN 1..1000 LOOP
        v_sum := v_sum + i;
        EXIT WHEN v_sum > 100;  -- Stop as soon as sum > 100
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Sum: ' || v_sum);
END;
/

8.2 CONTINUE – Skip Current Iteration

CONTINUE jumps directly to the next loop iteration (from Oracle 11g):

BEGIN
    FOR i IN 1..10 LOOP
        CONTINUE WHEN MOD(i, 2) = 0;  -- Skip even numbers
        DBMS_OUTPUT.PUT(i || ' ');    -- Only odd: 1 3 5 7 9
    END LOOP;
    DBMS_OUTPUT.NEW_LINE;
END;
/

8.3 Nested Loops with Labels

Labels allow targeted exit from an outer loop:

BEGIN
    <<outer_loop>>
    FOR i IN 1..5 LOOP
        FOR j IN 1..5 LOOP
            EXIT outer_loop WHEN i = 3 AND j = 3;  -- Exit both loops
            DBMS_OUTPUT.PUT('(' || i || ',' || j || ') ');
        END LOOP;
        DBMS_OUTPUT.NEW_LINE;
    END LOOP outer_loop;
    DBMS_OUTPUT.PUT_LINE('Done.');
END;
/

9. GOTO and Labels

9.1 GOTO (Use with Caution)

GOTO jumps to a label in the same block. Can make code hard to read – useful in exceptional cases:

DECLARE
    v_i NUMBER := 1;
BEGIN
    <<loop_start>>
    IF v_i <= 5 THEN
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_i);
        v_i := v_i + 1;
        GOTO loop_start;
    END IF;
END;
/

Recommendation: Avoid GOTO if possible, use structured loops instead.

9.2 NULL Statement as Placeholder

NULL is a legal statement that does nothing. Useful as a placeholder:

BEGIN
    IF 1 = 2 THEN
        NULL;  -- Intentionally empty – syntactically required
    END IF;
END;
/

10. Summary and Outlook

10.1 Overview of Control Structures

10.2 Common Mistakes

10.3 Outlook

The next chapter covers Cursors – the ability to process multiple rows from a SQL query row by row:

• Implicit cursors (automatic for DML)
• Explicit cursors (manual with OPEN / FETCH / CLOSE)
• Cursor attributes: %FOUND, %NOTFOUND, %ROWCOUNT
• Cursor FOR loop