[Chapter 6] 6.6 Fetching from 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:

Fetch into a PL/SQL record:
```
FETCH company_cur INTO company_rec;
```

Fetch into a variable:

FETCH new_balance_cur INTO new_balance_dollars;

Fetch into the row of a PL/SQL table row, a variable, and an Oracle Forms bind variable:
```
FETCH emp_name_cur INTO emp_name (1), hiredate, :dept.min_salary;
```

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:

Cursor that selects just three columns from dr_seuss table and a record based on that cursor:

CURSOR green_eggs_cur (character_in IN VARCHAR2)
IS
   SELECT ham_quantity, times_refused, excuse
     FROM dr_seuss
   WHERE book_title = 'GREEN EGGS AND HAM'
      AND character_name = character_in;
green_eggs_rec green_eggs_cur%ROWTYPE;

Cursor for all columns in dr_seuss table and a record based on the dr_seuss table:

CURSOR dr_seuss_cur
IS
   SELECT * FROM dr_seuss;
dr_seuss_rec dr_seuss_cur%ROWTYPE;

Programmer-defined record type which contains all three of the green_eggs_cur columns, followed by declaration of record:

TYPE green_eggs_rectype IS RECORD
   (ham# dr_seuss.ham_quantity%TYPE;
    saidno# dr_seuss.times_refused%TYPE,
    reason dr_seuss.excuse%TYPE);
full_green_eggs_rec green_eggs_rectype;

Programmer-defined record type which contains only two of the three cursor columns, following by declaration of record:

TYPE partial_green_eggs_rectype IS RECORD
   (ham# dr_seuss.ham_quantity%TYPE;
    saidno# dr_seuss.times_refused%TYPE);
partial_rec partial_green_eggs_rectype;

A set of local PL/SQL variables:

ham_amount NUMBER;
refused_count INTEGER;
lousy_excuse VARCHAR2(60);

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 .