Oracle PLSQLTutorial -
An introduction to using PL/SQL with Oracle (Part 6)

This is the sixth of our introductory Oracle PLSQLtutorials designed to show you how to make effective use of PL/SQL to improve not only your application's performance but also your own performance as an Oracle developer or DBA

This article explores the use of cursor FOR LOOPs.

Using cursor FOR Loops in Oracle PL/SQL

So far in these tutorials we've looked at data types, language elements, pros and cons, brief example of using PLSQLin the real world and implicit and explicit cursors. (See here for previous articles).

This tutorial looks at the use of CURSOR FOR Loops in your Oracle PL/SQL programs.

Fist of all let's look at the structure of a cursor FOR LOOP.

The general structure is as follows:

FOR <record> IN <cursor> LOOP
<PL/SQL statements>
END LOOP;

Note that you can do anything inside the body of the loop that you can do anywhere else in Oracle PL/SQL : you can call procedures or functions, create anonymous blocks, declare variables etc. as well as processing the results of the cursor.

Some other important facts to note are :-

the cursor can be an explicit (predefined) cursor or an implicit cursor. If it is an implicit cursor then the usual rules applicable to implicit cursors (that it must return one and only one row otherwise an exception is raised) do not apply in this case. Both an implicit cursor and an explicit used in an Oracle PL/SQL cursor FOR LOOP can return zero, one or many rows and the processing will be exactly the same. If no rows are returned from the query then the body of the loop is simply not executed - but no exception is raised. If one or more rows is returned, the body of the loop is executed once for each row and, again, no exception is raised.
the <record_index> variable is automatically instantiated by Oracle and can't be explicitly declared and the use of this record structure is the only method by which you can access the results of the query in a PL/SQL cursor FOR LOOP, therefore 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.

The beauty of using a PL/SQL cursor FOR LOOP is that Oracle automatically OPENs the cursor, the results are automatically FETCHed and when all rows have been returned, Oracle automatically CLOSEs the cursor for you. What more could you ask for ?

There are, however a few disadvantages to using a cursor FOR LOOP in PL/SQL. First, if the query returns no data, then all that happens is that the body of the loop is not executed. In some (if not most circumstances) this situation is probably an error or 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 elegant.

Another disadvantage of using cursor FOR Loops in Oracle PL/SQL is that the processing is exactly the same for each row returned, 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.

The final disadvantage is that 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.

The next part of this Oracle PL/SQL tutorial looks at the use of PL/SQL stored procedures in Oracle.

Part 5 PL/SQL tutorial Part 7

You might also be interested in our beginner's SQL tutorial or our other Oracle tutorials.

---------------------------------------

Looking for more Oracle tips and tricks ? For clear, concise, technical information, take a short cut now and subscribe to our ezine.

Let us have your questions and we'll answer them in a future newsletter.

Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.

Oracle PLSQLTutorial - An introduction to using PL/SQL with Oracle (Part 6)

Using cursor FOR Loops in Oracle PL/SQL

Oracle PLSQLTutorial -
An introduction to using PL/SQL with Oracle (Part 6)