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

Embedded-Only SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
3-14
Compound (BEGIN...END) Statement
Compound (BEGIN...END) Statement
Considerations for Compound Statement
C Examples of Compound Statement
A compound statement is an embedded SQL statement that groups other embedded
SQL statements together.
A compound statement is an SQL/MX extension that you use only in embedded SQL
programs in C or COBOL.
SQL-statement;[SQL-statement;]...
is the SQL statement list between the BEGIN and END keywords. The SQL
statements inside a compound statement are executed in sequential order.
Therefore, the result of executing a compound statement is exactly the same result
as executing the contained statements one at a time in sequential order.
The SQL statements inside a compound statement are atomic. Therefore, if the
execution of any statement within the BEGIN and END keywords encounters an
error, NonStop SQL/MX automatically rolls back all of the statements.
Considerations for Compound Statement
SQL Statements in the List
You can use most SQL statements inside a compound statement; however, you cannot
use transaction statements (BEGIN WORK, COMMIT WORK, ROLLBACK WORK,
and SET TRANSACTION), UPDATE STATISTICS, and CONTROL statements.
You can use SELECT INTO to retrieve only one row, but cursors are not allowed in
compound statements. You can also use rowsets within compound statements to
retrieve multiple rows from database tables.
Executing Compound Statements in a DAM Process
To improve performance, use the CONTROL QUERY DEFAULT
OPTS_PUSH_DOWN_DAM option to force NonStop SQL/MX to consider executing
compound statements in a NonStop Data Access Manager (DAM) process. Some
compound statements, however, should not be executed in a DAM process because
they can cause inconsistent data or return the wrong results. For more information
about using this option, see the SQL/MX Query Guide.
BEGIN
SQL-statement;[SQL-statement;]...
END;
C/COBOL