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.

How PL/SQL Control Structures Decide Your Data Fate

PL/SQL control structures are the procedural logic that governs statement execution order — IF-THEN-ELSE, CASE, loops, and GOTO. Unlike SQL's set-based operations, these structures evaluate conditions row-by-row or block-by-block, making them essential for business rules that can't be expressed in a single query. The core mechanic: each condition is a Boolean expression that returns TRUE, FALSE, or NULL — and NULL is not FALSE. This three-valued logic is the root of most silent failures.

In practice, an IF statement checks a condition and executes the THEN branch only if it evaluates to TRUE. If the condition is FALSE or NULL, control falls to ELSIF or ELSE. The critical nuance: comparing a variable to NULL with '=' or '<>' always yields NULL, never TRUE. So 'IF salary <> 5000' will skip rows where salary IS NULL, even though you intended to include them. This is not a bug — it's the SQL standard — but it catches teams constantly.

Use PL/SQL control structures when you need conditional branching, looping, or exception handling that pure SQL cannot provide — typically in stored procedures, triggers, or batch validation. They matter because they enforce data integrity at the database level, but misuse (especially NULL comparisons) silently corrupts results. In production, a single IF statement with an unhandled NULL can exclude 12K records from a payroll run, and no error is raised.

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;

PL/SQL Architecture: Where Your Code Actually Runs

Every IF, LOOP, and EXCEPTION you write gets handed off between two distinct engines. Understanding this handshake is what separates devs who debug in minutes from those who stare at logs for hours.

The PL/SQL engine handles your procedural logic—conditions, loops, variable assignments. The SQL engine takes your DML and passes it down to the database server. They're not the same process. They don't share memory. They communicate across a boundary that can kill performance if you're careless.

When you write a FOR loop with a SQL statement inside, the PL/SQL engine doesn't execute that query. It ships each iteration's SQL to the SQL engine separately. That's why a single cursor-FOR loop that fetches 10,000 rows will make 10,000 round trips unless you use bulk collect. Know your architecture before you cry about slow stored procedures.

// io.thecodeforge — database tutorial

DECLARE
   CURSOR c_orders IS
      SELECT order_id, total_amount
        FROM orders
       WHERE status = 'PENDING';
   
   v_count NUMBER := 0;
BEGIN
   -- Each iteration ships a FETCH to SQL engine
   FOR rec IN c_orders LOOP
      v_count := v_count + 1;
      -- PL/SQL engine does the counting
      -- SQL engine does the FETCH
   END LOOP;
   
   DBMS_OUTPUT.PUT_LINE('Processed: ' || v_count);
END;
/

Execution Flow: The Five-Step Handshake Nobody Documents

Here's what actually happens when you hit F5 on that block. Step one: your PL/SQL block gets parsed and sent to the server. The PL/SQL engine takes control first—it evaluates your procedural statements, resolves variable references, and builds a statement queue.

Step two: when it hits a SQL statement, the PL/SQL engine passes that chunk to the SQL engine. Not your whole block. Just the SQL. The SQL engine parses, optimizes, and executes the query against the database server.

Step three: the database server does the actual table scan, join, or index lookup. It returns rows to the SQL engine. Step four: the SQL engine passes results back to the PL/SQL engine. Step five: your procedural code continues with those results in memory.

Five handoffs. Every single SQL statement. That's why context is king—you want to minimize how many times you cross that boundary.

// io.thecodeforge — database tutorial

DECLARE
   v_emp_name employees.emp_name%TYPE;
   v_salary   employees.salary%TYPE;
BEGIN
   -- Step 1: PL/SQL engine receives block
   -- Step 2: SQL engine receives SELECT
   SELECT emp_name, salary
     INTO v_emp_name, v_salary
     FROM employees
    WHERE emp_id = 1001;
   
   -- Step 3: DB server returns row
   -- Step 4: SQL engine passes to PL/SQL
   -- Step 5: PL/SQL continues
   
   IF v_salary < 50000 THEN
      DBMS_OUTPUT.PUT_LINE('Bonus eligible: ' || v_emp_name);
   END IF;
END;
/