Non-scrollable Cursor/Serial Cursor


The non-scrollable cursor processes the row(s) one by one from the table or result table.

NO SCROLL keyword used to specify the cursor is non-scrollable.

If the cursor is not scrollable, each FETCH operation positions the cursor at the next sequential row in the result set.

If the SCROLL option is not specified, NO SCROLL is the default option.

Positioned UPDATE and positioned DELETE operations are allowed on Non-scrollable cursors.

"Non-Scrollable Cursors" also called as "Serial Cursors".

Syntax -


Declaring Cursor -

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

Opening Cursor -

EXEC SQL
	OPEN cursor-name/cursor-variable-name
END-EXEC.

Fetching Cursor -

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.

Examples -


Input-

employee_details table

Scenario1 - Declare non-scrolling cursor for retrieving all employee names from employee_details table.

Code -

   EXEC SQL 
	DECLARE E1 NO SCROLL CURSOR FOR
     	SELECT EMP_NAME
     	FROM   EMPLOYEE_DETAILS 
   END-EXEC.

Scenario2 - Declare the cursor for retrieving all employee names from the employee_details table.

Code -

   EXEC SQL 
	DECLARE E2 CURSOR FOR
     	SELECT EMP_NAME
     	FROM   EMPLOYEE_DETAILS 
   END-EXEC.

Scenario3 - Declare the cursor for retrieving employee names under department 1000 from the employee_details table.

Code -

   EXEC SQL 
	DECLARE E3 NO SCROLL CURSOR FOR
     	SELECT EMP_NAME
     	FROM   EMPLOYEE_DETAILS 
		WHERE  DEPT_ID = 1000 
   END-EXEC.

Scenario4 - Declare non-scrolling cursor for retrieving all employee names from highest salary to lowest.

Code -

   EXEC SQL 
	DECLARE E4 CURSOR FOR
     	SELECT EMP_NAME
     	FROM   EMPLOYEE_DETAILS 
		ORDER  BY SALARY DESC 
   END-EXEC.

Scenario5 - Declare a non-scrolling cursor for retrieving a sum of salaries at the designation level.

Code -

   EXEC SQL 
	DECLARE E2 CURSOR FOR
     	SELECT DESIGNATION,
		       SUM(SALARY) SALARIES_TOTAL
     	FROM   EMPLOYEE_DETAILS 
		GROUP  BY DESIGNATION
   END-EXEC.

Scenario6 - Declare non-scrolling cursor for retrieving a sum of salaries > 18000 at designation level.

Code -

   EXEC SQL 
	DECLARE E2 CURSOR FOR
     	SELECT DESIGNATION,
		       SUM(SALARY) SALARIES_TOTAL
     	FROM   EMPLOYEE_DETAILS 
		GROUP  BY DESIGNATION
		HAVING SUM(SALARY) > 18000
   END-EXEC.

Scenario7 - Declare non-scrolling cursor for retrieving employee names under department 1000 from employee_details table and hike 2000 salary.

Code -

   EXEC SQL 
	DECLARE E2 CURSOR FOR
     	SELECT EMP_NAME,
		       SALARY
     	FROM   EMPLOYEE_DETAILS
        WHERE  DEPT_ID = 1000
        FOR    UPDATE OF SALARY		
   END-EXEC.

Practical Example -


Scenario - The below example describes how the non-scrollable cursor is coded in the COBOL + DB2 program.

Requirement - Display employee_details table data for department 1000.

NON SCROLLABLE CURSOR Example Code -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID. CNSCROLL.                                            
                                                                        
       ENVIRONMENT DIVISION.                                            
       DATA DIVISION.                                                   
       WORKING-STORAGE SECTION.                                         
                                                                        
           EXEC SQL                                                     
             INCLUDE SQLCA                                              
           END-EXEC.                                                    
                                                                        
           EXEC SQL                                                     
             INCLUDE EMPLDET                                            
           END-EXEC.                                                    
                                                                        
      * DECLARING READONLY CURSOR                                       
                                                                        
           EXEC SQL                                                     
                DECLARE CSR-NSCROLL NO SCROLL CURSOR FOR                
                 SELECT EMP_ID,                                         
                        EMP_NAME,                                       
                        DESIGNATION,                                    
                        MANAGER_ID,                                     
                        DATE_OF_HIRE,                                   
                        SALARY,                                         
                        DEPT_ID                                         
                   FROM EMPLOYEE_DETAILS                                
                  WHERE DEPT_ID = 1000                                  
           END-EXEC.                                                    
                                                                        
       PROCEDURE DIVISION.                                              
                                                                        
      * OPENING CURSOR                                                  
                                                                        
           EXEC SQL                                                     
                OPEN CSR-NSCROLL                                        
           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-NSCROLL                                       
           END-EXEC.                                                    
                                                                        
           STOP RUN.                                                    
                                                                        
       FETCH-EMP-DETAILS.                                               
                                                                        
      * FETCH CURSOR                                                    
                                                                        
           EXEC SQL                                                     
                FETCH CSR-NSCROLL                                       
                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 -

NON SCROLLABLE CURSOR Program Output