5 Recommendations About Cursor FOR Loops in Oracle PL/SQL

A cursor FOR loop is a PL/SQL loop statement. It is a loop that is associated with a cursor embedded within the loop boundary.

There are two types of cursor FOR loops: SQL Cursor FOR loop and Explicit Cursor FOR Loop.

In SQL Cursor FOR loops, you include the text of a query directly in the FOR loop. For example:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serverout on
SQL> BEGIN
  2    FOR item IN
  3    ( SELECT last_name, job_id
  4       FROM employees
  5       WHERE job_id LIKE '%CLERK%'
  6       AND manager_id > 120 )
  7    LOOP
  8      DBMS_OUTPUT.PUT_LINE
  9        ('Name = ' || item.last_name || ', Job = ' || item.job_id);
 10    END LOOP;
 11  END;
 12  /
Name = OConnell, Job = SH_CLERK
Name = Grant, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...

PL/SQL procedure successfully completed.

In Explicit Cursor FOR Loops, you declare a cursor that specifies a query, and then reference the cursor in the FOR loop. For example:

SQL> DECLARE
  2   CURSOR c1 IS SELECT last_name, job_id FROM employees
  3                  WHERE job_id LIKE '%CLERK%' AND manager_id > 120;
  4  BEGIN
  5    FOR item IN c1
  6    LOOP
  7      DBMS_OUTPUT.PUT_LINE
  8        ('Name = ' || item.last_name || ', Job = ' || item.job_id);
  9    END LOOP;
 10  END;
 11  /
Name = OConnell, Job = SH_CLERK
Name = Grant, Job = SH_CLERK
Name = Bissot, Job = ST_CLERK
...

PL/SQL procedure successfully completed.

In both examples, you do not need to declare the record variable item, PL/SQL implicitly creates it for you with fields corresponding to the columns of the result set.

Steven Feuerstein gives us the following recommendations about cursor FOR loops which he learned from one of his mentors in the PL/SQL world, Bryn Llewellyn, Oracle’s PL/SQL product manager:

1. Never use a cursor FOR loop when you’re writing new code for normal production deployment in a multiuser application.

2. If you expect to retrieve just one row, use an implicit SELECT INTO query.

3. If you expect to retrieve multiple rows of data and you know the upper limit (as in, “I will never get more than 100 rows in this query”), use BULK COLLECT into a collection of type varray whose upper limit matches what you know about the query.

4. If you expect to retrieve multiple rows of data and you do not know the upper limit, use BULK COLLECT with a FETCH statement that relies on a LIMIT clause to ensure that you do not consume too much per-session memory.

5. If your existing code contains a cursor FOR loop, you should perform a cost-benefit analysis on converting that code, based on these recommendations.

Visit this page to read Steven’s full explanation and examples of each of the above recommendations.

In short, stop using cursor FOR loops and start using BULK COLLECT. It’s that simple.