SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-172
Publish/Subscribe Examples of DELETE
•
Use a cursor and delete some of the returned rows during processing:
...
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT EMPNUM, DEPTNUM, JOBCODE, SALARY
FOR UPDATE ACCESS
FROM PERSNL.EMPLOYEE
FOR SERIALIZABLE ACCESS
FOR UPDATE
END-EXEC.
...
EXEC SQL OPEN emp_cursor END-EXEC.
...
EXEC SQL FETCH emp_cursor
INTO :hv-empnum, :hv-deptnum,
:hv-jobcode, :hv-salary
END-EXEC.
...
* Process fetched row.
IF hv-jobcode = 1234
EXEC SQL DELETE FROM PERSNL.EMPLOYEE
WHERE CURRENT OF emp_cursor
END-EXEC.
END-IF.
...
Publish/Subscribe Examples of DELETE
Suppose that these SQL/MP tables and index (and the metadata mappings) have been
created:
CREATE TABLE $db.dbtab.tab1 (a INT NOT NULL, b INT, c INT);
CREATE TABLE $db.dbtab.tab2 (a INT, b INT, c INT);
CREATE INDEX $db.dbtab.itab1 ON tab1(b, c);
CREATE SQLMP ALIAS cat.sch.tab1 $db.dbtab.tab1;
CREATE SQLMP ALIAS cat.sch.tab2 $db.dbtab.tab2;
•
This example shows the SET ON ROLLBACK clause. The SET ON ROLLBACK
column must be declared as NOT NULL; it cannot be part of a secondary index.
SET NAMETYPE ANSI;
SET SCHEMA cat.sch;
DELETE FROM tab1
SET ON ROLLBACK a = a + 1;
•
This example shows the SET ON ROLLBACK clause in an embedded delete of a
SELECT statement:
SELECT * FROM
(DELETE FROM tab1 SET ON ROLLBACK a = a + 1) tab1;