Oracle Magazine - November/December 2008

d e v e l o p e r PL/SQLPRACTICES

BY STEVEN FEUERSTEIN

On Cursor FOR Loops
It’s important to know when not to use cursor FOR loops.

y mentor told me that when query-
ing data I should always use a
cursor FOR loop, even for a single
row lookup. He says it’s the easiest
way to fetch data, and Oracle Database automat-
ically optimizes it in Oracle Database 10g and
above. Do you recommend this practice?

You may want to find a new mentor. It’s great to learn from others, and it’s especially wonderful when the lesson you learn is simple and easy to remember. It’s not so great, however, when the advice is simplistic and results in sub-optimal code. That’s the case with this cursor FOR loop recommendation.

I have a different set of recommendations about cursor FOR loops, which I learned from one of my mentors in the PL/SQL world, Bryn Llewellyn, Oracle’s PL/SQL product manager. O Never use a cursor FOR loop when you’re writing new code for normal production deployment in a multiuser application. O If you expect to retrieve just one row, use an implicit SELECT INTO query (which I further recommend that you place inside its own retrieval function). O 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. O 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. O If your existing code contains a cursor FOR loop, you should perform a cost-benefit analysis on converting that code, based on these recommendations.

Let’s take a closer look at these five

codeLISTING 1: Encapsulating SELECT INTO in a function

PACKAGE employees_qp

IS FUNCTION last_name (id_in IN employees.employee_id%TYPE)

RETURN employees.last_name%TYPE;

END employees_qp;

PROCEDURE process_employee (id_in IN employees.employee_id%TYPE)

IS l_last_name employees.last_name%TYPE;

BEGIN l_last_name := employees_qp.last_name (id_in);

END process_employee;

cursor FOR loop recommendations.

Never use a cursor FOR loop when writing new code. This advice may come as something of a shock to you. Using the cursor FOR loop construct is a declarative way of asking Oracle Database to fetch each of the rows specified by your cursor and then execute the loop body for that row. You don’t have to explicitly open the cursor, code the fetch, check to see if the cursor is exhausted, or close the cursor. That’s very nice.

The problem with using a cursor FOR loop is that either it isn’t the appropriate construct for querying data (as is the case for a single-row fetch) or it isn’t the most efficient approach (BULK COLLECT offers a faster way of querying data).

Having said that, I can identify two circumstances in which using a cursor FOR loop would do little harm.

First, if you expect to retrieve a relatively small number of rows (in the hundreds at most), a cursor FOR loop’s performance in Oracle Database 10g and above will likely meet your requirements. Oracle Database automatically optimizes cursor FOR loops to execute similarly to BULK COLLECT, so you get most of the advantages of that approach.

Next, if you’re writing a “one-off” script, or a program that is run only occasionally and is not in the critical path of operations, you may want to

choose the simplicity and readability of the cursor FOR loop over the incremental improvement in performance (and additional complexity of code) that BULK COLLECT offers.

Use an implicit SELECT INTO for single-row fetches. Developers often tell me that they write a cursor FOR loop to fetch a single row. Why not? Oracle Database does so much of the work for you, saving several lines of code and several minutes of typing.

But there’s a problem with using a cursor FOR loop for a single-row fetch: the resulting code is very misleading. It looks like you expect to retrieve multiple rows, yet you get just one.

Will this cause a problem? Maybe not. However, from a best practices standpoint, writing code that is transparent in purpose and easy to read and understand is most important. You should not write code that appears to do one thing while it, in fact, does another.

If you need to retrieve a single row and you know that at most one row should be retrieved, you should use a SELECT INTO statement, as in the following: