FETCH
The FETCH statement is used in SQL Script to read one row from an open cursor.
Syntax
FETCH <cursor> INTO <varList>
The variable list can be a list of variables (same number as the number of projections) or a ROW variable with the right schema. For information on ROW, see
DECLARE CURSOR of Type Variable.
Remarks
• The <varList> works like the SELECT INTO clause. (See
SELECT INTO.)
• It is illegal to fetch from a cursor that is not open.
• Fetching past the last row does not cause an error. The variables are not altered and the FOUND attribute is set to FALSE. See
Attributes of Cursors for details.
• You can specify the direction of the fetch to be NEXT or FIRST. These words must be used along with the keyword FROM, as follows:
FETCH NEXT FROM x INTO i;
FETCH FIRST FROM x INTO i;
If no fetch orientation is specified, NEXT is the default.
If the orientation is NEXT, the fetch behaves as it always has: it fetches the current row’s data into the target variables.
If the orientation specified is FIRST, the cursor is repositioned to the first row, and the first row’s data is placed in the target variables.
Errors
The following table describes the errors that can occur while executing a FETCH statement.
Error Message | Cause |
Uninitiallized cursor | The cursor variable is used, but is not initialized at the time it is fetched. |
Cursor is not open | Cursor was closed when the fetch was attempted. |