For I In Oracle Pl Sql

5 min read

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:

  1. Initialize the Iterator – If the source is a numeric range, PL/SQL sets i to the lower bound. If it’s a collection, i starts at 1. For a cursor, PL/SQL opens the cursor and fetches the first row, assigning it to i.
  2. Enter the Loop Body – The statements inside the loop are executed with the current value of i.
  3. 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.
  4. 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, i is of type NUMBER.
  • Collection Index – When iterating over a collection (e.g., TABLE OF NUMBER), i is an INTEGER representing the collection’s index.
  • Record Variable – When the source is a cursor query, i becomes 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 apply FOR UPDATE.
  • Bulk Collections – For large result sets, prefer fetching data into a bulk collection (TABLE OF RECORD) and then using a FOR i IN 1..collection.COUNT loop. 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:

More to Read

Current Reads

Others Explored

Explore the Neighborhood

Thank you for reading about For I In Oracle Pl Sql. 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