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.