Filter
Top Products
The cursor declaration specifying the cursor name must be coded before the FETCH statement in the coding
sequence for the application program. The cursor specified by the FETCH statement must be open. If a cursor that is
not open is specified, an error occurs. If the FETCH statement is executed when the cursor is positioned in the last
row, the result is "No data". Likewise, if the FETCH statement is executed when the cursor is positioned immediately
after the last row, the result is still "No data".
Host variable in which fetched data is stored
The host variables in which values are stored for the row in which the cursor is positioned are specified in the fetch
target list. The number of host variables specified must be the same as the number of columns in the cursor table.
Data from the fetched columns is stored in the host variables in the same sequence from the left.
Fetching data containing null values
To fetch data containing null values, specify an indicator variable to be paired with the host variable in which fetched
data is stored. The following is an example of specifying a FETCH statement to fetch data containing null values:
Example 2:
In this example, indicator variables are specified in the FETCH statement used in Example 1.
FETCH STOCK_CSR INTO :ITMNO INDICATOR :ITMNO_INDICATOR
:STOCKQTY INDICATOR :STOCKQTY_INDICATOR
In example 2, "ITMNO_INDICATOR" and "STOCKQTY_INDICATOR" are indicator variables. Refer to Table: Column
values and values stored in indicator variables for details of the values stored in indicator variables by the FETCH
statement.
If indicator variables are not specified and fetched data contains a null value, the FETCH statement results in an error.
Therefore, specify indicator variables if you do not know whether any of the values for columns are null values.
Moving the cursor in different directions
The cursor can be moved in different directions by specifying a fetch direction in the FETCH statement. When
specifying a fetch direction, specify SCROLL in the cursor declaration. When a fetch direction is omitted, NEXT is
assumed to be specified.
The following are examples of specifying a fetch direction. The same cursor declaration applies to examples a) to f).
a) This example fetches the value of the row following the current row (NEXT specified). The cursor is positioned to [5]
in the following Figure: Example of positioning a cursor in different directions.
b) This example fetches the value of the row preceding the current row (PRIOR specified). The cursor is positioned to
[4] in the following Figure: Example of positioning a cursor in different directions.
Example: FETCH PRIOR FROM CSR1 INTO :H1, :H2
c) This example fetches the value of the first row (FIRST specified) regardless of the current row. The cursor is
positioned to [1] in the following Figure: Example of positioning a cursor in different directions.
Example: FETCH FIRST FROM CSR1 INTO :H1, :H2
52