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).
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.
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.
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 .
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.