Retrieving rows by using a scrollable cursor

A scrollable cursor is cursor that can be moved in both a forward and a backward direction. Scrollable cursors can be either row-positioned or rowset-positioned.

Procedure

When you open any cursor, the cursor is positioned before the first row of the result table. You move a scrollable cursor around in the result table by specifying a fetch orientation keyword in a FETCH statement.
A fetch orientation keyword indicates the absolute or relative position of the cursor when the FETCH statement is executed. The following table lists the fetch orientation keywords that you can specify and their meanings. These keywords apply to both row-positioned scrollable cursors and rowset-positioned scrollable cursors.
Table 1. Positions for a scrollable cursor
Keyword in FETCH statement Cursor position when FETCH is executed1.a
BEFORE Before the first row
FIRST or ABSOLUTE +1 On the first row
LAST or ABSOLUTE -1 On the last row
AFTER After the last row
ABSOLUTE1.b On an absolute row number, from before the first row forward or from after the last row backward
RELATIVE1.b On the row that is forward or backward a relative number of rows from the current row
CURRENT On the current row
PRIOR or RELATIVE -1 On the previous row
NEXT On the next row (default)
Table notes
  1. The cursor position applies to both row position and rowset position, for example, before the first row or before the first rowset.
  2. For more information about ABSOLUTE and RELATIVE, see FETCH statement

Example

To use the cursor that is declared in Types of cursors to fetch the fifth row of the result table, use a FETCH statement like this: 

EXEC SQL FETCH ABSOLUTE +5 C1 INTO :HVDEPTNO, :DEPTNAME, :MGRNO;

To fetch the fifth row from the end of the result table, use this FETCH statement: 

EXEC SQL FETCH ABSOLUTE -5 C1 INTO :HVDEPTNO, :DEPTNAME, :MGRNO;