Introduction
The FOR‑i loop is one of the most common constructs in Oracle PL/SQL for iterating over a set of data, whether it’s a numeric range, a collection, or the rows returned by a cursor. In this article we’ll explore the syntax, best practices, and real‑world scenarios that showcase the power of the FOR i IN pattern. Also, understanding how to write an effective FOR i IN … loop is essential for any developer who wants to write clean, efficient, and maintainable code. By the end you’ll be able to confidently use the FOR‑i loop to process data, generate reports, and build strong PL/SQL blocks Surprisingly effective..
Steps to Write a FOR i IN Loop
1. Choose the Right Iterator Source
A FOR i IN loop can iterate over three main sources:
- A numeric range –
FOR i IN 1..10 LOOP - A collection –
FOR i IN 1..my_collection.COUNT LOOP - A cursor –
FOR rec IN (SELECT * FROM employees) LOOP
Each source determines how you reference the index variable i Worth knowing..
2. Declare the Index Variable (Optional)
Although PL/SQL automatically creates the loop variable i, you may want to declare it explicitly for clarity or to reuse it later:
DECLARE
v_counter NUMBER;
BEGIN
FOR v_counter IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Count: ' || v_counter);
END LOOP;
END;
3. Write the Loop Body
Inside the loop you can read from the iterator, perform calculations, and assign values to other variables Worth keeping that in mind..
FOR emp_rec IN (SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 30) LOOP
-- Use the index variable i (or any other variable) here
DBMS_OUTPUT.PUT_LINE('Employee #' || emp_rec.employee_id ||
': ' || emp_rec.first_name || ' ' ||
emp_rec.last_name);
END LOOP;
4. Use the Index Variable for Complex Logic
When you need to track the iteration number, you can use i to build dynamic SQL, generate row numbers, or control flow:
FOR i IN (SELECT department_id FROM departments ORDER BY department_id) LOOP
-- i is the *record* type of the subquery
INSERT INTO dept_summary (dept_id, seq)
VALUES (i.department_id, i.department_id + 100);
END LOOP;
If you need a numeric counter, combine the cursor with ROW_NUMBER() or use a numeric range:
FOR i IN 1..10 LOOP
INSERT INTO temp_numbers (num) VALUES (i * 2);
END LOOP;
5. Handle Exceptions Inside the Loop
Placing EXCEPTION blocks inside a FOR loop allows you to continue processing even if one iteration fails:
FOR rec IN (SELECT * FROM high_value_data) LOOP
BEGIN
PROCESS_ROW(rec);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error processing row #' || rec.id);
-- Log the error but keep looping
END;
END LOOP;
6. Close Resources Properly
When the iterator is a cursor opened with OPEN ... FETCH, you must close it manually. The FOR i IN syntax automatically handles cursor cleanup, but if you open a cursor explicitly you should close it:
CURSOR c IS SELECT * FROM large_table;
FOR rec IN c LOOP
NULL; -- processing
END LOOP;
-- c is automatically closed when the loop ends
Scientific Explanation
How the FOR i IN Loop Works Internally
When PL/SQL encounters a FOR i IN statement, it performs the following steps:
- Initialize the Iterator – If the source is a numeric range, PL/SQL sets
ito the lower bound. If it’s a collection,istarts at1. For a cursor, PL/SQL opens the cursor and fetches the first row, assigning it toi. - Enter the Loop Body – The statements inside the loop are executed with the current value of
i. - Iterate – After each iteration, PL/SQL advances the iterator to the next value (or fetches the next row). This continues until the iterator reaches its upper bound or the cursor returns
NO_DATA_FOUND. - Exit and Clean Up – Once the iterator is exhausted, the loop exits and any resources (like an explicitly opened cursor) are closed automatically.
Because the loop variable is read‑only within the FOR construct, you cannot accidentally modify the iteration source, which reduces the risk of infinite loops.
Types of Loop Variables
- Numeric Loop Variable – When the source is a numeric range,
iis of typeNUMBER. - Collection Index – When iterating over a collection (e.g.,
TABLE OF NUMBER),iis anINTEGERrepresenting the collection’s index. - Record Variable – When the source is a cursor query,
ibecomes a record type that mirrors each column of the SELECT list. You can access fields using dot notation (i.column_name).
Performance Considerations
- Implicit Cursor vs. Explicit Cursor – Using
FOR i IN (SELECT …)creates an implicit cursor, which is convenient but may have slightly higher overhead than an explicit cursor if you need to process rows in a specific order or applyFOR UPDATE. - Bulk Collections – For large result sets, prefer fetching data into a bulk collection (
TABLE OF RECORD) and then using aFOR i IN 1..collection.COUNTloop. This reduces context switches between PL/SQL and SQL. - Index Usage – Ensure the cursor’s SELECT list includes an index on the driving column; otherwise, the optimizer may perform a full table scan, impacting performance.
Frequently Asked Questions (FAQ)
1. Can I use FOR i IN with a reverse range?
Yes. Oracle supports reverse ranges using the DOWN BY keyword (available in PL/SQL 12c and later). Example:
FOR i IN REVERSE 10 DOWNTO 1 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
2. What happens if the cursor returns no rows?
If the cursor is empty, the loop body never executes. Which means no rows are processed, and the loop exits immediately. This behavior is safe for reporting scripts where missing data should simply result in an empty output Which is the point..
3. Is the loop variable i scoped outside the loop?
No. The variable created by the FOR i IN statement is local to the loop and cannot be referenced after the loop ends. If you need the final value, assign it to another variable before the loop finishes:
DECLARE
v_last_id NUMBER;
BEGIN
FOR rec IN (SELECT id FROM my_table) LOOP
v_last_id := rec.id; -- capture each iteration
END LOOP;
DBMS_OUTPUT.PUT_LINE('Last ID: ' || v_last_id);
END;
4. How do I break out of a FOR i IN loop early?
You can use the EXIT WHEN condition to stop iteration before the iterator is exhausted: