DB2 Cursor for Update

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 is used to declare the updatable cursors and columns followed FOR UPDATE OF clause that might get updated. WHERE CURRENT OF used to update the rows in updatable cursors using the current row positioning.

If the "FOR UPDATE OF" clause is coded 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 -

EXEC SQL
	DECLARE cursor-name 
	    CURSOR FOR select-statement
	    FOR UPDATE OF columns-list
END-EXEC.

UPDATE Query -

EXEC SQL
    UPDATE table-name
	   SET column-name1 = :HV-column1,
	       column-name2 = :HV-column2,
		   ....
     WHERE CURRENT OF cursor-name
END-EXEC.

Example -

Input -

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. 

---