Fetches single or multiple rows from the cursor result set, depending on the ROW_COUNT specification at CURSOR OPEN time.
If a cursor is specified as scrollable, the fetch orientation in the FETCH statement specifies the fetch direction.
If the cursor is not specified as scrollable, FETCH retrieves the next row in the result set.
EXEC SQL FETCH [ <fetch orientation> ]
[ FROM ] <cursor name>
{ [ INTO <fetch target list> ] |
[SQL DESCRIPTOR <>]
<fetch orientation> : : =
| NEXT
| PRIOR
| FIRST
| LAST
| ABSOLUTE <fetch_offset>
| RELATIVE <fetch_offset>
<fetch offset> : : =
<signed_numeric_literal>
<fetch target list> : : =
<target specification>
[ { <comma> <target specification> } ]
END-EXEC
Specified as NEXT, PRIOR, FIRST, LAST, ABSOLUTE, or RELATIVE.
Specified as an exact, signed numeric value with a scale of zero.
To fetch a row when a cursor is declared and open:
EXEC SQL FETCH LAST FROM c1 INTO :title,:roy END-EXEC.
To fetch a previous row:
EXEC SQL FETCH PRIOR FROM c1 INTO :title,:roy END-EXEC.
To fetch row 20:
EXEC SQL FETCH ABSOLUTE 20 FROM c1 INTO :title, :roy
END-EXEC.
If fetch orientation is not specified, NEXT is the default.
If you specify fetch orientation as
any type except NEXT on a non-scrollable cursor, you receive the
following message:
The fetch type can only be used with scrollable cursors.
If fetch orientation positions the cursor beyond the last row or before the first row, sqlca.sqlcode is set to 100, indicating that no rows are found. If an error handler is installed, it may provide additional information.
declare, open
