DB2 Updatable Cursor

The updatable cursor updates the columns in the retrieved row(s). Positioned UPDATE and DELETE are allowed in updatable cursors.

FOR UPDATE OF clause used to declare the updatable cursors, and it is 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.

If the "FOR UPDATE OF" clause is coded while declaring a cursor, UPDATE or DELETE statements might execute on any specific row or all the rows of the result table. However, it is not mandatory to execute UPDATE or DELETE 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.

DELETE Query -

EXEC SQL
    DELETE table-name
     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 deleting 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. 
   
DELETE -

   EXEC SQL 
	DELETE FROM EMPLOYEE_DETAILS
	WHERE  CURRENT OF E2
   END-EXEC. 

---