|PL/SQL User's Guide and Reference
Release 2 (9.2)
Part Number A96624-01
PL/SQL Language Elements, 21 of 52
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".
This clause 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
This identifies 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.
This identifies an explicit cursor previously declared within the current scope.
This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope.
This identifies 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.
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.
This optional clause, allowed only in bulk (not scalar)
FETCH statements, lets you limit the number of rows fetched from the database.
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.
This identifies a previously declared 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.
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.
FETCH past the last row in the result set, the values of the target fields or variables are indeterminate and the
%NOTFOUND attribute yields
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
OUT mode. However, if the subprogram also opens the cursor variable, you must specify 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
PL/SQL raises the predefined exception
INVALID_CURSOR if you try to fetch from a closed or never-opened cursor or cursor variable.
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;
In the following example, you fetch rows one at a time from the cursor variable
emp_cv into the user-defined record
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;
COLLECT clause lets you bulk-bind entire columns of Oracle data. That way, you can fetch all rows from the result set at once. In the following example, you bulk-fetch 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;
In the example below, you use 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;