Generally, a cursor fetches a specific row only once during its life cycle. There might be some scenarios where the row should fetch more than once. IBM introduced the SCROLL concept to fulfill this requirement.

The scrollable cursor fetches row(s) as many times as required from the result table. The cursor navigates through the result table using the position specified on the FETCH statement.

SCROLL option specifies the cursor is scrollable. The scrollable cursor can scroll forward, backward, and reposition at the beginning, ending, or the relative offset position.

Scrollable cursors are especially useful for screen-based applications (CICS screens with page-up and page-down logic).

Predefined scroll options are used to position the cursor before issuing the FETCH statement with host variables. The FETCH statement uses these scroll options to position the cursor. However, the FETCH used for the position doesn't need host variables.

Syntax -

Declaring Cursor -

EXEC SQL
	DECLARE cursor-name
	    SCROLL CURSOR FOR 
		select-statement
	    FOR FETCH ONLY
END-EXEC.

FETCH for Positioning the Cursor -

EXEC SQL
	FETCH [scroll-option] 
	 FROM cursor-name
END-EXEC.

FETCH for retrieving data -

EXEC SQL
	FETCH cursor-name
	INTO :hv1,
	     :hv2,
	     .
	     .
	     :hvn
END-EXEC.

Scroll-option -

Scroll-option positioning is relative to the current cursor location in the result table. Below are the scroll-options used for cursor positioning -

Scroll-option	Description
NEXT	Positions the cursor on the next row from the current position. This option is the default, even no scroll-option is specified.
PRIOR	Positions the cursor on the previous row from the current 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	Doesn't change the cursor position.
RELATIVE n	Positions the cursor on the nth row relative to the current cursor position. n allows +ve and -ve values. For example - if n is -2, the cursor positions at the two rows before the current position. Similarly, for +ve value.

Example -

Input -

Cursor Declaration-

Code -

   EXEC SQL 
	DECLARE CSS1 SCROLL CURSOR FOR
     	SELECT EMP_NAME,
               MANAGER_ID
     	FROM   EMPLOYEE_DETAILS
        FOR    FETCH ONLY
   END-EXEC.

Scenario1 - Place the cursor on the next row of the result table from the current position.

Code -

   EXEC SQL 
	FETCH NEXT FROM CSS1
   END-EXEC.

Scenario2 - Place the cursor on the previous row of the result table from the current position.

Code -

EXEC SQL 
	FETCH PRIOR FROM CSS1
   END-EXEC.

---