PL/SQL User's Guide and Reference

FETCH Statement

Description

The FETCH statement retrieves rows of data one at a time from the result set of a multi-row query. The data is stored in variables or fields that correspond to the columns selected by the query. For more information, see "Managing Cursors"

Syntax

fetch_statement ::=

FETCH {  cursor_name 
       | cursor_variable_name
       | :host_cursor_variable_name}
   INTO {variable_name[, variable_name]... | record_name};

Keyword and Parameter Description

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope.

host_cursor_variable_ name

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.

variable_name[, variable_name]...

This identifies a list of previously declared scalar variables into which column values are fetched. For each column value returned by the query associated with the cursor or cursor variable, there must be a corresponding, type-compatible variable in the list.

record_name

This identifies a user-defined or %ROWTYPE record into which rows of values are fetched. For each column value returned by the query associated with the cursor or cursor variable, there must be a corresponding, type-compatible field in the record.

Usage Notes

You must use either a cursor FOR loop or the FETCH statement to process a multi-row query.

Any variables in the WHERE clause of the query are evaluated only when the cursor or cursor variable is opened. To change the result set or the values of variables in the query, you must reopen the cursor or cursor variable with the variables set to their new values.

To reopen a cursor, you must close it first. However, you need not close a cursor variable before reopening it.

You can use different INTO lists on separate fetches with the same cursor or cursor variable. Each fetch retrieves another row and assigns values to the target variables.

If you FETCH past the last row in the result set, the values of the target fields or variables are indeterminate and the %NOTFOUND attribute yields TRUE.

PL/SQL makes sure the return type of a cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO clause. Also, the number of fields or variables must equal the number of column values.

When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN (or IN OUT) mode. However, if the subprogram also opens the cursor variable, you must specify the IN OUT mode.

Eventually, the FETCH statement must fail to return a row; so when that happens, no exception is raised. To detect the failure, you must use the cursor attribute %FOUND or %NOTFOUND. For more information, see "Using Cursor Attributes" .

PL/SQL raises the predefined exception INVALID_CURSOR if you try to fetch from a closed or never-opened cursor or cursor variable.

Examples

The following example shows that any variables in the query associated with a cursor are evaluated only when the cursor is opened:

DECLARE
   my_sal NUMBER(7,2);
   num    INTEGER(2) := 2;
   CURSOR emp_cur IS SELECT num*sal FROM emp;
BEGIN
   OPEN emp_cur;  -- num equals 2 here
   LOOP
      FETCH emp_cur INTO my_sal;
      EXIT WHEN emp_cur%NOTFOUND;
         -- process the data
      num := num + 1;  -- does not affect next FETCH; sal will
                       -- be multiplied by 2
   END LOOP;
   CLOSE emp_cur;
END;

In this example, each retrieved value equals 2 * sal, even though num is incremented after each fetch. To change the result set or the values of variables in the query, you must close and reopen the cursor with the variables set to their new values.

In the following Pro*C example, you fetch rows from a host cursor variable into a host record (struct) named emp_rec:

/* Exit loop when done fetching. */
EXEC SQL WHENEVER NOTFOUND DO break;
for (;;)
{ 
   /* Fetch row into record. */
   EXEC SQL FETCH :emp_cur INTO :emp_rec; 
   /* process the data. */
}

The next example shows that you can use a different INTO clause on separate fetches with the same cursor variable. Each fetch retrieves another row from the same result set.

/* Exit loop when done fetching. */
EXEC SQL WHENEVER NOTFOUND DO break;
for (;;)
{ 
   /* Fetch row from result set. */
   EXEC SQL FETCH :emp_cur INTO :emp_rec1;
   /* Fetch next row from same result set. */
   EXEC SQL FETCH :emp_cur INTO :emp_rec2;
   /* process the data. */
}