Cursors

When you write code in which the results set returned by a SELECT statement includes more than one row of data, you must declare and use a cursor. A cursor indicates the current position in a results set, in the same way that the cursor on a screen indicates the current position.

A cursor enables you to:

• Fetch rows of data one at a time
• Perform updates and deletions at a specified position within a results set.

The example below demonstrates the following sequence of events:

1. The DECLARE CURSOR statement associates the SELECT statement with the cursor Cursor1.
2. The OPEN statement opens the cursor, thereby executing the SELECT statement.
3. The FETCH statement retrieves the data for the current row from the columns au_fname and au_lname and places the data in the host variables first_name and last_name.
4. The program loops on the FETCH statement until no more data is available.
5. The CLOSE statement closes the cursor.

 EXEC SQL DECLARE Cursor1 CURSOR FOR
     SELECT au_fname, au_lname FROM authors
 END-EXEC
  . . .
     EXEC SQL
         OPEN Cursor1
     END-EXEC
       . . .
     perform until sqlcode not = zero
         EXEC SQL
             FETCH Cursor1 INTO :first_name,:last_name
         END-EXEC
         display first_name, last_name
     end-perform
      . . .
     EXEC SQL
         CLOSE Cursor1
     END-EXEC