msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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.


Filed in Oracle, Tips on 10 Nov 08 | Tags: , ,


Reader's Comments

  1. |

    Hi Eddie,

    The full set of recommendations from Bryn Llewellyn can be found here:

    http://www.oracle.com/technology/tech/pl_sql/pd

    Best Regards,

  2. |

    It's worth noting that in 10g, if you're doing step 5, Oracle automatically converts it into step 4 for you (with a default limit of 100).

  3. |

    Thanks Mathew. Bryn's white paper is excellent. I linked to it last month from http://awads.net/wp/2008/10/09/4-useful-links-f

  4. |

    Yep, and that's good for existing code. There is still however the overhead of converting it to BULK COLLECT. So, the best way is to directly use BULK COLLECT whenever you can.

  5. |

    [...] 5 Recommendations About Cursor FOR Loops in Oracle PL/SQL < Eddie Awad’s Blog (tags: oracle loop) Posted in del.icio.us | [...]

  6. |

    [...] Awad has 5 Recommendations About Cursor FOR Loops in Oracle PL/SQL, from the keyboard of Steven Feuerstein. “In short, stop using cursor FOR loops and start [...]

  7. |

    I’d like to add a caveat to the main point here: “and use BULK COLLECT only if you have to, otherwise just use SQL statements”.

    For updates, inserts, etc, I’ve seen many cases where developers write unnecessary PL/SQL code which does explicit looping (bulk collected or not) instead of just using the set-based, declarative methods offered by SQL. See this asktom entry where Tom Kyte compares CURSOR FOR LOOPS, BULK COLLECT, and single SQL statements… guess which is the fastest and most simple?

  8. |

    @George I’ve seen these cases too. Basically, SQL should always be the first choice for manipulating data in the database. If you cannot do it in SQL use PL/SQL. The trick is to know/learn how to do it in SQL.

  9. |

    After I submitted my last comment, I read this latest Asktom thread which demonstrates our point.

  10. |

    In most places I have seen cursor loops used they are used to drive insert or update statements. And can be rewritten to use a single insert info select from statement or a update from statement. Sql is a SET based langauge and will perform set opperations quicker than multiple individual actions. So if you are writing a loop in sql stop and see if you can change that into a smaller query.

  11. |

    @Daniel agreed. Also, let’s not forget about the FORALL.

  12. |

    On a side note, i just changed an INSERT into a FOR loop. It was adding rights, and the statements checks if any rights conflict with the ones currently held. When adding more than one right, and those rights conflict, we give whichever right comes first in the passed TABLE. To do this, we used a FOR loop to INSERT each record individually.

    Sometimes you just have to use a loop.

  13. |

    @Brian Yep, sometimes it depends on what you want to do.

  14. |

    I sent this around to team mates and most thought that FOR loops are just fine thank-you-very-much and BULK COLLECTs are way more work.

    Even Feuerstein’s article mentions that the FOR loops are okay for small datasets. I tend to use them in that instance and BULK COLLECT for the larger ones.

    I’d even go so far to say that, in many cases, the time you spend doing a cost-benefit analysis on existing FOR loops vs changing to BULK COLLECTs may actually exceed the amount of time the conversion would save! But obviously there are some that really would benefit from a careful study.

    My point being, hard and fast rules like the 5 above are only useful guidelines and don’t always apply.

  15. |

    @Stew I agree with your point, which is another way of saying “it depends”. However, “way more work” is not a good reason not to use BULK COLLECT.

  16. |

    Eddie,

    I wasn’t the one to make the “way more work” comment, but I don’t disagree with it entirely.

    To your reply ““way more work” is not a good reason not to use BULK COLLECT.”, I’d say “it depends.”

    If the code won’t be used much and/or doesn’t give a significant performance improvement, then coding faster using simpler techniques can be the way to go. And that’s why I have a few code templates for creating various types of BULK COLLECT procedures, so it’s just as quick to create them as write an implicit FOR loop.

    Thanks,

    Stew

  17. |

    >If the code won’t be used much

    Ha! If it isn’t ad hoc (and even then) assume it will be used a lot. The reason we have bad code is specifically because people assume it won’t be used much.

    > and/or doesn’t give a significant performance improvement

    It doesn’t give an improvement when? Today when it works on three records, or in six months when it becomes the central batch processor? Today when the DB load is low so it can spend significantly more resources on the processes or at month’s end when due to reports running it causes all the reports on the system to run slower?

    >coding faster using simpler techniques can be the way to go.

    It’s only faster and simpler because it is familiar. It could just as easily be the opposite way around with more use.

  18. |

    Brian,

    Excellent counterpoint. As I said, this isn’t my logic, but that of others.

    But I disagree with your last point:

    > It’s only faster and simpler because it is familiar. It could just as easily be the opposite way around with more use.

    You can’t tell me there’s less to type in a well-written BULK COLLECT routine than an implicit cursor FOR loop, especially if you’re handling records instead of single columns.

  19. |

    Regarding typing less code, in most well known SQL/PLSQL development IDEs you can define/re-use code snippets, sometimes called templates, to reduce the amount of time spent typing statements such as a BULK COLLECT routines.

  20. |

    Yeah, if it is completely ad-hoc, i sometimes get lazy and use the less typing method. But for real code, i usually snag a snippet.

    At some point, i simply remember the code. And the fact that a few more keystrokes are hit, are hardly recognized overall.