Fetching Rows with a Cursor
Go Up to Selecting Multiple Rows
Once a cursor is opened, rows can be retrieved, one at a time, from the results table by using the FETCH
statement. FETCH
:
- Retrieves the next available row from the results table.
- Copies those rows into the host variables specified in the
INTO
clause of theFETCH
statement. - Advances the cursor to the start of the next available row or sets
SQLCODE
to 100, indicating the cursor is at the end of the results table and there are no more rows to retrieve.
The complete syntax of the FETCH
statement in SQL is:
FETCH <cursorname> INTO :variable [[INDICATOR] :variable] [, :variable [[INDICATOR] :variable>] ...];
- Important: In dynamic SQL (DSQL) multi-row select processing, a different
FETCH
syntax is used. For more information about retrieving multiple rows in DSQL, see Fetching Rows with a DSQL Cursor.
For example, the following statement retrieves a row from the results table for the DEPT_EMP
cursor, and copies its column values into the host-language variables, deptname
, lname
, and fname
:
EXEC SQL FETCH DEPT_EMP INTO :deptname, :lname, :fname;
To process each row in a results table in the same manner, enclose the FETCH
statement in a host-language looping construct. For example, the following C code fetches and prints each row defined for the DEPT_EMP
cursor:
. . . EXEC SQL FETCH DEPT_EMP INTO :deptname, :lname, :fname; while (!SQLCODE) { printf("%s %s works in the %s department.\n", fname, lname, deptname); EXEC SQL FETCH DEPT_EMP INTO :deptname, :lname, :fname; } EXEC SQL CLOSE DEPT_EMP; . . .
Every FETCH
statement should be tested to see if the end of the active set is reached. The previous example operates in the context of a while loop that continues processing as long as SQLCODE
is zero. If SQLCODE
is 100, it indicates that there are no more rows to retrieve. If SQLCODE
is less than zero, it indicates that an error occurred.