Summary -

In this topic, we described about the below sections -

OPEN CURSOR statement is used to open the cursor declared in the application program.

The OPEN CURSOR statement may create a temporary result table with the matched data that satisfies the conditions in the SELECT statement.

The temporary result table can be created during the OPEN CURSOR or FETCH CURSOR statements.

Once the result table gets created, the cursor is positioned at the first row of the result table.

If the result table is empty, the cursor state is the same as after reading the last row.

On the opening of CURSOR, the DB2 system performs two tasks -

  • Uses the SELECT statement in DECLARE CURSOR to identify the set of rows.
  • Creates a temporary result table and makes it ready for processing.

Suppose if any host variables are used in the DECLARE CURSOR. In that case, the host variables should have a valid value before the OPEN CURSOR is performed. Otherwise, DB2 uses the current value of the host variables, which might be LOW-VALUES also.

Syntax -

EXEC SQL
	OPEN cursor-name/cursor-variable-name 
	[USING variables/host-variables/expression]
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 - Open cusor.

Code-
   EXEC SQL 
	OPEN C1
   END-EXEC.

Scenario2 - Open cursor using host variables HV1, HV2 to receive two column values.

Code-
   EXEC SQL 
	OPEN C2 USING :HV1, :HV2
   END-EXEC.

Scenario3 - Open dynamic cursor using descriptor sqlda.

Code-
   EXEC SQL 
	OPEN C3 USING DESCRIPTOR :sqlda
   END-EXEC.

Scenario4 - Open cursor using a variable that contains cursor name.

Declaration-  05 WS-CS-VAR     PIC X(10) VALUE "C9".
Code-
   EXEC SQL 
	OPEN WS-CS-VAR
   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.

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