SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-116
COBOL Examples of DELETE
COBOL Examples of DELETE
•
Remove the row for JOHN WALKER from the EMPLOYEE table:
EXEC SQL DELETE FROM PERSNL.EMPLOYEE
WHERE FIRST_NAME = 'JOHN' AND LAST_NAME = 'WALKER'
SERIALIZABLE ACCESS
END-EXEC.
•
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
SERIALIZABLE ACCESS
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;