Fetch Statement--Fetch the Data
The FETCH statement advances the position of the cursor through the result rows returned by the select. Using the FETCH statement, your application can process the rows one at a time.
The syntax of the FETCH statement is:
EXEC SQL FETCH cursor_name
INTO variable {, variable};
The FETCH statement advances the cursor to the first or next row in the result table and loads the values into host language variables.
To illustrate, the example of cursor processing shown previously contains the following DECLARE CURSOR statement:
exec sql declare c1 cursor for
select ename, sal
from employee
for update of sal;
open c1;
Later in the program, the following FETCH statement appears:
exec sql fetch c1 into :name, :salary;
This FETCH statement puts the values from the ename and sal columns of the current row into the host language variables name and salary.
Since the FETCH statement operates on a single row at a time, it is ordinarily placed inside a host language loop.
There are two ways to detect when the last row in the result table has been fetched:
• The sqlcode variable in the SQLCA is set to 100 if an attempt to fetch past the last row of the result table is made. (The SQL Communications Area (SQLCA) is a group of variables used by OpenSQL to provide error and status information to applications. After the last row is retrieved, succeeding fetches do not affect the contents of the host language variables specified in the INTO clause of the FETCH statement.
• The WHENEVER NOT FOUND statement specifies an action to be performed when the cursor moves past the last row. For details about the WHENEVER statement, see Trapping Errors Using the Whenever Statement in the chapter “OpenSQL Features.”
Cursors can only move forward through a set of rows. To fetch a row again, a cursor must be closed and reopened.