SQL Programming Manual for Pascal

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for Pascal—528614-001
3-13
Multirow Delete Operation Using a Cursor
WHERE partnum BETWEEN 6400 and 6700
AND partcost > 1600.00;
Multirow Delete Operation Using a Cursor
To read and test a value before you delete a row, you must use a cursor. A cursor
allows you to delete a set of rows one row at a time.
You specify the set of rows with the SELECT clause in the DECLARE CURSOR
statement. You read each row using a FETCH statement, test the data, and then
ignore or delete the row depending on your test logic. If you determine that you want to
delete the data from the table, you use a DELETE WHERE CURRENT statement.
For audited tables and views, a TMF transaction must be in progress. The same TMF
transaction must include both fetching and deleting the row. If you want to ensure that
a lock is held on the row you are deleting until the transaction completes, you can
declare the cursor with a DELETE WHERE CURRENT statement that specifies a
column in the rows to be deleted.
This example declares a cursor, fetches and tests the data, and deletes the current
row:
EXEC SQL
DECLARE CHECK_EMP CURSOR FOR
SELECT DEPTNUM
FROM EMPLOYEE
WHERE DEPTNUM = :DEPTNUM_DEL
OR DEPTNUM = :DEPTNUM_UPD
FOR UPDATE OF DEPTNUM;
EXEC SQL
FETCH CHECK_EMP INTO :DEPTNUM;
* Program logic to test the data
...
EXEC SQL
DELETE FROM EMPLOYEE WHERE CURRENT OF CHECK_EMP;
DELETE WHERE CURRENT and Static SQL Cursors
A static SQL cursor can be referred to only within the compilation unit in which the
cursor was declared. This means that for static SQL programs, any DELETE WHERE
CURRENT, DECLARE CURSOR, OPEN, FETCH, and CLOSE statements that refer to
the cursor must be in the same compilation unit.
If the program exits a procedure with an open cursor, procedures that execute later can
still refer to the cursor, as long as the procedures are in the same compilation unit.
Caution. Do not use a stand-alone DELETE operation to delete a row that has been retrieved
using a cursor FETCH operation. Doing so can validate the cursor’s buffering for the table and
might degrade performance.