You use FETCH to position a cursor. The new cursor position is either on a row, before the first row or after the last row of the cursor table. If the new cursor position is on a row in the cursor table, this row is the current row and the column values of this row can be read. If no row is read for FETCH because the specified position does not exist, an appropriate SQLSTATE is set, which can be handled with WHENEVER NOT FOUND. If you declare a cursor with SCROLL, the cursor can be positioned with FETCH on any row in the cursor table and in any order. A cursor defined with NO SCROLL can only be positioned on the next row (FETCH NEXT...).
You can transfer the values of the current row to host variables, procedure parameters of the type INOUT or OUT, local variables or an SQL descriptor area.
The cursor declaration with DECLARE CURSOR must be located in the same compilation unit and must physically precede the FETCH statement in the program text.
There must be no backup status of the cursor created with a STORE statement when the FETCH statement is executed. The cursor must be open.
If the cursor is declared with WITH HOLD, the isolation level or consistency level of the transaction must be the same as when the cursor was opened.
If block mode is activated for the cursor (see section "PREFETCH pragma") and if a FETCH NEXT... statement has already been executed for the cursor, only this FETCH NEXT statement is permitted subsequently for this cursor, i.e. the same statement in a loop or subroutine.
FETCH { [NEXT] | PRIOR | FIRST | LAST | RELATIVE n | ABSOLUTE n }
[FROM] cursor
{ INTO variable ,... | SQL DESCRIPTOR GLOBAL descriptor }
n ::= { integer | : host_variable | routine_parameter | local_variable }
variable ::=
{
: host_variable [[INDICATOR] : indicator_variable ] |
routine_parameter |
local_variable
}
NEXT
Positions the cursor on the next row in the cursor table. If you declared the cursor without SCROLL, you can only use the NEXT clause.
If the cursor is located on the last row of the cursor table, the cursor is positioned after the last row. If it is already positioned after the last row, its position remains unchanged.
PRIOR
Positions the cursor on the preceding row of the cursor table.
If the cursor is positioned on the first row of the cursor table, it is positioned before the first row. If it is already positioned in front of the first row, its position remains unchanged.
You can only specify PRIOR if you declared the cursor with SCROLL.
FIRST
Positions the cursor on the first row of the cursor table or before the first row if the cursor table is empty.
You can only specify FIRST if you declared the cursor with SCROLL.
LAST
Positions the cursor on the last row of the cursor table or after the last row if the cursor table is empty.
You can only specify LAST if you declared the cursor with SCROLL.
ABSOLUTE n
Specify the position of the cursor.
You can only specify ABSOLUTE if you declared the cursor with SCROLL.
You can specify the following for n:
An integer
A host variable (if the statement is not part of a procedure) of the SQL data type INT or SMALLINT
A routine parameter or a local variable (if the statement is part of a routine) of the SQL data type INT or SMALLINT
The cursor position is determined by the value of n as follows:
> 0 | The cursor is positioned on the nth row of the cursor table or after the last row |
0 <0 | The cursor is positioned before the first row of the cursor table. The cursor is positioned on the (N+1-|n|)th row of the cursor table, where N is Example
|
RELATIVE n
Position of the cursor relative to its current position. You can only specify RELATIVE if you declared the cursor with SCROLL.
You can specify the following for n:
An integer literal
A host variable (if the statement is not part of a procedure) of the SQL data type INT or SMALLINT
A routine parameter or a local variable (if the statement is part of a routine) of the SQL data type INT or SMALLINT
The cursor position is determined by the value of n as follows:
> 0 | The cursor is positioned on the row that is n rows after its current position. If the |
0 <0 | The cursor position remains unchanged. The cursor is positioned on the row that is n rows in front of its actual position. |
FROM cursor
Name of the cursor.
INTO clause
Indicates where the values read are to be stored.
:host_variable, routine_parameter, local_variable
Name of a host variable (if the statement is not part of a procedure) or name of a procedure parameter of the type INOUT or OUT or of a local variable (if the statement is part of a routine). The column value of the derived row is assigned to the specified output destination.
The data type must be compatible with the data type of the relevant output value (see section "Reading values into host variables or a descriptor area"). If an output value is an aggregate with several elements (only in the case of host variables), the corresponding host variable must be a vector with the same number of elements.
The number of specified elements must match the number of columns in the SELECT list of the cursor description. The value of the nth column in the SELECT list is assigned to the nth output destination in the INTO clause.
indicator_variable
Name of the indicator variable for the preceding host variable. If the host variable is a vector, the indicator variable must also be a vector with the same number of elements.
The indicator value indicates whether the NULL value was transferred or whether data was lost:
0 -1 > 0 | The host variable contains the value read. The assignment was error free. The value to be assigned is the NULL value. For alphanumeric and national values: |
descriptor
For a dynamic cursor.
Name of an SQL descriptor area containing the data type description of the output values and into which the output values read with the FETCH statement are written.
The SQL descriptor area must be created beforehand and supplied with appropriate values:
The value of the COUNT field must be the same as the number of output values, which is calculated as follows: Number of atomic derived columns plus number of column elements of each multiple derived column. The following also applies:
0 <= COUNT <= defined maximum number of item descriptors
The output values are assigned to the DATA fields of the item descriptors in the order of the items in the descriptor area. The data type description for an item must be compatible with the data type of the corresponding output value (see section "Reading values into host variables or a descriptor area").
If the value to be transferred is the NULL value, the appropriate INDICATOR field is set to the value -1. If a string to be assigned is truncated, the
corresponding INDICATOR field indicates the original length.
If block mode is activated for the open cursor cursor, and if a FETCH NEXT... statement has been executed whose INTO clause contains the name of another SQL descriptor area, you receive an error message.
Behavior of SESAM/SQL in the event of an error
If an error occurs when a value is read (e.g. value is the NULL value, but the indicator variable is not specified; numeric value is too big for the target data type), the cursor is moved to its new position but the assigned values are undefined.
In the event of other errors (e.g. incompatible data types), the position of the cursor remains unchanged and no values are read.
Examples
Position the cursor CUR_ORDER on a row in the cursor table and read the column values of the current row in the host variables ORDER_NUM, CUST_NUM, CONTACT_NUM, ORDER_TEXT, TARGET and ORDER_STAT.
Using the indicator variables IND_CONTACT_NUM, IND_ORDER_TEXT and IND_ACTUAL, check whether information has been lost in the transfer of the alphanumeric values and whether any of the columns contain the NULL value.
FETCH cur_order
INTO :ORDER_NUM,
:CUST_NUM,
:CONTACT_NUM INDICATOR :IND_CONTACT_NUM,
:ORDER_TEXT INDICATOR :IND_ORDER_TEXT,
:ACTUAL INDICATOR :IND_ACTUAL,
:ORDER_STAT
Position the cursor CUR_RESULT on a row in the cursor table and read the column values in the descriptor area DESCR_AREA.
FETCH cur_result INTO SQL DESCRIPTOR GLOBAL 'DESCR_AREA'
See also
CLOSE, DECLARE CURSOR, DELETE, OPEN, STORE, UPDATE