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

Simple and Compound Statements
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
5-13
Compound Statements
Examples
This example deletes all rows (or employees) from the EMPLOYEE table specified by
the deptnum_to_delete host variable (which is entered by a user):
EXEC SQL
DELETE FROM persnl.employee
WHERE deptnum = :deptnum_to_delete;
This example deletes all suppliers from the PARTSUPP table who charge more than
TERMINAL-MAX-COST for a terminal. The 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.
Compound Statements
Compound statements enable SQL/MX clients to batch multiple SQL statements into
one data request to the server. This reduction in the number of client-server requests
results in increased transaction throughput and, consequently, faster response times.
The compound statement feature benefits both the client and the server. The client
waits for the server a reduced number of times, and the server switches contexts a
reduced number of times when responding to different client requests.
Compound statements are supported for static SQL only.
The SQL statements are coded within the BEGIN and END keywords. The statements
are executed sequentially and are atomic. Therefore, if the execution of any statement
within the BEGIN and END keywords encounters an error (such as a unique constraint
violation), NonStop SQL/MX automatically rolls back all the statements. NonStop
SQL/MX limits the quantity of SQL statements in a compound statement to 100.
Each SELECT statement within a BEGIN ... END statement should return at least one
row. If a SELECT statement within a BEGIN ... END statement does not return at least
one row, further execution of the compound statement stops, and NonStop SQL/MX
issues either a warning (8014) or an error (8015). The warning is displayed if no
updates occurred before the SELECT statement that did not return a row. In the case
of the warning, NonStop SQL/MX does not roll back the transaction. The error is
displayed if updates occurred before the SELECT statement that did not return a row.
Because the updates occurred as part of this compound statement, NonStop SQL/MX
rolls back the transaction. In both cases, the behavior is atomic because none of the
statements are executed.
BEGIN
SQL-statement;[SQL-statement;]...
END;
C
COBOL