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

. <PL/SQL statements> . END 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 in PL/SQL. 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 elegant. 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 (

SELECT employee_id

       ,first_name||’ ‘||last_name employee_name

FROM employees) LOOP

DBMS_OUTPUT.PUT_LINE ( emp.employee_name||‘ is employee #’||emp.employee_id); END LOOP; END; /* of implicit cursor example */ Explicit cursor example:


    CURSOR emps IS

     SELECT employee_id, first_name||’ ‘||last_name employee_name

    FROM employees;


FOR emp IN emps LOOP

Bookmark and Share