DB2 UDB for iSeries: Types of cursors

Types of cursors

SQL supports serial and scrollable cursors. The type of cursor determines the positioning methods that can be used with the cursor.

Serial cursor

A serial cursor is one defined without the SCROLL keyword.
For a serial cursor, each row of the result table can be fetched only once per OPEN of the cursor. When the cursor is opened, it is positioned before the first row in the result table. When a FETCH is issued, the cursor is moved to the next row in the result table. That row is then the current row. If host variables are specified (with the INTO clause on the FETCH statement), SQL moves the current row's contents into your program's host variables.
This sequence is repeated each time a FETCH statement is issued until the end-of-data (SQLCODE = 100) is reached. When you reach the end-of-data, close the cursor. You cannot access any rows in the result table after you reach the end-of-data. To use a serial cursor again, first close the cursor and then re-issue the OPEN statement. You can never back up using a serial cursor.

Scrollable cursor

For a scrollable cursor, the rows of the result table can be fetched many times. The cursor is moved through the result table based on the position option specified on the FETCH statement. When the cursor is opened, it is positioned before the first row in the result table. When a FETCH is issued, the cursor is positioned to the row in the result table that is specified by the position option. That row is then the current row. If host variables are specified (with the INTO clause on the FETCH statement), SQL moves the current row's contents into your program's host variables. Host variables cannot be specified for the BEFORE and AFTER position options.
This sequence is repeated each time a FETCH statement is issued. The cursor does not need to be closed when an end-of-data or beginning-of-data condition occurs. The position options enable the program to continue fetching rows from the table. The following scroll options are used to position the cursor when issuing a FETCH statement. These positions are relative to the current cursor location in the result table.

NEXT Positions the cursor on the next row. This is the default if no position is specified.
PRIOR Positions the cursor on the previous row.
FIRST Positions the cursor on the first row.
LAST Positions the cursor on the last row.
BEFORE Positions the cursor before the first row.
AFTER Positions the cursor after the last row.
CURRENT Does not change the cursor position.
RELATIVE n Evaluates a host variable or integer n in relationship to the cursor's current position. For example, if n is -1, the cursor is positioned on the previous row of the result table. If n is +3, the cursor is positioned three rows after the current row.

For a scrollable cursor, the end of the table can be determined by the following:
  FETCH AFTER FROM C1
Once the cursor is positioned at the end of the table, the program can use the PRIOR or RELATIVE scroll options to position and fetch data starting from the end of the table.

Parent topic:
Using a cursor

NEXT	Positions the cursor on the next row. This is the default if no position is specified.
PRIOR	Positions the cursor on the previous row.
FIRST	Positions the cursor on the first row.
LAST	Positions the cursor on the last row.
BEFORE	Positions the cursor before the first row.
AFTER	Positions the cursor after the last row.
CURRENT	Does not change the cursor position.
RELATIVE n	Evaluates a host variable or integer n in relationship to the cursor's current position. For example, if n is -1, the cursor is positioned on the previous row of the result table. If n is +3, the cursor is positioned three rows after the current row.