Description

The following keywords specify a new position for the cursor: NEXT, PRIOR, FIRST, LAST, BEFORE, AFTER, CURRENT, and RELATIVE. Of those keywords, only NEXT may be used for cursors that have not been declared SCROLL.

NEXT
Positions the cursor on the next row of the result table relative to the current cursor position. NEXT is the default if no other cursor orientation is specified.
PRIOR
Positions the cursor on the previous row of the result table relative to the current cursor position.
FIRST
Positions the cursor on the first row of the result table.
LAST
Positions the cursor on the last row of the result table.
BEFORE
Positions the cursor before the first row of the result table.
AFTER
Positions the cursor after the last row of the result table.
CURRENT
Does not reposition the cursor, but maintains the current cursor position. If the cursor has been declared as DYNAMIC SCROLL and the current row has been updated so its place within the sort order of the result table is changed, an error is returned.
RELATIVE
Variable or integer is assigned to an integer value k. RELATIVE positions the cursor to the row in the result table that is either k rows after the current row if k>0, or k rows before the current row if k<0. If a variable is specified, it must be a numeric variable with zero scale and it must not include an indicator variable.
Table 55. Synonymous Scroll Specifications
Specification Alternative
RELATIVE +1 NEXT
RELATIVE -1 PRIOR
RELATIVE 0 CURRENT
FROM
This keyword is provided for clarity only. If a scroll position option is specified, then this keyword is required. If no scrolling option is specified, then the FROM keyword is optional.
cursor-name
Identifies the cursor to be used in the fetch operation. The cursor-name must identify a declared cursor as explained in Description for the DECLARE CURSOR statement. When the FETCH statement is executed, the cursor must be in the open state.

If a single-fetch or multiple-row-fetch clause is not specified, no data is returned to the user. However, the cursor is positioned and a row lock may be acquired. For more information about locking, see Isolation level.