The FETCH statement positions a cursor on the next row
of its result table and assigns the values of that row to target variables.
Invocation
Although an interactive SQL
facility might provide an interface that gives the appearance of interactive
execution, this statement can only be embedded within an application
program. It is an executable statement that cannot be dynamically
prepared. When invoked using the command line processor,
the syntax following cursor-name is optional
and different from the SQL syntax. For more information, refer to "Using
command line SQL statements and XQuery statements".
Authorization
For each global variable used as a
cursor-variable-name or
in the expression for an
array-index, the
privileges held by the authorization ID of the statement must include
one of the following:
- READ privilege on the global variable that is not defined in a
module
- EXECUTE privilege on the module of the global variable that is
defined in a module
For each global variable used as an
assignment-target,
the privileges held by the authorization ID of the statement must
include one of the following:
- WRITE privilege on the global variable that is not defined in
a module
- EXECUTE privilege on the module of the global variable that is
defined in a module
For the authorization required to use a cursor, see "DECLARE
CURSOR".
Syntax
>>-FETCH--+------+--+-cursor-name----------+-------------------->
'-FROM-' '-cursor-variable-name-'
.-,---------------------.
V |
>--+-INTO----| assignment-target |-+---+-----------------------><
'-USING DESCRIPTOR--descriptor-name-'
assignment-target
>>-+-global-variable-name-------------------+------------------><
+-host-variable-name---------------------+
+-SQL-parameter-name---------------------+
+-SQL-variable-name----------------------+
+-transition-variable-name---------------+
+-array-variable-name--[--array-index--]-+
'-field-reference------------------------'
Description
- cursor-variable-name
- Identifies the cursor to be used in the fetch operation. The cursor-variable-name
must identify a cursor variable that is in scope. When the FETCH statement
is executed, the underlying cursor of the cursor-variable-name must
be in the open state. A FETCH statement using a cursor-variable-name can
only be used within a compound SQL (compiled) statement.
- INTO assignment-target
Identifies one or more targets for the assignment
of output values. The first value in the result row is assigned to
the first target in the list, the second value to the second target,
and so on. Each assignment to an assignment-target is
made in sequence through the list. If an error occurs on any assignment,
the value is not assigned to the target, and no more values are assigned
to targets. Any values that have already been assigned to targets
remain assigned.
When the data type of every assignment-target is
not a row type, then the value 'W' is assigned to the SQLWARN3 field
of the SQLCA if the number of assignment-targets is
less than the number of result column values.
If
the data type of an assignment-target is
a row type, then there must be exactly one assignment-target specified
(SQLSTATE 428HR), the number of columns must match the number of fields
in the row type, and the data types of the columns of the fetched
row must be assignable to the corresponding fields of the row type
(SQLSTATE 42821).
If the data type of an assignment-target is
an array element, then there must be exactly one assignment-target specified.
- global-variable-name
- Identifies the global variable that is the assignment target.
- host-variable-name
- Identifies the host variable that is the assignment target. For LOB output values, the target can be a regular
host variable (if it is large enough), a LOB locator variable, or
a LOB file reference variable.
- SQL-parameter-name
- Identifies the parameter that is the assignment target.
- SQL-variable-name
- Identifies the SQL variable that is the assignment target. SQL
variables must be declared before they are used.
- transition-variable-name
- Identifies the column to be updated in the transition row. A transition-variable-name must
identify a column in the subject table of a trigger, optionally qualified
by a correlation name that identifies the new value.
- array-variable-name
- Identifies an SQL variable, SQL
parameter, or global variable of an array type.
- [array-index]
- An expression that specifies which element in the array will be
the target of the assignment. For an ordinary array, the array-index expression
must be assignable to INTEGER (SQLSTATE 428H1) and cannot be the null
value. Its value must be between 1 and the maximum cardinality defined
for the array (SQLSTATE 2202E). For an associative array, the array-index expression
must be assignable to the index data type of the associative array
(SQLSTATE 428H1) and cannot be the null value.
- field-reference
- Identifies the field within a row type value that is the assignment
target. The field-reference must be specified
as a qualified field-name where the qualifier
identifies the row value in which the field is defined.
- USING DESCRIPTOR descriptor-name
- Identifies an SQLDA that must contain a valid description of zero
or more host variables.
Before the FETCH statement is processed,
the user must set the following fields in the SQLDA:
- SQLN to indicate the number of SQLVAR occurrences provided in
the SQLDA.
- SQLDABC to indicate the number of bytes of storage allocated for
the SQLDA.
- SQLD to indicate the number of variables used in the SQLDA when
processing the statement.
- SQLVAR occurrences to indicate the attributes of the variables.
The SQLDA must have enough storage to contain all SQLVAR
occurrences. Therefore, the value in SQLDABC must be greater than
or equal to 16 + SQLN*(N), where N is the length of an SQLVAR occurrence.
If
LOB or structured type result columns need to be accommodated, there
must be two SQLVAR entries for every select-list item (or column of
the result table).
SQLD must be set to a value greater than
or equal to zero and less than or equal to SQLN.
The nth variable described in the SQLDA
corresponds to the nth column of the result table of the cursor.
The data type of each variable must be compatible with its corresponding
column.
Each assignment to a variable is made according to
specific rules. If the number of variables is less than the number
of values in the row, the SQLWARN3 field of the SQLDA is set to 'W'.
Note that there is no warning if there are more variables than the
number of result columns. If an assignment error occurs, the value
is not assigned to the variable, and no more values are assigned to
variables. Any values that have already been assigned to variables
remain assigned.
Notes
- Cursor position: An open cursor has
three possible positions:
- Before a row
- On a row
- After the last row.
A cursor can only be on a row as a result of a FETCH statement.
If the cursor is currently positioned on or after the last row of
the result table: - SQLCODE is set to +100, and SQLSTATE is set to '02000'.
- The cursor is positioned after the last row.
- Values are not assigned to assignment targets.
If the cursor is currently positioned before a row, it will
be repositioned on that row, and values will be assigned to targets as specified by the INTO or USING clause.
If
the cursor is currently positioned on a row other than the last row,
it will be repositioned on the next row and values of that row will
be assigned to targets as specified by the
INTO or USING clause.
If a cursor is on a row, that row is called
the current row of the cursor. A cursor referenced in an UPDATE or
DELETE statement must be positioned on a row.
It is possible
for an error to occur that makes the state of the cursor unpredictable.
- When retrieving into LOB locators in situations where it is not
necessary to retain the locator across FETCH statements, it is good
practice to issue a FREE LOCATOR statement before issuing the next
FETCH statement, as locator resources are limited.
- It is possible that a warning may not be returned
on a FETCH. It is also possible that the returned warning applies
to a previously fetched row. This occurs as a result of optimizations
such as the use of system temporary tables or pushdown operators.
- Statement caching affects the behavior of an EXECUTE IMMEDIATE
statement.
- DB2® CLI supports additional
fetching capabilities. For instance when a cursor's result table
is read-only, the SQLFetchScroll() function can be used to position
the cursor at any spot within that result table.
- For an updatable cursor, a lock is obtained on a row when it is
fetched.
- If the cursor definition contains an SQL data change statement
or invokes a routine that modifies SQL data, an error during the fetch
operation does not cause the modified rows to be rolled back, even
if the error results in the cursor being closed.
Examples
Example 1: In this C example,
the FETCH statement fetches the results of the SELECT statement into
the program variables
dnum,
dname,
and
mnum. When no more rows remain to be fetched,
the not found condition is returned.
EXEC SQL DECLARE C1 CURSOR FOR
SELECT DEPTNO, DEPTNAME, MGRNO FROM TDEPT
WHERE ADMRDEPT = 'A00';
EXEC SQL OPEN C1;
while (SQLCODE==0) {
EXEC SQL FETCH C1 INTO :dnum, :dname, :mnum;
}
EXEC SQL CLOSE C1;
Example 2:
This FETCH statement uses an SQLDA.
FETCH CURS USING DESCRIPTOR :sqlda3