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
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
FUNCTION last_name (id_in IN employees.employee_id%TYPE)
PROCEDURE process_employee (id_in IN employees.employee_id%TYPE)
l_last_name := employees_qp.last_name (id_in);
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:
PROCEDURE process_employee (
id_in IN employees.employee_id%TYPE)