SQL/MP Programming Manual for COBOL85

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
4-11
Deleting a Single Row
After a successful DELETE operation, the SQLCA structure contains the number of
rows deleted. If an error occurs on a DELETE operation, the SQLCA contains the
approximate number of rows deleted. To return the contents of the SQLCA, use
SQLCA_DISPLAY2_ or SQLCA_TOBUFFER2_ procedure.
For more information, see Section 5, SQL/MP System Procedures, and Section 9,
Error and Status Reporting.
Deleting a Single Row
To delete a single row, move a key value to a host variable and then specify the host
variable in the WHERE clause. This DELETE statement deletes only one row of the
EMPLOYEE table because each value in EMPNUM (the primary key) is unique. A user
enters the value for the host variable named HOSTVAR-EMPNUM.
EXEC SQL DELETE FROM PERSNL.EMPLOYEE
WHERE EMPNUM = :HOSTVAR-EMPNUM END-EXEC.
Deleting Multiple Rows
If you do not need to check a column value before you delete a row, use a single
DELETE statement to delete multiple rows in a table. This example deletes all rows (or
employees) from the EMPLOYEE table specified by DELETE-DEPTNUM (which is
entered by a user).
EXEC SQL DELETE FROM PERSNL.EMPLOYEE
WHERE DEPTNUM = :DELETE-DEPTNUM END-EXEC.
This example deletes all suppliers from the PARTSUPP table who charge more than
TERMINAL-MAX-COST for a terminal. Terminal part numbers range from TERMINAL-
FIRST-NUM to TERMINAL-LAST-NUM.
EXEC SQL DELETE FROM INVENT.PARTSUPP
WHERE PARTNUM BETWEEN :TERMINAL-FIRST-NUM
AND :TERMINAL-LAST-NUM
AND PARTCOST > :TERMINAL-MAX-COST END-EXEC.
Using SQL Cursors
An SQL cursor is a named pointer that a host language program (C, COBOL, Pascal,
or TAL) can use to access a set of rows in a table or view, one row at a time. Using a
cursor, a program can process rows in the same way it might process records in a
sequential file. The program can test the data in each row at the current cursor position
and then, if the data meets certain criteria, the program can display, update, delete, or
ignore the row.
Figure 4-1 on page 4-12 shows the steps you follow to declare and use a static SQL
cursor in a COBOL program. A cursor operation must execute each statement in this
specified order. All steps are required, even if you execute the FETCH statement only
once to retrieve a single row.