FETCH Statement

The FETCH statement retrieves rows of data from the result set of a multi-row query. You can fetch rows one at a time, several at a time, or all at once. The data is stored in variables or fields that correspond to the columns selected by the query. For more information, see "Querying Data with PL/SQL".

Syntax

Description of fetch_statement.gif follows

Description of the illustration fetch_statement.gif

Keyword and Parameter Description

BULK COLLECT

Instructs the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. The SQL engine bulk-binds all collections referenced in the INTO list.

collection_name

A declared collection into which column values are bulk fetched. For each query select_item, there must be a corresponding, type-compatible collection in the list.

cursor_name

An explicit cursor declared within the current scope.

cursor_variable_name

A PL/SQL cursor variable (or parameter) declared within the current scope.

host_array_name

An array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) into which column values are bulk fetched. For each query select_item, there must be a corresponding, type-compatible array in the list. Host arrays must be prefixed with a colon.

host_cursor_variable_name

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.

LIMIT

This optional clause, allowed only in bulk (not scalar) FETCH statements, lets you bulk fetch several rows at a time, rather than the entire result set.

record_name

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.

variable_name

A variable into which a column value is 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.

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 returns 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.

Because a sequence of FETCH statements always runs out of data to retrieve, no exception is raised when a FETCH returns no data. To detect this condition, you must use the cursor attribute %FOUND or %NOTFOUND.

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

Restrictions on BULK COLLECT

[Moved from Collections and Tuning chapters -- might have to move it once more! -- John]

The following restrictions apply to the BULK COLLECT clause:

You cannot bulk collect into an associative array that has a string type for the key.
You can use the BULK COLLECT clause only in server-side programs (not in client-side programs). Otherwise, you get the error this feature is not supported in client-side programs.
All target variables listed in a BULK COLLECT INTO clause must be collections.
Composite targets (such as objects) cannot be used in the RETURNING INTO clause. Otherwise, you get the error unsupported feature with RETURNING clause.
When implicit datatype conversions are needed, multiple composite targets cannot be used in the BULK COLLECT INTO clause.
When an implicit datatype conversion is needed, a collection of a composite target (such as a collection of objects) cannot be used in the BULK COLLECT INTO clause.

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);
   n      INTEGER(2) := 2;
   CURSOR emp_cur IS SELECT  n*sal FROM emp;
BEGIN
   OPEN emp_cur;  --  n equals 2 here
   LOOP
      FETCH emp_cur INTO my_sal;
      EXIT WHEN emp_cur%NOTFOUND;
         -- process the data
       n :=  n + 1;  -- does not affect next FETCH; sal will be multiplied by 2
   END LOOP;

The following example fetches rows one at a time from the cursor variable emp_cv into the user-defined record emp_rec:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   emp_cv  EmpCurTyp;
   emp_rec emp%ROWTYPE;
BEGIN
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      ...
   END LOOP;
END;

The BULK COLLECT clause lets you fetch entire columns from the result set, or the entire result set at once. The following example, retrieves columns from a cursor into a collection:

DECLARE
   TYPE NameList IS TABLE OF emp.ename%TYPE;
   names NameList;
   CURSOR c1 IS SELECT ename FROM emp WHERE job = 'CLERK';
BEGIN
   OPEN c1;
   FETCH c1 BULK COLLECT INTO names;
   ...
   CLOSE c1;
END;

The following example uses the LIMIT clause. With each iteration of the loop, the FETCH statement fetches 100 rows (or less) into index-by table acct_ids. The previous values are overwritten.

DECLARE
   TYPE NumList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   CURSOR c1 IS SELECT acct_id FROM accounts;
   acct_ids NumList;
   rows NATURAL := 100;  -- set limit
BEGIN
   OPEN c1;
   LOOP
      /* The following statement fetches 100 rows (or less). */
      FETCH c1 BULK COLLECT INTO acct_ids LIMIT rows;
      EXIT WHEN c1%NOTFOUND;
      ...
   END LOOP;
   CLOSE c1;
END;