Summary -

In this topic, we described about the below sections -

The cursor for the update is used to perform updates to the columns in the retrieved row(s).

Positioned UPDATE is allowed in the cursor for the update.

FOR UPDATE OF clause used to declare the updatable cursors.

FOR UPDATE OF followed with a list of columns that might get updated.

WHERE CURRENT OF used to update or delete the rows in updatable cursors using the current row positioning.

All columns can be updated if a FOR UPDATE OF, an ORDER BY, a FOR READ ONLY, or a SCROLL clause without a DYNAMIC clause is not specified.

If the "FOR UPDATE OF" clause is specified while declaring a cursor, the UPDATE statement might execute on any specific row or all rows of the result table. However, it is not mandatory to execute UPDATE for every FETCH.

Syntax -

Declaring Cursor -

EXEC SQL
	DECLARE cursor-name/cursor-variable-name 
	    CURSOR FOR select-statement
	    FOR UPDATE OF columns-list
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.

UPDATE Statement -

EXEC SQL
	UPDATE table_name
	   SET column1 = :hv1,
	       column2 = :hv2,
		   .
		   .
		   columnn = :hvn
    WHERE CURRENT OF cursor-name/cursor-variable-name
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 updating column manager_id of employee_details table.

Code -

DECLARE CURSOR -

   EXEC SQL 
	DECLARE E1 CURSOR FOR
     	SELECT EMP_NAME,
               MANAGER_ID
     	FROM   EMPLOYEE_DETAILS
        FOR    UPDATE OF MANAGER_ID
   END-EXEC.

UPDATE -

   EXEC SQL 
	UPDATE EMPLOYEE_DETAILS
	SET    MANAGER_ID = :WS-MANAGER-ID		   
	WHERE  CURRENT OF E1
   END-EXEC.

Scenario2 - Declare the cursor for updating all columns of the employee_details table.

Code -

DECLARE CURSOR -

   EXEC SQL 
	DECLARE E2 CURSOR FOR
     	SELECT EMP_ID,
               EMP_NAME,
               DESIGNATION,
               MANAGER_ID,
               DATE_OF_HIRE,
               SALARY,
               DEPT_ID
     	FROM   EMPLOYEE_DETAILS 
     	FOR UPDATE 
   END-EXEC.
   
UPDATE -

   EXEC SQL 
	UPDATE EMPLOYEE_DETAILS
	SET    DESIGNATION = :WS-DESG,
               MANAGER-ID  = :WS-MANAGER-ID,
               DATE_OF_HIRE = :WS-DOH,
               SALARY       = :WS-SALARY		   
	WHERE  CURRENT OF E2
   END-EXEC.
   

Scenario3 - Declare the cursor for updating designation "FRESHER" in department 1000 of employee_details table.

Code -

DECLARE CURSOR -

   EXEC SQL 
	DECLARE E3 CURSOR FOR
     	SELECT EMP_ID,
               EMP_NAME,
               DESIGNATION
     	FROM   EMPLOYEE_DETAILS 
     	WHERE  DESIGNATION = "FRESHER"
		AND    DEPT_ID = 1000
     	FOR    UPDATE OF DESIGNATION 
   END-EXEC.
   
UPDATE -

   EXEC SQL 
	UPDATE EMPLOYEE_DETAILS
	SET    DESIGNATION = "JUNIOR DEVELOPER"	
	WHERE  CURRENT OF E3
   END-EXEC.

Scenario4 - Declare the cursor for updating designation "FRESHER" in departments 1000 and 2000 of the employee_details table.

Code -

DECLARE CURSOR -

   EXEC SQL 
	DECLARE E4 CURSOR FOR
     	SELECT EMP_ID,
               EMP_NAME,
               DESIGNATION
     	FROM   EMPLOYEE_DETAILS 
     	WHERE  DESIGNATION = "FRESHER"
		AND    DEPT_ID IN (1000, 2000)
		ORDER  BY DEPT_ID ASC
     	FOR    UPDATE OF DESIGNATION 
   END-EXEC.
   
UPDATE -

   EXEC SQL 
	UPDATE EMPLOYEE_DETAILS
	SET    DESIGNATION = "JUNIOR DEVELOPER"	
	WHERE  CURRENT OF E4
   END-EXEC.

Practical Example -

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

Requirement - Hike the salary of TESTER(s) by 3000.

Code -

UPDATE CURSOR Program Code
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID. CUPDATE.                                             
                                                                        
       ENVIRONMENT DIVISION.                                            
       DATA DIVISION.                                                   
       WORKING-STORAGE SECTION.                                         
                                                                        
           EXEC SQL                                                     
             INCLUDE SQLCA                                              
           END-EXEC.                                                    
                                                                        
           EXEC SQL                                                     
             INCLUDE EMPLDET                                            
           END-EXEC.                                                    
                                                                        
      * DECLARING UPDATE   CURSOR                                       
                                                                        
           EXEC SQL                                                     
                DECLARE CSR1 CURSOR FOR                                 
                SELECT EMP_ID,                                          
                       EMP_NAME,                                        
                       DESIGNATION,                                     
                       MANAGER_ID,                                      
                       SALARY                                           
                FROM   EMPLOYEE_DETAILS                                 
                WHERE  DESIGNATION = 'TESTER'                           
                  AND  DEPT_ID = 2000                                   
                FOR    UPDATE OF SALARY                                 
           END-EXEC.                                                    
                                                                        
        01 WS-VAR.                                                      
           05 WS-UCNT               PIC 9(02) VALUE ZEROES.             
           05 WS-UPDATED-SAL        PIC S9(5)V9(2) USAGE COMP-3.        
                                                                        
       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.                                                    
                                                                        
           DISPLAY "NO OF ROWS UPDATED:  ", WS-UCNT.                    
                                                                        
           STOP RUN.                                                    
                                                                        
       FETCH-EMP-DETAILS.                                               
                                                                        
      * FETCH CURSOR                                                    
                                                                        
           EXEC SQL                                                     
                FETCH CSR1                                              
                INTO   :EMP-ID,                                         
                       :EMP-NAME,                                       
                       :DESIGNATION,                                    
                       :MANAGER-ID                                      
                       :MANAGER-ID-NI,                                  
                       :SALARY                                          
           END-EXEC.                                                    
                                                                        
           EVALUATE SQLCODE                                             
               WHEN ZERO                                                
                    PERFORM UPDATE-EMP-DETAILS                          
                       THRU UPDATE-EMP-DETAILS-EXIT                     
               WHEN 100                                                 
                    CONTINUE                                            
               WHEN OTHER                                               
                    DISPLAY "DB2 ERROR:  ", SQLCODE                     
           END-EVALUATE.                                                
                                                                        
       FETCH-EMP-DETAILS-EXIT.                                          
           EXIT.                                                        
                                                                        
       UPDATE-EMP-DETAILS.                                              
                                                                        
           COMPUTE WS-UPDATED-SAL = SALARY + 3000.                      
                                                                        
           EXEC SQL                                                     
                UPDATE EMPLOYEE_DETAILS                                 
                   SET SALARY = :WS-UPDATED-SAL                         
                 WHERE CURRENT OF CSR1                                  
           END-EXEC.                                                    
                                                                        
           EVALUATE SQLCODE                                             
               WHEN ZERO                                                
                    COMPUTE WS-UCNT = WS-UCNT + 1                       
               WHEN 100                                                 
                    DISPLAY "UPDATING ROW NOT FOUND"                    
               WHEN OTHER                                               
                    DISPLAY "DB2 ERROR:  ", SQLCODE                     
           END-EVALUATE.                                                
                                                                        
       UPDATE-EMP-DETAILS-EXIT.                                         
           EXIT.                                                        
**************************** Bottom of Data ****************************

Output -

UPDATE CURSOR Program Output

employee_details table after UPDATE -

UPDATE CURSOR Program Output