Summary -

In this topic, we described about the below sections -

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-

employee_details table

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.

Code -

SCROLLABLE CURSOR Program 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 -

SCROLLABLE CURSOR Program Output