SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Static SQL Cursors
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
6-9
Process the Retrieved Values
SET price = :new_price,
qty_available = :new_qty
WHERE CURRENT OF get_by_partnum;
... /* Branch back to retrieve the next row. */
EXEC SQL CLOSE get_by_partnum;
Positioned DELETE Statement
Use the positioned DELETE statement to delete a row in a table at the current cursor
position. You can delete multiple rows, one row at a time. Before you delete a row, you
can also test one or more column values if necessary. Use this general syntax:
For complete syntax, see the DELETE statement in the SQL/MX Reference Manual.
The WHERE CURRENT OF clause specifies the row to delete. The cursor must also
be declared as updatable (the default is read-only) in the query expression of the
DECLARE CURSOR statement.
If you delete all rows from a table, the table still exists until it is deleted by a DROP
TABLE statement.
A positioned DELETE statement must execute within the scope of other SQL
statements that refer to the cursor, including the DECLARE CURSOR, OPEN, and
FETCH statements.
For audited tables and views, the DELETE statement must execute within the same
transaction as the OPEN and FETCH statements for the cursor.
After the positioned DELETE statement executes, the cursor is positioned before the
next row in the result table.
Example
This example declares a cursor named get_by_partnum, retrieves data from the
PARTS table, tests the data, and deletes specific rows:
PROCEDURE DIVISION.
...
EXEC SQL DECLARE get_by_partnum CURSOR FOR
SELECT partnum, partdesc, price, qty_available
FROM sales.parts
WHERE partnum >= :min-partnum
END-EXEC.
...
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.
DELETE FROM table-name
WHERE CURRENT OF cursor-name
COBOL