Scrollable Cursor
Summary
Generally, a cursor fetches a specific row only once during its life cycle. There might be some scenarios that the row from the result table should fetch more than once. IBM introduced the SCROLL concept to fulfill this requirement.
The scrollable cursor fetches row(s) many times from the result table.
The cursor moved to through the result table using the position specified on the FETCH statement.
SCROLL option specifies the cursor is scrollable.
If the cursor is scrollable, the cursor can scroll forward, backward, and repositioned at the beginning, at the end, or at the relative offset position.
This is a powerful option to fetch data in random order.
Scrollable cursors are especially useful for screen-based applications (CICS screens with page-up and page-down logic).
There are predefined scroll options used to position the cursor before issuing the FETCH statement with host variables.
These scroll options are also used the FETCH statement to position the cursor. But, the FETCH used for the position doesn't need host variables along with it.
Syntax -
Declaring Cursor -
EXEC SQL
DECLARE cursor-name/cursor-variable-name
SCROLL CURSOR FOR
select-statement
FOR FETCH ONLY
END-EXEC.
Opening Cursor -
EXEC SQL
OPEN cursor-name/cursor-variable-name
END-EXEC.
FETCH for Positioning the Cursor -
EXEC SQL
FETCH [scroll-option]
FROM cursor-name/cursor-variable-name
END-EXEC.
FETCH for retrieving data -
EXEC SQL
FETCH cursor-name/cursor-variable-name
INTO :hv1,
:hv2,
.
.
:hvn
END-EXEC.
Closing Cursor -
EXEC SQL
CLOSE cursor-name/cursor-variable-name
END-EXEC.
cursor-name -
- Cursor-name is the name of the cursor used in an application program to refer to the cursor.
- The cursor-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
cursor-variable-name -
- Cursor-variable-name is a working-storage variable that contains the cursor name used in an application program to refer to the cursor.
- The cursor-variable-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
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. |
Examples -
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.
Scenario3 - Position the cursor on the first row of the result table.
Code -
EXEC SQL
FETCH FIRST FROM CSS1
END-EXEC.
Scenario4 - Position the cursor on the last row of the result table.
Code -
EXEC SQL
FETCH LAST FROM CSS1
END-EXEC.
Scenario5 - Position the cursor before the first row of the result table.
Code -
EXEC SQL
FETCH BEFORE FROM CSS1
END-EXEC.
Scenario6 - Position the cursor after the last row of the result table.
Code -
EXEC SQL
FETCH AFTER FROM CSS1
END-EXEC.
Scenario7 - Position the cursor 3 rows before the current cursor position.
Code -
EXEC SQL
FETCH RELATIVE -3 FROM CSS1
END-EXEC.
Scenario8 - Position the cursor 4 rows after the current cursor position.
Code -
EXEC SQL
FETCH RELATIVE +4 FROM CSS1
END-EXEC.
Practical Example -
Scenario - The below example describes how the scrollable cursor is coded in the COBOL + DB2 program.
Requirement - Display employee_details table data and from row 3 again.
SCROLLABLE CURSOR Example Code -
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CSCROLL.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE EMPLDET
END-EXEC.
* DECLARING SCROLLABLE CURSOR
EXEC SQL
DECLARE CSR_SCROLL SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION,
MANAGER_ID,
DATE_OF_HIRE,
SALARY,
DEPT_ID
FROM EMPLOYEE_DETAILS
ORDER BY EMP_ID
FOR FETCH ONLY
END-EXEC.
PROCEDURE DIVISION.
* OPENING CURSOR
EXEC SQL
OPEN CSR_SCROLL
END-EXEC.
* FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE
IF SQLCODE EQUAL ZERO
PERFORM FETCH-EMP-DETAILS
THRU FETCH-EMP-DETAILS-EXIT
UNTIL SQLCODE NOT EQUAL 0
END-IF.
* POSITION THE CURSOR ON THE FIRST ROW OF RESULT TABLE
EXEC SQL
FETCH FIRST FROM CSR_SCROLL
END-EXEC.
* POSITION THE CURSOR ON THE THIRD ROW OF RESULT TABLE
EXEC SQL
FETCH RELATIVE +1 FROM CSR_SCROLL
END-EXEC.
* FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE
IF SQLCODE EQUAL ZERO
PERFORM FETCH-EMP-DETAILS
THRU FETCH-EMP-DETAILS-EXIT
UNTIL SQLCODE NOT EQUAL 0
END-IF.
* CLOSING CURSOR
EXEC SQL
CLOSE CSR_SCROLL
END-EXEC.
STOP RUN.
FETCH-EMP-DETAILS.
* FETCH CURSOR
EXEC SQL
FETCH CSR_SCROLL
INTO :EMP-ID,
:EMP-NAME,
:DESIGNATION,
:MANAGER-ID
:MANAGER-ID-NI,
:DATE-OF-HIRE,
:SALARY,
:DEPT-ID
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY EMP-ID, "|", EMP-NAME, "|",
DESIGNATION, "|", MANAGER-ID, "|",
DATE-OF-HIRE, "|", SALARY, "|", DEPT-ID
WHEN 100
CONTINUE
WHEN OTHER
DISPLAY "DB2 ERROR: ", SQLCODE
END-EVALUATE.
FETCH-EMP-DETAILS-EXIT.
EXIT.
**************************** Bottom of Data ****************************
Output -
