Embedded Usage
In an embedded DELETE statement, specify the cursor name with a string constant or a host language variable.
If the DELETE statement does not delete any rows, the sqlcode variable in the SQLCA structure is set to 100. Otherwise, the sqlerrd(3) variable in the SQLCA structure contains the number of rows deleted.
There are two embedded versions of the DELETE statement: the first is like the interactive version of the statement, and the second is for use with cursors.
Non-Cursor Delete
The non-cursor version of the embedded SQL DELETE statement is identical to the interactive delete. Host language variables can be used to represent constant expressions in the search_condition but they cannot specify names of database columns or include any operators. The complete search condition can be specified using a host string variable.
To reduce the overhead required to execute a (non-cursor) delete repeatedly, specify the keyword REPEATED. The REPEATED keyword directs the DBMS Server to save the execution plan of the DELETE statement the first time the statement is executed, thereby improving subsequent executions of the same delete. The REPEATED keyword is valid for non-cursor deletes only and is ignored if used with the cursor version of the statement. The repeated delete cannot be specified as a dynamic SQL statement.
If the search_condition is dynamically constructed and the search_condition is changed after initial execution of the statement, the REPEATED option cannot be specified. The saved execution plan is based on the initial values in the search_condition and changes are ignored. This rule does not apply to simple variables used in search_conditions.
Cursor Delete
The cursor version 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, the DBMS Server generates an error.
To position the cursor to a row, issue a FETCH statement. After a deletion, the cursor points to a position after the deleted row, but before the next row, if any.
If the cursor is opened for direct update, the deletion takes effect immediately. If the cursor is opened for deferred update, the deletion takes effect when the cursor is closed. If the cursor is opened for deferred update, a row cannot be deleted after it has been updated. If an attempt is made to do so, the DBMS Server returns an error indicating an ambiguous update operation.
Both 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 continue in a loop to repeat the process. This process fails because the first commit closes the cursor.
A cursor delete can be executed dynamically using the PREPARE and EXECUTE statements. However, a cursor delete can only be prepared after the referenced cursor is opened. The prepared cursor delete remains valid while the cursor is open. If the named cursor is closed and reopened, the corresponding DELETE statement must be prepared again. If an attempt is made to execute the DELETE statement associated with the previously open cursor, the DBMS Server returns an error.
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.
Last modified date: 04/26/2024