Input -

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

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.

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.

DELETE CURSOR Example Code -

----+----1----+----2----+----3----+----4----+----5----+
       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.

Output -

employee_details table after DELETE -

---