The cursor is a data structure that represents a specific location within the active
set of rows that the SELECT statement retrieved.
- Sequential cursor - reads through the active set only once each time it is opened, by moving the cursor forward one row each time a row is requested.
- Scroll cursor - fetches the rows of the active set in any sequence. To implement a scroll cursor, the database server creates a temporary table to hold the active set.
The scope of a cursor is the module in which it is declared. Cursor names must be unique within a
module.
The general sequence of program statements when using a SELECT cursor for
Query-by-Example is:
- DECLARE - the program declares a cursor for the STRING that
contains the SQL SELECT statement. This allocates storage to hold
the cursor. The string does not have to be prepared using the PREPARE
statement.
- OPEN - the program opens the cursor. The active set associated with the cursor
is identified, and the cursor is positioned before the first row of the set.
- FETCH - the program fetches a row of data into host variables and processes it.
The syntax FETCH NEXT <cursor-identifier>
INTO <variable-names> can be used with
a SCROLL CURSOR to fetch the next row relative to the current
position of the cursor in the SQL result set. Using FETCH PREVIOUS
... moves the cursor back one row in the SQL result set.
- CLOSE - the program closes the cursor after the last row desired is fetched.
This releases the active result set associated with the cursor. The cursor can be
reopened.
- FREE - when the cursor is no longer needed, the program frees the cursor to
release the storage area holding the cursor. Once a cursor has been freed, it must
be declared again before it can be reopened.
The cursor program statements must appear physically within the module in the order listed.