Step 4: Retrieving a row using a cursor

 

To move the contents of a selected row into the host variables of your program, use the FETCH statement.

The SELECT statement within the DECLARE CURSOR statement identifies rows that contain the column values your program wants. However, SQL does not retrieve any data for your application program until the FETCH statement is issued.

When your program issues the FETCH statement, SQL uses the current cursor position as a starting point to locate the requested row in the result table. This changes that row to the current row. If an INTO clause was specified, SQL moves the current row's contents into your program's host variables. This sequence is repeated each time the FETCH statement is issued.

SQL maintains the position of the current row (that is, the cursor points to the current row) until the next FETCH statement for the cursor is issued. The UPDATE statement does not change the position of the current row within the result table, although the DELETE statement does.

The serial cursor FETCH statement looks like this:

EXEC SQL  FETCH cursor-name    INTO :host variable-1[, :host variable-2] …
END-EXEC.

The scrollable cursor FETCH statement looks like this:

EXEC SQL  FETCH RELATIVE integer    FROM cursor-name    INTO :host variable-1[, :host variable-2] …
END-EXEC.

 

Parent topic:

Examples: Using a cursor