FETCH
NAME
FETCH: move a cursor and read its row (in a program)
SYNOPSIS
<fetch statement> ::=
FETCH [ [ <fetch orientation> ] FROM ] <cursor name>
INTO <target specification> [ { , <target specification> }... ]
<fetch orientation> ::=
NEXT | PRIOR | FIRST | LAST
| { ABSOLUTE | RELATIVE } <simple value specification>
<target specification> ::= <parameter name> [ [ INDICATOR ] <parameter name> ]
<dynamic fetch statement> ::=
FETCH [ [ <fetch orientation> ] FROM ] <dynamic cursor name>
{ INTO <target specification> [ { , <target specification> }... ]
| INTO SQL DESCRIPTOR <descriptor name> }
The <dynamic cursor name> is OPEN's, the <descriptor name> ALLOCATE DESCRIPTOR's. In a polymorphic
table function's fulfill component, PTF and a name are of its cursors
and its areas (CREATE FUNCTION).
DESCRIPTION
FETCH moves an open cursor to a row and assigns the row's values to the targets, in order: NEXT, the default, to the row after; PRIOR, the row before; FIRST and LAST; ABSOLUTE n, the nth row, counting from the last if n is negative; RELATIVE n, n rows on, or back if negative. Past the last row, or before the first, the cursor is on no row and FETCH completes with no data, SQLSTATE 02000, the targets unchanged.
A cursor a compound statement declares (BEGIN) is fetched by this statement within it, in a routine's body, a trigger's action or a statement run directly. Its name is the compound statement's own, in scope there alone, and the cursor is destroyed as the compound statement ends. Its targets are SQL variables and SQL parameters, and a NOT FOUND handler takes its no data.
A fulfill component fetches, NEXT alone, from the cursor of each table argument it is passed the name of, FETCH FROM PTF and that name. Its rows are the partition's, in the argument's ORDER BY, each the columns the describe component requested, then of PASS THROUGH a BIGINT that PIPE ROW gives back to name the row.
A null value sets the target's indicator to -1. A character string longer than its target is cut to fit, and the indicator holds its length in characters; FETCH then completes with a warning, SQLSTATE 01004, SQLCODE 0. A binary string is cut so, its length in octets in the indicator; one shorter than a BINARY target is padded with X'00' octets, with no warning. Any other value is assigned to a target as to a column, by its type.
INTO SQL DESCRIPTOR puts the row into the items of a descriptor area,
as EXECUTE puts a single SELECT's: each value cast to its
item's type, INDICATOR 0 and DATA the value, or INDICATOR -1 of the null
value. A cursor of an extended name is fetched from as one of a cursor
name. The diagnostics area names a dynamic FETCH DYNAMIC FETCH.
FETCH runs only in a program that embeds turborel: see DECLARE CURSOR.
RULES
- The cursor is open.
- An orientation other than NEXT is of a SCROLL cursor.
- The targets are as many as the cursor's columns, each a parameter of a type its column's value may be assigned to; a null value has a target with an indicator.
- A descriptor area's COUNT is the number of columns, each item of a type its column's values are cast to; the clause is of INTO, not USING.
PRIVILEGES
None.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 2A000 | -103 | A rule above is broken |
| 24000 | -501 | The cursor is not open |
| 34000 | -921 | No cursor of the extended name is allocated |
| 07002, 07006, 07008 | -921 | The descriptor area does not match the columns, as of EXECUTE (rule 4) |
| 22002 | -301 | A null value, and its target has no indicator |
| 02000 | 100 | Not an error: no data, the cursor past its last row or before its first |
| 01004 | 0 | Not an error: a warning, a string cut to fit its target |
EXAMPLES
Of the table STAFF of DECLARE CURSOR, its third
row's NAME null, C a cursor of SELECT ID, NAME FROM STAFF ORDER BY ID
and S a SCROLL cursor of the same, each open; the targets MY_ID and
MY_NAME, NAME_IND MY_NAME's indicator:
FETCH C INTO MY_ID, MY_NAME INDICATOR NAME_IND 1, Ada
FETCH C INTO MY_ID, MY_NAME INDICATOR NAME_IND 2, Grace
FETCH C INTO MY_ID, MY_NAME INDICATOR NAME_IND 3, NAME_IND = -1
FETCH C INTO MY_ID, MY_NAME INDICATOR NAME_IND no data (02000)
FETCH PRIOR FROM C INTO MY_ID, MY_NAME INDICATOR NAME_IND -103 (2A000): the cursor is not SCROLL: it fetches NEXT alone [ISO/IEC 9075:1992 §13.3 SR3]
FETCH LAST FROM S INTO MY_ID, MY_NAME INDICATOR NAME_IND 3, NAME_IND = -1
FETCH ABSOLUTE 1 FROM S INTO MY_ID, MY_NAME INDICATOR NAME_IND 1, Ada
FETCH RELATIVE 1 FROM S INTO MY_ID, MY_NAME INDICATOR NAME_IND 2, Grace
FETCH PRIOR FROM S INTO MY_ID, MY_NAME INDICATOR NAME_IND 1, Ada
Of a cursor not open:
FETCH C INTO MY_ID, MY_NAME INDICATOR NAME_IND -501 (24000): cursor C is not open [X3.135-1986 §8.6 GR1]
Of S, of a target SHORT, CHAR(2), and one with no indicator:
FETCH ABSOLUTE -1 FROM S INTO MY_ID, MY_NAME INDICATOR NAME_IND 3, NAME_IND = -1
FETCH ABSOLUTE 1 FROM S INTO MY_ID, SHORT INDICATOR NAME_IND 1, SHORT = 'Ad', NAME_IND = 3; warning 01004
FETCH LAST FROM S INTO MY_ID, MY_NAME -301 (22002): the value is null, and target MY_NAME has no indicator parameter [X3.135-1986 §8.6 GR8]
Of the dynamic cursor D of DECLARE CURSOR, open:
FETCH D USING MY_ID, MY_NAME -103 (2A000): the <using clause> of a FETCH is of INTO [ISO/IEC 9075:1992 §17.15 SR2]
STANDARD
ISO/IEC 9075:1992 §13.3, and of a dynamic cursor §17.15; X3.135-1986 §8.6, whose FETCH moves to the next row alone. A local cursor's is ISO/IEC 9075-4:2006 draft §12.3, Feature P002.