Summary -
In this topic, we described about the below sections -
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/FOR UPDATE OF clause used to declare these cursors.
WHERE CURRENT OF used to delete the row in these 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/FOR UPDATE OF" clause is specified while declaring a cursor, DELETE statements might execute on any specific row or all the rows of the result table. However, it is not mandatory to execute DELETE for every FETCH.
Syntax -
Declaring Cursor - EXEC SQL DECLARE cursor-name/cursor-variable-name CURSOR FOR select-statement FOR UPDATE OF 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 DELETE FROM table_name 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-

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.
Practical Example -
Scenario - The below example describes how the delete cursor is coded in the COBOL + DB2 program.
Requirement - Delete the rows whose designation is TESTER.
Code -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- ***************************** Top of Data ****************************** IDENTIFICATION DIVISION. PROGRAM-ID. CDELETE. 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 END-EXEC. 01 WS-VAR. 05 WS-DCNT PIC 9(02) VALUE ZEROES. 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 DELETED: ", WS-DCNT. 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 DELETE-EMP-DETAILS THRU DELETE-EMP-DETAILS-EXIT WHEN 100 CONTINUE WHEN OTHER DISPLAY "DB2 ERROR: ", SQLCODE END-EVALUATE. FETCH-EMP-DETAILS-EXIT. EXIT. DELETE-EMP-DETAILS. EXEC SQL DELETE FROM EMPLOYEE_DETAILS WHERE CURRENT OF CSR1 END-EXEC. EVALUATE SQLCODE WHEN ZERO COMPUTE WS-DCNT = WS-DCNT + 1 WHEN 100 DISPLAY "DELETING ROW NOT FOUND" WHEN OTHER DISPLAY "DB2 ERROR: ", SQLCODE END-EVALUATE. DELETE-EMP-DETAILS-EXIT. EXIT. **************************** Bottom of Data ****************************
Output -

employee_details table after DELETE -
