Using Cursors

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.