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

Embedded-Only SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
3-15
C Examples of Compound Statement
SELECT Statements Within Compound Statements
Every 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 a warning or an error. A warning is displayed if no updates occurred before the
SELECT statement that did not return a row. In this case NonStop SQL/MX does not
roll back the transaction. An error is displayed if updates occurred before the SELECT
statement that did not return a row. Since updates occurred as part of this compound
statement NonStop SQL/MX rolls back the transaction. In both the cases the behavior
is atomic because none of the statements are executed.
C Examples of Compound Statement
•
These INSERT and SELECT statements inside the BEGIN and END keywords
execute sequentially:
...
EXEC SQL WHENEVER SQLERROR GOTO end_compound;
EXEC SQL
BEGIN
INSERT INTO SALES.ORDERS
(ORDERNUM, ORDER_DATE, DELIV_DATE, SALESREP, CUSTNUM)
VALUES (:hv_ordernum, :hv_orderdate, :hv_delivdate,
:hv_salesrep, :hv_custnum);
SELECT CUSTNUM, CUSTNAME, STREET, CITY, STATE, POSTCODE
INTO :hv_custnum, :hv_custname,
:hv_street, :hv_city, :hv_state, :hv_postcode
FROM SALES.CUSTOMER
WHERE CUSTNUM = :hv_custnum;
END;
end_compound:
... /* Process the error */