Summary -

In this topic, we described about the below sections -

DECLARE CURSOR is used to declare a cursor in the application program.

The SELECT statement should use within the DECLARE CURSOR statement and should not have INTO clause in it.

The cursor name specified with DECLARE CURSOR acts as the name of the result table. However, DECLARE CURSOR may not always create any result table by declaring it.

DECLARE CURSOR only identifies the set of rows to retrieve with SELECT statement during the execution.

DECLARE CURSOR can have GROUP BY, ORDER BY clauses in it.

DECLARE CURSOR statement can code in DATA DIVISION and PROCEDURE DIVISION.

Syntax -

EXEC SQL
	DECLARE cursor-name/cursor-variable-name 
		[ASENSITIVE/INSENSITIVE] 
		[SCROLL/NO SCROLL] 
		[WITHOUT HOLD/ WITH HOLD] 
		[WITHOUT RETURN/WITH RETURN] CURSOR 
		FOR select-statement
		[FOR UPDATE OF column-name(s)/FOR FETCH ONLY]
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.

ASENSITIVE or INSENSITIVE -

This option specifies the cursor as sensitive or insensitive to the table changes(i.e., table inserts, updates, and deletes).

ASENSITIVE -

  • Specifies the cursor is sensitive to insert, update, or delete operations performed on the database table after the result table is generated.
  • ASENSITIVE depends on how the SELECT statement is optimized.
  • This option is the default option.
  • For example, when the positioned UPDATE or DELETE is performed using the cursor, the changes are immediately visible in the result table.

INSENSITIVE -

  • Specifies the cursor is not sensitive to insert, update, or delete operations performed on the database table after the result table is generated.
  • If INSENSITIVE is specified, the cursor is read-only.
  • If INSENSITIVE is specified, the result table is formed when the cursor is opened.
  • As a result, the result table size, rows order, and values do not change after the cursor opens (once the result table is generated).
  • As it is a read-only cursor, FOR UPDATE clause should not be used and can't use for positioning for updates or deletes.

NO SCROLL/SCROLL -

This option specifies the cursor is scrollable or non-scrollable.

NO SCROLL -

  • NO SCROLL specifies the cursor is non-scrollable.
  • If the cursor is not scrollable, each FETCH positions the cursor at the next sequential row in the resultset.
  • If none is specified, NO SCROLL is the default option.

SCROLL -

  • SCROLL 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).
  • We can say that the data in the result table is to remain static.

WITH/WITHOUT HOLD -

This option specifies the cursor should be closed or not during the COMMIT operation performed before CLOSE CURSOR.

WITHOUT HOLD -

  • WITHOUT HOLD specifies the cursor can be closed if any COMMIT operation is performed before CLOSE CURSOR.
  • If no option specified or WITHOUT HOLD option specifies, COMMIT closes the CURSOR and commits current work.
  • If the HOLD option is not specified, this is the default.

WITH HOLD -

  • WITH HOLD prevents the cursor from closing when the COMMIT operation is executed before the CLOSE CURSOR.
  • If WITH HOLD option specifies, COMMIT only commits the current unit of work.
  • The effect of the WITH HOLD cursor when COMMIT executes -
    • All open cursors defined WITH HOLD remain open.
    • The cursor positioning also won't be impacted.
    • All the locks released except the cursors defined WITH HOLD clause current position locks.
    • The valid operations on cursors WITH HOLD immediately followed by COMMIT execution are - FETCH and CLOSE.
    • UPDATE and DELETE CURRENT OF CURSOR only valid after the corresponding FETCH.
  • The effect of the WITH HOLD cursor when ROLLBACK executes -
    • All open cursors are closed.
    • All locks acquired are released.

WITHOUT RETURN or WITH RETURN -

This option specifies whether or not the result table of the cursor is returned from a procedure.

FOR UPDATE OF -

  • FOR UPDATE OF used to declare the updatable cursors.
  • Positioned UPDATE and positioned DELETE are allowed in updatable cursors.
  • WHERE CURRENT OF used to update or delete the rows in updatable cursors using the current row positioning.

FOR FETCH ONLY -

  • Specifies when declaring READ ONLY Cursors.
  • Positioned UPDATE and positioned DELETE not allowed in read-only cursors.

Examples -

Input-

employee_details table

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

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

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

Code-
   EXEC SQL 
	DECLARE CS2 CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	    FOR FETCH ONLY
   END-EXEC.

Scenario3 - Declare the cursor for retrieving employees in department 1000 from the table employee_details.

Code-
   EXEC SQL 
	DECLARE CS3 SCROLL CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  WHERE DEPT_ID = 1000
     	    FOR FETCH ONLY
   END-EXEC.

Scenario4 - Declare the cursor for retrieving employees in the order old to recent from the table employee_details.

Code-
   EXEC SQL 
	DECLARE CS4 SCROLL CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY DATE_OF_JOIN ASC
     	    FOR FETCH ONLY
   END-EXEC.

Scenario5 - Declare the updatable cursor for updating MANAGER for employees who are having manager "EMPLOYEE1" from the employee_details table.

Code-
   EXEC SQL 
	DECLARE CS5 CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  WHERE MANAGER_ID = "EMPLOYEE1"
     	    FOR UPDATE OF MANAGER_ID
   END-EXEC.

Scenario6 - Declare the scrollable cursor for retrieving rows from the table employee_details.

Code-
   EXEC SQL 
	DECLARE CS6 SCROLL CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR FETCH ONLY
   END-EXEC.

Scenario7 - Declare the sensitive scrollable cursor for retrieving rows from the table employee_details.

Code-
   EXEC SQL 
	DECLARE CS7 ASENSITIVE SCROLL CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR FETCH ONLY
   END-EXEC.

Scenario8 - Declare the insensitive scrollable cursor for retrieving rows from the table employee_details.

Code-
   EXEC SQL 
	DECLARE CS8 INSENSITIVE SCROLL CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR FETCH ONLY
   END-EXEC.

Scenario9 - Declare cursor WITH HOLD for retrieving rows from the table employee_details.

Code-
   EXEC SQL 
	DECLARE CS9 WITH HOLD CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR UPDATE OF MANAGER_ID
   END-EXEC.

Scenario10 - Declare WITHOUT HOLD cursor for retrieving rows from the table employee_details.

Code-
   EXEC SQL 
	DECLARE CS10 WITHOUT HOLD CURSOR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR FETCH ONLY
   END-EXEC.

Scenario11 - Declare cursor using a variable that contains cursor name for retrieving rows from the table employee_details.

Declaration-  05 WS-CS-VAR     PIC X(10) VALUE "CS11".
Code-
   EXEC SQL 
	DECLARE WS-CS-VAR FOR
     	 SELECT EMP_NAME
     	   FROM EMPLOYEE_DETAILS 
     	  ORDER BY EMP_ID
     	    FOR FETCH ONLY
   END-EXEC.

Practical Example -

Scenario - The below example describes how the cursor is declared and used in the COBOL + DB2 program.

Requirement - Display employee id, name, designation from department 1000 in the employee_details table.

Input-

employee_details table

Code -

CURSOR Program Code
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID. CREADONL.                                            
                                                                        
       ENVIRONMENT DIVISION.                                            
       DATA DIVISION.                                                   
       WORKING-STORAGE SECTION.                                         
                                                                        
           EXEC SQL                                                     
             INCLUDE SQLCA                                              
           END-EXEC.                                                    
                                                                        
           EXEC SQL                                                     
             INCLUDE EMPLDET                                            
           END-EXEC.                                                    
                                                                        
      * DECLARING CURSOR                                                
                                                                        
           EXEC SQL                                                     
                DECLARE CSR1 CURSOR FOR                                 
                SELECT EMP_ID,                                          
                       EMP_NAME,                                        
                       DESIGNATION                                      
                FROM   EMPLOYEE_DETAILS                                 
                WHERE  DEPT_ID = 1000                                   
                FOR    FETCH ONLY                                       
           END-EXEC.                                                    
                                                                        
       PROCEDURE DIVISION.                                              
                                                                        
      * OPENING CURSOR                                                  
                                                                        
           EXEC SQL                                                     
                OPEN CSR1                                               
           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 CSR1                                              
           END-EXEC.                                                    
                                                                        
           STOP RUN.                                                    
                                                                        
       FETCH-EMP-DETAILS.                                               
                                                                        
      * FETCH CURSOR                                                    
                                                                        
           EXEC SQL                                                     
                FETCH CSR1                                              
                INTO   :EMP-ID,                                         
                       :EMP-NAME,                                       
                       :DESIGNATION                                     
           END-EXEC.                                                    
                                                                        
           EVALUATE SQLCODE                                             
               WHEN ZERO                                                
                    DISPLAY  EMP-ID, "|", EMP-NAME, "|",                
                             DESIGNATION                                
               WHEN 100                                                 
                    CONTINUE                                            
               WHEN OTHER                                               
                    DISPLAY "DB2 ERROR:  ", SQLCODE                     
           END-EVALUATE.                                                
                                                                        
       FETCH-EMP-DETAILS-EXIT.                                          
           EXIT.                                                        
**************************** Bottom of Data ****************************

Output -

CURSOR Program Output