Cursor for Delete
Summary
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.
DELETE CURSOR Example 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 -
