Summary -

In this topic, we described about the below sections -

The read-only cursor is a cursor that is used to perform only read operations on the table or result table.

FOR FETCH ONLY option used to declare READ ONLY Cursors.

Positioned UPDATE and positioned DELETE operations not allowed on read-only cursors.

The read-only cursor has the following advantages -

  • It improves the performance of FETCH operations.
  • No blocking and avoids exclusive locks.
  • It prevents some types of deadlocks.

"FOR FETCH ONLY" can specify as a synonym for "FOR READ ONLY".

Syntax -

Declaring Cursor -

EXEC SQL
	DECLARE cursor-name/cursor-variable-name 
	    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 the cursor for retrieving all employee names from the employee_details table.

Code-
   EXEC SQL 
	DECLARE E1 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 E2 CURSOR FOR
     	SELECT EMP_NAME
     	FROM   EMPLOYEE_DETAILS 
		FOR    FETCH ONLY 
   END-EXEC.
Note! Above E1 and E2 cursors are the same and read-only. Because the E1 cursor does not have any UPDATE clause, it is also considered a read-only cursor.

Scenario3 - Declare the read-only cursor for retrieving all employee names from highest salary to lowest.

Code-
   EXEC SQL 
	DECLARE E2 CURSOR FOR
     	SELECT EMP_NAME
     	FROM   EMPLOYEE_DETAILS 
		ORDER  BY SALARY DESC
		FOR    FETCH ONLY 
   END-EXEC.

Scenario4 - Declare the read-only cursor for retrieving the 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
		FOR    FETCH ONLY 
   END-EXEC.

Scenario5 - Declare the read-only cursor for retrieving the 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
		FOR    FETCH ONLY 
   END-EXEC.

Practical Example -

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

Requirement - Display employee_details table data.

Code -

READONLY 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 READONLY CURSOR                                       
                                                                        
           EXEC SQL                                                     
                DECLARE CSR1 CURSOR FOR                                 
                SELECT EMP_ID,                                          
                       EMP_NAME,                                        
                       DESIGNATION,                                     
                       MANAGER_ID,                                      
                       DATE_OF_HIRE,                                    
                       SALARY,                                          
                       DEPT_ID                                          
                FROM   EMPLOYEE_DETAILS                                 
                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,                                    
                       :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 -

READONLY CURSOR Program Output