Oracle Consulting and Training
For Better, Faster, Smarter,
Oracle PL/SQLTutorial Part 6 - Cursor For Loops
This is number six in our series of introductory Oracle PL/SQL tutorials designed to show you how to
make effective use of the language to improve your applications’ performance and your own performance
as an Oracle developer.
So far in these tutorials we've looked at data types, language elements, pros and cons of PL/SQL. brief
examples of using it in the real world, and implicit and explicit cursors. (See here for previous articles).
This tutorial looks at the use of CURSOR FOR Loops.
Cursor FOR LOOPs in Oracle PL/SQL
First of all let's look at the structure of a cursor FOR LOOP
FOR <loop index> IN ( <cursor> ) LOOP
As we can see it its actually quite simple in its basic form
and very much like any other FOR loop. We have what you
might call the header which sets up the FOR LOOP and
controls the number of iterations - one for each row
returned by the query statement which is the cursor - the
loop body, and the END LOOP statement which obviously signifies the end of the loop.
Within the body of the loop you can do (almost) anything that you can do anywhere else in Oracle
PL/SQL: call procedures/functions, create anonymous blocks, declare variables. open/close cursors etc.
and of course most importantly you process the results of the cursor. Three things you can’t do are
OPEN/CLOSE/FETCH from the cursor in the loop header - Oracle does all that or you.
Note that the cursor can be an explicit (predefined) cursor or an implicit cursor. If it is an implicit cursor
then the usual rule for an implicit cursor - that it must return one and only one row otherwise an exception
is raised - does not apply in this case. Both an implicit cursor and an explicit cursor used in an Oracle
PL/SQL cursor FOR LOOP can return zero, one or many rows and the processing will be exactly the same
irrespective of the type of cursor.. If no rows are returned from the query then the body of the loop is
simply not executed. If one or more rows is returned, the body of the loop is executed once for each row.
The <loop index> variable is automatically instantiated by Oracle with a type of cursor%ROWTYPE and
can't be explicitly declared - the use of this structure is the only method by which you can access the results
of the query. This means that expressions (e.g.. UPPER(last_name)) must be given a column alias -
which simply means giving it a name - e.g. UPPER(last_name) last_name.
Despite its simplicity and elegance, there are, however a few disadvantages to using a cursor FOR LOOP
First, if the query returns no data, then all that happens is that the body of the loop is not executed. There is
no exception to trap or any warning generated, even though this situation is usually at least something that
you wish to be aware of. You can overcome this by the use of flags (Boolean variables) but it is not quite so
Second, the processing is exactly the same for each row returned from the cursor, which means you can't so
easily initialize variables with the results of the first row. There are many ways around this of course,
including splitting the cursor in to two, using Boolean variables to signal the first time through the loop
etc., but again they are not quite so elegant.
Third, you have to process every row returned by the query - in other words you can't close the cursor
prematurely, in fact there is no way to close the cursor explicitly at all as already mentioned. It is possible
to exit the loop early by use of a GOTO or EXIT statement but this is not good practice. It is usually better
to control processing by use of a filter in the query or an IF statement within the body of the loop.
Last, but not least, if you are storing the returned rows in a PL/SQL collection you may get better results by
using a BULK COLLECT statement, especially for explicit cursors.
Let’s finish this discussion of cursor FOR LOOPs with two simple examples - the first one using an
implicit cursor and the second using an explicit cursor.
BEGIN /* implicit cursor example */
FOR emp IN (
,first_name||’ ‘||last_name employee_name
FROM employees) LOOP
emp.employee_name||‘ is employee #’||emp.employee_id);
END; /* of implicit cursor example */
Explicit cursor example:
CURSOR emps IS
SELECT employee_id, first_name||’ ‘||last_name employee_name
FOR emp IN emps LOOP
emp.employee_name||‘ is employee #’||emp.employee_id);
END; -- of explicit cursor example
The next part of this tutorial looks at the use of PL/SQL stored procedures in Oracle.
PL/SQL tutorial Part 5
PL/SQL tutorial Part 7
Looking to sky-rocket productivity, save time and reduce costs?
Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-
led training in Oracle and related technologies on or off site in cities across the UK as well as self-study
online training. See our scheduled Oracle training courses, or let us know your requirements.
Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information,
take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you
make your Oracle systems faster and more reliable and save you hours searching for information.
Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.
Smartsoft Computing Ltd, Bristol, England
Tel: 0845 003 1320
Need help with Oracle? Contact Us
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry
trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
© Copyright Smartsoft Computing Ltd 2014. All rights reserved.