Selecting multiple rows using a cursor in embedded SQL applications

To allow an application to retrieve a set of rows, SQL uses a mechanism called a cursor.

About this task

To help understand the concept of a cursor, assume that the database manager builds a result table to hold all the rows retrieved by executing a SELECT statement. A cursor makes rows from the result table available to an application by identifying or pointing to a current row of this table. When a cursor is used, an application can retrieve each row sequentially from the result table until an end of data condition, that is, the NOT FOUND condition, SQLCODE +100 (SQLSTATE 02000) is reached. The set of rows obtained as a result of executing the SELECT statement can consist of zero, one, or more rows, depending on the number of rows that satisfy the search condition.

Procedure

To process a cursor:

  1. Specify the cursor using a DECLARE CURSOR statement.
  2. Perform the query and build the result table using the OPEN statement.
  3. Retrieve rows one at a time using the FETCH statement.
  4. Process rows with the DELETE or UPDATE statements (if required).
  5. Terminate the cursor using the CLOSE statement.

What to do next

An application can use several cursors concurrently. Each cursor requires its own set of DECLARE CURSOR, OPEN, CLOSE, and FETCH statements.