PL/SQL IF — NULL Comparison Silently Skips 12K Records

Standard SQL is declarative — you describe the result you want and the optimizer figures out how to get there. That model works brilliantly for set-based operations, but it breaks down the moment your logic needs to branch on a runtime value, iterate until a condition is met, or recover from a failure mid-stream and continue processing. That is exactly the gap PL/SQL control structures fill.

In production, the stakes around this are not theoretical. A poorly structured loop processing 500,000 rows one at a time generates context switches between the PL/SQL and SQL engines on every single iteration — each one carrying the overhead of a round trip. The redo log swells. Row-level locks accumulate. PGA memory climbs. A batch job that should complete in under a minute ends up holding locks for thirty. The same operation expressed as a single FORALL or a well-structured UPDATE runs entirely inside the SQL engine with an optimized execution plan and no context-switching penalty.

But set-based SQL has its own limits. When each row's outcome determines how the next row should be processed, when business rules span multiple dependent queries, when you need to log partial failures and continue rather than abort — that is where procedural control structures earn their place. The judgment call between reaching for a loop versus restructuring the problem as a single SQL statement is one of the clearest signals separating a mid-level Oracle developer from a senior one.

This guide covers the mechanics, the production patterns, the failure modes, and the performance trade-offs. By the end you will know not just how each structure works, but when to use it, when to avoid it, and what to check first when something goes wrong at 2 a.m.

Conditional Logic: The IF-THEN-ELSIF Branching Model

SQL's declarative model handles set operations elegantly, but it was never designed for branching on runtime state. The moment you need to execute a completely different code path based on a value fetched from the database — not just select a different column value, but run different procedures, apply different validation rules, or write to different tables — you need procedural conditional logic. IF-THEN-ELSIF is how PL/SQL expresses that.

The evaluation model is straightforward: conditions are assessed top-down and the first branch whose condition evaluates to TRUE executes. Every subsequent branch is skipped entirely. This short-circuit behaviour is not just an optimisation — it is the mechanism. It means condition ordering matters. If you place the most expensive condition first and it is rarely TRUE, you are paying that cost on every evaluation. Put your cheapest, most commonly matched condition at the top.

The ELSE clause is where many production batch jobs have quietly failed. Without it, any input state that matches no ELSIF condition is silently ignored. In an interactive application, a missed branch is a minor UX issue. In an overnight batch job processing 300,000 invoices, it means a subset of records is never touched and nobody finds out until month-end reconciliation. The ELSE branch should always be present in batch processing, and it should always write to an audit table — not just to DBMS_OUTPUT, which is lost the moment the session ends.

Null handling is the sharpest edge in this model. PL/SQL inherits Three-Valued Logic from SQL: TRUE, FALSE, and NULL. Any comparison involving NULL using a standard operator returns NULL, not FALSE. IF v_status = NULL does not evaluate to TRUE when v_status is NULL — it evaluates to NULL, which is not TRUE, so the block never fires. This is not an edge case. It is how the language works, and it catches experienced developers who switch from other languages where null comparisons behave differently. The fix is always IS NULL and IS NOT NULL — no exceptions.

-- io.thecodeforge: Conditional branching with proper NULL handling and audit trail
DECLARE
    v_counter    NUMBER        := 1;
    v_threshold  CONSTANT NUMBER := 5;
    v_status     VARCHAR2(20);
    v_raw_input  VARCHAR2(20)  := NULL;  -- Simulating a NULL-status record
BEGIN
    -- Demonstrate correct NULL handling — IS NULL, never = NULL
    IF v_raw_input IS NULL THEN
        v_status := 'PENDING_REVIEW';
        -- In production: INSERT INTO batch_audit (record_key, reason, logged_at)
        --                VALUES (v_id, 'NULL status on intake', SYSTIMESTAMP);
        DBMS_OUTPUT.PUT_LINE('NULL input detected — flagged for review');
    ELSIF v_raw_input = 'APPROVED' THEN
        v_status := 'PROCESSING';
    ELSIF v_raw_input IN ('REJECTED', 'CANCELLED') THEN
        v_status := 'CLOSED';
    ELSE
        -- Catch-all: unexpected states must never silently pass through
        v_status := 'UNKNOWN';
        DBMS_OUTPUT.PUT_LINE('WARNING: Unexpected status value — ' ||
                             NVL(v_raw_input, 'IS NULL'));
    END IF;

    -- IF-THEN-ELSIF driving a WHILE LOOP — realistic batch pattern
    WHILE v_counter <= v_threshold LOOP
        IF v_counter < 3 THEN
            v_status := 'INITIALIZING';
        ELSIF v_counter BETWEEN 3 AND 4 THEN
            v_status := 'PROCESSING';
        ELSE
            v_status := 'FINALIZING';
        END IF;

        DBMS_OUTPUT.PUT_LINE(
            'Iteration ' || v_counter || ' | Status: ' || v_status
        );
        v_counter := v_counter + 1;
    END LOOP;
END;

Iterative Logic: Master the Three Loop Types

The choice of loop type is a correctness decision before it is a performance decision. Each of the three loop types in PL/SQL encodes a specific contract about when the exit condition is evaluated, who manages the counter, and what happens to open cursors when the loop terminates.

The BASIC LOOP executes its body at least once before evaluating the EXIT WHEN condition. This is the right choice when you need to perform an action — read from a queue, generate a sequence value, attempt a connection — and then decide whether to continue. The risk is the infinite loop: if EXIT WHEN is omitted or the condition can never become TRUE, the loop runs until the session is killed or the server runs out of PGA memory. Always add a safety ceiling alongside the business exit condition.

The WHILE LOOP evaluates its condition before the first iteration. If the condition is false going in, the body never executes. This is the right model when you are processing a queue, a file, or an external resource that might already be empty. The discipline required here is ensuring the condition variable is actually modified inside the loop body — a WHILE that checks a variable nobody updates is an infinite loop waiting to happen.

The FOR LOOP is the safest of the three for the simple reason that you cannot accidentally create an infinite loop with it. The bounds are fixed at entry, the counter is implicitly declared and incremented, and the loop exits cleanly when the upper bound is reached or the cursor is exhausted. The Cursor FOR LOOP extends this safety to resource management: the cursor is opened, each row fetched into the loop record variable, and the cursor closed on exit — even if an unhandled exception fires mid-loop. This is the single most effective way to prevent ORA-01000 in production code.

The performance conversation is separate from the loop type conversation. RBAR — Row-By-Agonizing-Row — is the pattern of performing one DML operation per loop iteration when a single SQL statement or FORALL could handle the entire dataset. The cost is context switching: every transition between the PL/SQL engine and the SQL engine has overhead. For 500,000 rows, that overhead accumulates into minutes. FORALL sends the entire collection of DML operations to the SQL engine in one call. The throughput difference is not a small margin — benchmarks consistently show 50x to 100x improvement for pure DML workloads. If the loop body is a single INSERT, UPDATE, or DELETE, rewrite it as FORALL before it ships.

-- io.thecodeforge: Production loop patterns — safe cursors, bulk DML, and exception resilience
DECLARE
    -- Type declarations for BULK COLLECT and FORALL pattern
    TYPE t_invoice_ids  IS TABLE OF invoices.invoice_id%TYPE INDEX BY PLS_INTEGER;
    TYPE t_amounts      IS TABLE OF invoices.amount%TYPE     INDEX BY PLS_INTEGER;

    v_ids       t_invoice_ids;
    v_amounts   t_amounts;
    v_idx       PLS_INTEGER := 1;
    v_max       CONSTANT PLS_INTEGER := 3;

    -- Simulated error table insert (replace with your audit table)
    PROCEDURE log_error(p_id IN NUMBER, p_msg IN VARCHAR2) IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('ERROR LOG | id=' || p_id || ' | ' || p_msg);
        -- INSERT INTO batch_errors (record_id, error_msg, logged_at)
        -- VALUES (p_id, p_msg, SYSTIMESTAMP);
        COMMIT;
    END;
BEGIN
    -- ---------------------------------------------------------------
    -- Pattern 1: Numeric FOR LOOP — counter implicit, bounds fixed
    -- Use when: known iteration count, no cursor involved
    -- ---------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('--- Numeric FOR LOOP ---');
    FOR i IN 1..v_max LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
    END LOOP;

    -- ---------------------------------------------------------------
    -- Pattern 2: BASIC LOOP with safety ceiling
    -- Use when: must execute at least once before checking exit
    -- ---------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('--- BASIC LOOP with safety ceiling ---');
    LOOP
        DBMS_OUTPUT.PUT_LINE('Basic loop pass: ' || v_idx);
        v_idx := v_idx + 1;
        EXIT WHEN v_idx > v_max;           -- Business exit condition
        -- EXIT WHEN v_idx > 10000;        -- Safety ceiling (always add this)
    END LOOP;

    -- ---------------------------------------------------------------
    -- Pattern 3: Cursor FOR LOOP — open/fetch/close all implicit
    -- Use when: iterating over a query result; safest for cursors
    -- ---------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('--- Cursor FOR LOOP ---');
    FOR rec IN (
        SELECT level AS invoice_id,
               ROUND(DBMS_RANDOM.VALUE(100, 9999), 2) AS amount
        FROM   dual
        CONNECT BY level <= v_max
    ) LOOP
        BEGIN
            IF rec.amount IS NULL THEN
                log_error(rec.invoice_id, 'NULL amount — skipped');
                CONTINUE;   -- Skip this row, process the rest
            END IF;
            DBMS_OUTPUT.PUT_LINE(
                'Invoice ' || rec.invoice_id ||
                ' | Amount: ' || rec.amount
            );
        EXCEPTION
            WHEN OTHERS THEN
                log_error(rec.invoice_id, SQLERRM);
                -- Loop continues to next row automatically
        END;
    END LOOP;

    -- ---------------------------------------------------------------
    -- Pattern 4: FORALL — bulk DML, no per-row context switching
    -- Use when: loop body is a single INSERT/UPDATE/DELETE
    -- ---------------------------------------------------------------
    DBMS_OUTPUT.PUT_LINE('--- FORALL bulk DML pattern ---');
    -- Populate collections (in production: BULK COLLECT INTO from a query)
    v_ids(1) := 1001; v_amounts(1) := 500.00;
    v_ids(2) := 1002; v_amounts(2) := 1250.75;
    v_ids(3) := 1003; v_amounts(3) := 875.50;

    -- Single round trip to SQL engine for all three rows
    -- FORALL i IN 1..v_ids.COUNT
    --     UPDATE invoices
    --     SET    amount = v_amounts(i), updated_at = SYSTIMESTAMP
    --     WHERE  invoice_id = v_ids(i);

    DBMS_OUTPUT.PUT_LINE(
        'FORALL would process ' || v_ids.COUNT || ' rows in one SQL engine call'
    );
END;