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:

DECLARE

    CURSOR emps IS

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

    FROM employees;

BEGIN

FOR emp IN emps LOOP

DBMS_OUTPUT.PUT_LINE ( emp.employee_name||‘ is employee #’||emp.employee_id); END LOOP; 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 View our privacy policy 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 linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd 2014. All rights reserved.