Preparable dynamic DELETE statement: locating (Delete row using a preprocessable cursor)
Function
Deletes the row pointed to by a cursor. This command is used to delete a row by means of an EXECUTE statement after performing preprocessing by a PREPARE statement, or to perform preprocessing and execution at once by means of an EXECUTE IMMEDIATE statement.
Privileges
A user who has the DELETE privilege for a table can delete rows from that table.
Format
DELETE [FROM [authorization-identifier.]table-identifier
[IN (RDAREA-name-specification)][[AS] correlation-name]
[used-index-SQL-optimization-specification]]
WHERE CURRENT OF GLOBAL cursor-name
[WITH ROLLBACK] |
Operands
For details about operands other than GLOBAL or cursor-name, and for rules on operands, see DELETE statement Format 1.
- WHERE CURRENT OF GLOBAL cursor-name
- GLOBAL
- Specifies GLOBAL as the scope for cursor-name.
- cursor-name
- Specifies the name of the cursor that points to the row to be deleted.
- The cursor specified in cursor-name is the cursor identified by the extended cursor name specified in the ALLOCATE CURSOR statement. The value of the extended cursor name specified in the ALLOCATE CURSOR statement should be specified in cursor-name. However, a result set cursor cannot be specified in cursor-name.
- At execution time, the cursor specified in cursor-name must be open and must be positioned on the row to be deleted.
- The cursor identified in cursor-name does not have any row that it can point to, after the execution of the DELETE statement. If any row after the deleted row is to be updated or deleted, the FETCH statement should be executed on the cursor to move it.
- If extended-cursor-name is specified, an extended cursor must be specified for queries that specify the FOR UPDATE clause. For details about the FOR UPDATE clause, see the FOR UPDATE clause under Operands in Dynamic SELECT statement Format 1 (Retrieve dynamically) in this chapter.
Common rules
- After performing preprocessing using a PREPARE statement, use the EXECUTE statement to execute, or use the EXECUTE IMMEDIATE statement to preprocess and execute at once.
- When omitting a table identifier, make sure that before preprocessing is performed, the ALLOCATE CURSOR statement is used to allocate the cursor to the dynamic SELECT statement. In this operation, the table that is the object of retrieval specified in the dynamic SELECT statement to which the cursor is allocated is assumed. When specifying a table identifier, it is not necessary that the cursor be allocated to the dynamic SELECT statement before the preprocessing.
- The common rules on the DELETE statement Format 1 are applicable to the other common rules.
Rules on referential constraints
- The rules on the DELETE statement Format 1 are applicable.
Notes
- The notes on the DELETE statement Format 1 are applicable.
Examples
- Delete the row specified with the cursor (cr (value: CR1)) from the inventory table (STOCK):
PREPARE :sel FOR 'SELECT * FROM STOCK FOR UPDATE'
<Assign CR1 to the embedded variable cr>
ALLOCATE CURSOR GLOBAL :cr FOR GLOBAL :sel
OPEN GLOBAL :cr
FETCH GLOBAL :cr INTO <Name of the variable into which columns are fetched>
PREPARE PRE1 FOR
'DELETE FROM STOCK WHERE CURRENT OF GLOBAL CR1'
EXECUTE PRE1
DEALLOCATE PREPARE GLOBAL :sel