SQL/MP Programming Manual for COBOL

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
4-22
Multirow DELETE Statement
Multirow DELETE Statement
When used with a cursor, a DELETE statement deletes multiple rows one row at a time
from a table or protection view. You identify the set of rows to delete (or test) in the
associated SELECT statement. Before you delete a row, you can test one or more
column values, and then, if you decide to delete the row, specify the WHERE
CURRENT OF clause in the DELETE statement.
If you delete all rows from a table, the table still exists until it is deleted from the catalog
with a DROP TABLE statement.
To execute a DELETE statement, a process started by a program must have read and
write access to the table or view containing the rows to be deleted and to tables or
views in subqueries of the search condition. For information about process access, see
Required Access Authority on page 7-1.
A DELETE statement must execute within the scope of all other SQL statements,
including the DECLARE CURSOR, OPEN, FETCH, INSERT, and CLOSE statements,
that refer to the cursor. For audited tables and views, the DELETE statement must
execute within the same TMF transaction as the OPEN and FETCH statements for the
cursor.
This example declares a cursor named GET-BY-PARTNUM, fetches data from the
PARTS table, tests the data, and then deletes specific rows:
EXEC SQL DECLARE GET-BY-PARTNUM CURSOR FOR
SELECT PARTNUM,
PARTDESC,
PRICE,
QTY-AVAILABLE
FROM SALES.PARTS
WHERE (PARTNUM >= :PARTNUM OF PARTS)
ORDER BY PARTNUM
STABLE ACCESS END-EXEC.
PROCEDURE DIVISION.
...
EXEC SQL OPEN GET-BY-PARTNUM END-EXEC.
EXEC SQL FETCH GET-BY-PARTNUM ... END-EXEC.
* Test the value(s) in the current row.
...
* Delete the current row.
EXEC SQL DELETE FROM SALES.PARTS
WHERE CURRENT OF GET-BY-PARTNUM
END-EXEC.
...
EXEC SQL CLOSE GET-BY-PARTNUM END-EXEC.
Note. Do not use a stand-alone DELETE statement to delete a row that has been retrieved
using a FETCH statement. A stand-alone DELETE statement can invalidate the cursor’s
buffering for the table and degrade performance.