FETCH
Go Up to Statement and Function Reference (Language Reference Guide)
Retrieves the next available row from the active set of an opened cursor. Available in gpre and DSQL.
SQL form:
FETCH cursor
[INTO :hostvar [[INDICATOR] :indvar]
[, :hostvar [[INDICATOR] :indvar] …]];
DSQL form:
FETCH cursor {INTO | USING} SQL DESCRIPTOR xsqlda
Blob form: See FETCH (BLOB).
| Argument | Description |
|---|---|
|
<cursor> |
Name of the opened cursor from which to fetch rows. |
|
<hostvar> |
A host-language variable for holding values retrieved with the
|
|
<indvar> |
Indicator variable for reporting that a column contains an unknown or |
|
|
Specifies that values should be returned in the specified |
|
<xsqlda> |
|
Description: FETCH retrieves one row at a time into a program from the active set of a cursor. The first FETCH operates on the first row of the active set. Subsequent FETCH statements advance the cursor sequentially through the active set one row at a time until no more rows are found and SQLCODE is set to 100.
A cursor is a one-way pointer into the ordered set of rows retrieved by the select expression in the DECLARE CURSOR statement. A cursor enables sequential access to retrieved rows. There are four related cursor statements:
| Stage | Statement | Purpose |
|---|---|---|
|
1 |
|
Declare the cursor; the |
|
2 |
|
Retrieve the rows specified for retrieval with |
|
3 |
|
Retrieve the current row from the active set, starting with the first row; subsequent |
|
4 |
|
Close the cursor and release system resources. |
The number, size, data type, and order of columns in a FETCH must be the same as those listed in the query expression of its matching DECLARE CURSOR statement. If they are not, the wrong values can be assigned.
Examples: The following embedded SQL statement fetches a column from the active set of a cursor:
EXEC SQL
FETCH PROJ_CNT INTO :department, :hcnt;