Pl Sql For Loop With Select Query

9 min read

PL/SQL FOR LOOP with SELECT Query

Working with data in Oracle databases often requires iterating through result sets to perform calculations, transformations, or complex business logic. That's why the PL/SQL FOR LOOP combined with a SELECT query stands out as one of the most elegant and efficient constructs for this purpose. Unlike explicit cursors that demand manual opening, fetching, and closing, this implicit cursor syntax handles the entire lifecycle automatically, reducing boilerplate code and minimizing the risk of resource leaks Small thing, real impact..

And yeah — that's actually more nuanced than it sounds.

Understanding the Implicit Cursor FOR LOOP

At its core, the PL/SQL FOR LOOP with a SELECT statement creates an implicit cursor. That's why oracle Database automatically executes the query, opens a cursor, fetches rows one by one into a loop index record variable, and closes the cursor once the last row is processed or an exception occurs. The loop index acts as a record variable whose structure matches the columns returned by the SELECT list.

The basic syntax follows this pattern:

FOR record_variable IN (SELECT column_list FROM table_name WHERE conditions)
LOOP
    -- Statements referencing record_variable.column_name
END LOOP;

Notice that the SELECT statement sits directly inside the parenthesis following the IN keyword. There is no need to declare the record variable beforehand; it is implicitly declared with %ROWTYPE attributes matching the query projection. This feature significantly accelerates development speed while maintaining strong typing safety.

This is where a lot of people lose the thread.

Key Advantages Over Explicit Cursors

Developers transitioning from explicit cursor syntax often appreciate the reduction in lines of code. An explicit cursor requires a declaration section, an OPEN statement, a FETCH loop with an EXIT WHEN condition, and a CLOSE statement. The implicit FOR LOOP condenses all of this into a single block.

Beyond brevity, the implicit cursor offers performance benefits in specific scenarios. Even so, because the PL/SQL engine manages the fetch cycle internally, it can optimize the context switching between the SQL and PL/SQL engines. In modern Oracle versions, the optimizer may even transform the loop into a bulk operation under the hood when appropriate, though explicit BULK COLLECT remains the gold standard for massive data volumes Easy to understand, harder to ignore..

Another subtle advantage involves scope and visibility. And the loop index record exists only within the loop body. Think about it: once the loop terminates, the variable goes out of scope automatically. This prevents accidental reuse of stale data in subsequent logic blocks—a common bug source when explicit cursor record variables are declared in the outer block It's one of those things that adds up. Practical, not theoretical..

Practical Implementation Patterns

Basic Iteration and Column Access

Consider a scenario where you need to update a status flag for all overdue invoices. The loop index allows direct access to columns by name using dot notation Not complicated — just consistent. That alone is useful..

BEGIN
    FOR inv_rec IN (
        SELECT invoice_id, customer_id, due_date
        FROM invoices
        WHERE status = 'PENDING'
        AND due_date < SYSDATE
    )
    LOOP
        -- Process each overdue invoice
        UPDATE invoices
        SET status = 'OVERDUE',
            last_modified = SYSDATE
        WHERE invoice_id = inv_rec.invoice_id;
        
        DBMS_OUTPUT.PUT_LINE('Invoice ' || inv_rec.invoice_id || ' marked overdue for customer ' || inv_rec.customer_id);
    END LOOP;
    
    COMMIT;
END;
/

In this example, inv_rec is the implicit record. Accessing inv_rec.invoice_id is type-safe; if the column name changes in the query, the code fails at compile time rather than runtime Not complicated — just consistent. Nothing fancy..

Using Aliases and Expressions

When the SELECT list contains expressions, aggregate functions, or joined columns with identical names, aliases become mandatory. The loop index uses these aliases as field names.

FOR dept_summary IN (
    SELECT d.department_name,
           COUNT(e.employee_id) AS emp_count,
           AVG(e.salary) AS avg_sal
    FROM departments d
    LEFT JOIN employees e ON d.department_id = e.department_id
    GROUP BY d.department_name
)
LOOP
    DBMS_OUTPUT.PUT_LINE(dept_summary.department_name || ': ' || 
                         dept_summary.emp_count || ' employees, Avg Salary: ' || 
                         ROUND(dept_summary.avg_sal, 2));
END LOOP;

Here, emp_count and avg_sal are derived fields. Without aliases, the PL/SQL compiler would raise an error because the record fields require valid identifiers.

Parameterized Queries for Reusability

While the query is often hardcoded, you can reference PL/SQL variables from the enclosing block within the SELECT statement. This effectively parameterizes the cursor without formal cursor parameters.

DECLARE
    v_min_salary NUMBER := 5000;
    v_dept_id    NUMBER := 10;
BEGIN
    FOR emp_rec IN (
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = v_dept_id
        AND salary > v_min_salary
    )
    LOOP
        -- Logic for high-earning employees in specific department
        NULL;
    END LOOP;
END;
/

The variables v_min_salary and v_dept_id are evaluated once when the loop starts. If they change inside the loop, the query result set remains unaffected because the cursor is already open Which is the point..

Advanced Techniques and Considerations

Read Consistency and the Snapshot

Oracle ensures read consistency for the duration of the cursor execution. Modifications committed by other sessions after the loop begins are invisible to the cursor. The query results reflect the data as it existed at the moment the loop started (the "snapshot SCN"). This behavior is crucial for reporting logic but requires awareness: if the loop runs for a long time, the snapshot might become stale relative to the current database state No workaround needed..

The FOR UPDATE Clause and WHERE CURRENT OF

A powerful variation involves locking rows for update during iteration. Adding FOR UPDATE to the SELECT statement locks each row as it is fetched, preventing other sessions from modifying those rows until the current transaction ends Simple, but easy to overlook..

FOR acc_rec IN (
    SELECT account_id, balance
    FROM accounts
    WHERE status = 'ACTIVE'
    FOR UPDATE OF balance NOWAIT
)
LOOP
    UPDATE accounts
    SET balance = balance * 1.02 -- Apply 2% interest
    WHERE CURRENT OF acc_rec; -- References the current row in the implicit cursor
END LOOP;

The WHERE CURRENT OF clause references the implicit cursor by its loop index name (acc_rec). This avoids re-specifying the primary key in the WHERE clause, reducing parsing overhead and ensuring the update targets exactly the row currently locked.

Caution: Using NOWAIT or WAIT n is highly recommended with FOR UPDATE inside loops to avoid indefinite blocking if another session holds locks on the target rows.

Handling Empty Result Sets

If the SELECT query returns zero rows, the loop body simply never executes. This behavior differs from a SELECT INTO statement. Which means no NO_DATA_FOUND exception is raised. If you need to detect an empty set, you must track iterations manually using a counter or a Boolean flag.

DECLARE
    v_found BOOLEAN := FALSE;
BEGIN
    FOR rec IN (SELECT * FROM config WHERE active = 'Y')
    LOOP
        v_found := TRUE;
        -- Process config
    END LOOP;
    
    IF NOT v_found THEN
        DBMS_OUTPUT.PUT_LINE('No active configuration found.');
    END IF;
END;

Performance: FOR LOOP vs. BULK COLLECT

For small to medium result sets (hundreds or low thousands of rows), the implicit FOR LOOP is perfectly adequate and highly readable. That said, for large data volumes—typically exceeding 10,000 rows—the context switching between the PL/SQL and SQL engines becomes a bottleneck.

In high-throughput scenarios, BULK COLLECT with a `L

LIMIT clause and a collection-based loop becomes the preferred pattern. Instead of fetching one row at a time, BULK COLLECT retrieves rows in batches, and FORALL sends DML statements to the SQL engine in a single call That's the part that actually makes a difference..

DECLARE
    TYPE t_account_tab IS TABLE OF accounts%ROWTYPE;
    l_accounts t_account_tab;
    CURSOR c_accounts IS
        SELECT *
        FROM accounts
        WHERE status = 'ACTIVE';
BEGIN
    OPEN c_accounts;
    LOOP
        FETCH c_accounts BULK COLLECT INTO l_accounts LIMIT 1000;
        EXIT WHEN l_accounts.COUNT = 0;

        FORALL i IN 1 .. l_accounts.On the flip side, cOUNT
            UPDATE accounts
            SET balance = balance * 1. 02
            WHERE account_id = l_accounts(i).

The `LIMIT` value is a tuning knob. In real terms, a batch size that is too small increases round trips between the PL/SQL and SQL engines; one that is too large increases PGA memory consumption. Sizes between 100 and 10,000 are common, but the correct value depends on row width, available memory, and the amount of undo and redo the DML will generate.

This changes depending on context. Keep that in mind.

Bulk processing also changes exception behavior. With `FORALL`, a failure in one row can abort the entire batch unless `SAVE EXCEPTIONS` is specified. In that case, the individual failures are collected in the `SQL%BULK_EXCEPTIONS` array and can be inspected after the statement completes:

```sql
BEGIN
    FORALL i IN 1 .. l_accounts.COUNT SAVE EXCEPTIONS
        UPDATE accounts
        SET balance = balance * 1.02
        WHERE account_id = l_accounts(i).account_id;
EXCEPTION
    WHEN OTHERS THEN
        FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(
                'Error on element ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
                ': ' || SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE)
            );
        END LOOP;
END;

This pattern is especially useful for data migrations, batch recalculation, and ETL-style workloads where the operation is naturally set-based but still requires procedural control over batching Surprisingly effective..

Choosing the Right Iteration Pattern

The implicit cursor FOR loop is not obsolete; it is simply optimized for a different job. Also, it excels when each row requires complex procedural logic, when the result set is modest, or when readability matters more than raw throughput. BULK COLLECT and FORALL are better suited to large volumes and DML-heavy operations, but they introduce additional memory management and exception-handling considerations.

A practical rule of thumb is to begin with a cursor FOR loop, measure performance with realistic data volumes, and refactor to bulk processing only when profiling shows that row-by-row context switching is the bottleneck. Premature bulk collection can make code harder to maintain without delivering meaningful benefit.

Not obvious, but once you see it — you'll see it everywhere.

Final Considerations

Whether iterating row by row or in batches, the implicit cursor remains governed by Oracle’s read-consistency model. Which means FOR UPDATE adds row-level locking and enables WHERE CURRENT OF, but it should be used deliberately, with a NOWAIT or bounded WAIT clause to avoid unnecessary contention. Worth adding: the snapshot taken when the cursor opens determines what the query sees, even if the loop performs updates or runs for an extended period. In practice, empty result sets are silent, so any required “no rows found” behavior must be coded explicitly. And for large workloads, batch fetching with BULK COLLECT and bulk DML with FORALL can reduce context switching dramatically—provided batch sizes and exception handling are chosen with care.

Used together, these features make the PL/SQL cursor loop both a readable control structure and a scalable data-processing tool. The key

is to match the processing style to the problem’s characteristics: favor the implicit cursor FOR loop for clarity and moderate workloads, and adopt BULK COLLECT with FORALL when volume and throughput demand it. Both approaches remain valid, and the best solution often emerges from profiling realistic data rather than theoretical assumptions. By understanding the trade-offs—read-consistency guarantees, memory usage, exception handling, and lock management—developers can write PL/SQL that is both maintainable and performant, turning the cursor loop from a simple iteration construct into a precise instrument for data manipulation.

New This Week

Hot off the Keyboard

These Connect Well

What Others Read After This

Thank you for reading about Pl Sql For Loop With Select Query. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home