Cursor with DELETE

A cursor can also delete rows. Declare it FOR UPDATE and use DELETE ... WHERE CURRENT OF to remove the row the cursor is currently positioned on.

Declaring a cursor for delete

• End the DECLARE SELECT with FOR UPDATE (a column list is optional for delete).
• Example: DECLARE C1 CURSOR FOR SELECT * FROM EMP WHERE ESAL <= 10000 FOR UPDATE.
• Like updateable cursors, the SELECT is restricted to a single table - no joins, no GROUP BY, no ORDER BY, no DISTINCT.
• DELETE ... WHERE CURRENT OF removes exactly one row: the row the last successful FETCH positioned the cursor on.
• After the DELETE, the cursor is positioned between rows - the next FETCH moves to the row after the deleted one.

Deleting the current row - full example

• Example:
  
  WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. 01 DCLDEPT2. 02 DNO PIC S9(4) COMP. 02 DNAME PIC X(10). EXEC SQL DECLARE C1 CURSOR FOR SELECT * FROM EMP WHERE ESAL <= 10000 FOR UPDATE END-EXEC. PROCEDURE DIVISION. PERFORM OPEN-PARA. PERFORM FETCH-PARA UNTIL SQLCODE NOT = 0. STOP RUN. OPEN-PARA. EXEC SQL OPEN C1 END-EXEC. IF SQLCODE NOT = 0 DISPLAY "OPEN FAILED " SQLCODE STOP RUN END-IF. FETCH-PARA. EXEC SQL FETCH C1 INTO :DCLDEPT2 END-EXEC. IF SQLCODE = 0 EXEC SQL DELETE FROM EMP WHERE CURRENT OF C1 END-EXEC IF SQLCODE NOT = 0 DISPLAY "DELETE FAILED " SQLCODE STOP RUN END-IF ELSE IF SQLCODE = 100 PERFORM CLOSE-PARA ELSE DISPLAY "FETCH FAILED " SQLCODE STOP RUN END-IF END-IF. CLOSE-PARA. EXEC SQL CLOSE C1 END-EXEC.
• Check SQLCODE after the DELETE as well - a failed delete (for example a referential constraint) must stop or be handled, not ignored.
• The DELETE names the base table (EMP) and the cursor (C1) in WHERE CURRENT OF - do not confuse the two names.

Rules for positioned deletes

• The cursor must be positioned on a row by a FETCH with SQLCODE 0 before DELETE ... WHERE CURRENT OF.
• Only one DELETE ... WHERE CURRENT OF is allowed per cursor position - after the delete, FETCH again before the next delete.
• A COMMIT closes the cursor (unless WITH HOLD), so re-OPEN after committing if more rows remain.
• If a referential constraint blocks the delete, SQLCODE -532 is returned - the row stays and the cursor position is unchanged.
• Always CLOSE the cursor when the loop ends, on both the normal path and the error path.