DB2 Cursor for Delete

The cursor for DELETE is used to delete the rows from the table or result table. Positioned DELETE is allowed in the cursor for DELETE.

FOR UPDATE OF clause is used to declare these cursors. WHERE CURRENT OF is used to delete the row using the current row positioning in the cursor. However, it is not mandatory to execute DELETE for every FETCH.

Syntax -

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

DELETE Query -

EXEC SQL
    DELETE table-name
     WHERE CURRENT OF cursor-name
END-EXEC.

Example -

Input -

Scenario1 - Declare the cursor for deleting rows that have the designation as "FRESHER" employee_details table.

Code -

DECLARE CURSOR -

   EXEC SQL 
	DECLARE E1 CURSOR FOR
     	SELECT EMP_NAME,
               MANAGER_ID
     	FROM   EMPLOYEE_DETAILS
		WHERE  DESIGNATION = "FRESHER"
        FOR    UPDATE OF SALARY
   END-EXEC.

DELETE -

   EXEC SQL 
	DELETE FROM EMPLOYEE_DETAILS
	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. 

---