Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 6.5 Opening CursorsChapter 6
Database Interaction and Cursors
Next: 6.7 Column Aliases in Cursors

6.6 Fetching from Cursors

A SELECT statement creates a virtual table in SQL: its return set is a series of rows determined by the WHERE clause (or lack thereof) and with columns determined by the column list of the SELECT. So a cursor represents that virtual table within your PL/SQL program. In almost every situation, the point of declaring and opening a cursor is to return, or fetch, the rows of data from the cursor and then manipulate the information retrieved. PL/SQL provides a FETCH statement for this action.

The general syntax for a FETCH is shown below:

FETCH <cursor_name> INTO <record_or_variable_list>;

where <cursor_name> is the name of the cursor from which the record is fetched and <record_or_variable_list> is the PL/SQL data structure into which the next row of the active set of records is copied. You can fetch into a record structure (declared with the %ROWTYPE attribute or TYPE declaration statement) or you can fetch into a list of one or more variables (PL/SQL variables or application-specific bind variables such as Oracle Forms items).

The following examples illustrate the variety of possible fetches:

6.6.1 Matching Column List with INTO Clause

When you fetch into a list of variables, the number of variables must match the number of expressions in the SELECT list of the cursor. When you fetch into a record, the number of columns in the record must match the number of expressions in the SELECT list of the cursor.

If you do not match up the column list in the cursor's query with the INTO elements in the FETCH, you will receive the following compile error:

PLS-00394: wrong number of values in the INTO list of a FETCH statement

Let's look at variations of FETCH to see what will and will not work. Suppose I have declared the following cursor, records, and variables:

Now that everything is declared, I then OPEN the cursor for the "Sam I Am" character (passed as an argument) as follows:

OPEN green_eggs_cur ('Sam I Am');

All of the following fetches will compile without error because the number and type of items in the INTO clause match those of the cursor:

FETCH green_eggs_cur INTO green_eggs_rec;
FETCH green_eggs_cur INTO ham_amount, refused_count, lousy_excuse;
FETCH green_eggs_cur INTO full_green_eggs_rec;
FETCH dr_seuss_cur INTO dr_seuss_rec;

Notice that you can FETCH a cursor's row into either a table-based or a programmer-defined record. You do not have to worry about record type compatibility in this situation. PL/SQL just needs to be able to match up a cursor column/expression with a variable/field in the INTO clause.

As you can see from the above FETCHes, you are not restricted to using any single record or variable list for a particular FETCH -- even in the same program. You can declare multiple records for the same cursor and use all those different records for different fetches. You can also fetch once INTO a record and then later INTO a variable list, as shown below:

OPEN green_eggs_cur ('Sam I Am');
FETCH green_eggs_cur INTO green_eggs_rec;
FETCH green_eggs_cur INTO amount_of_ham, num_rejections, reason;
CLOSE green_eggs_cur;

PL/SQL is very flexible in how it matches up the cursor columns with the INTO clause.

6.6.2 Fetching Past the Last Row

Once you open a cursor, you can FETCH from it until there are no more records left in the active set. At this point the %NOTFOUND cursor attribute for the cursor is set to TRUE.

Actually, you can even FETCH past the last record! In this case, PL/SQL will not raise any exceptions. It just won't do anything for you. Because there is nothing left to fetch, though, it also will not modify the values in the INTO list of the FETCH. The fetch will not set those values to NULL.

You should, therefore, never test the values of INTO variables to determine if the FETCH against the cursor succeeded. Instead, you should check the value of the %NOTFOUND attribute, as explained in Section 6.9.

Previous: 6.5 Opening CursorsOracle PL/SQL Programming, 2nd EditionNext: 6.7 Column Aliases in Cursors
6.5 Opening CursorsBook Index6.7 Column Aliases in Cursors

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference