Cursor Delete
The cursor version immediately deletes the row to which the specified cursor is pointing. If the cursor is not currently pointing at a row when the delete is executed, then the DBMS generates an error indicating the need to issue a FETCH statement to position the cursor on a row. (After a deletion, the cursor points to a position after the deleted row, but before the next row, if any.)
The COMMIT and ROLLBACK statements close all open cursors. A common programming error is to delete the current row of a cursor, commit the change, and then loop to repeat the process. This process fails because the first commit closes the cursor.
In performing a cursor delete, make sure that certain conditions are met:
• A cursor must be declared in the same file in which any DELETE statements referencing that cursor appear. This applies also to any cursors referenced in dynamic DELETE statement strings.
• A cursor name in a dynamic DELETE statement must be unique among all open cursors in the current transaction.
• The cursor stipulated in the delete must be open before the statement is executed.
• The SELECT statement of the cursor must not contain a DISTINCT, GROUP BY, HAVING, ORDER BY, or UNION clause.
• The FROM clause of the delete and the FROM clause in the cursor's declaration must refer to the same database table.
The cursor name can be specified with a string constant or a host language variable.
If the statement does not delete any rows, the sqlcode variable in the SQLCA structure is set to 100.
The sqlerrd(3) variable in the SQLCA structure contains the number of rows deleted.