An SQL control statement may allow multiple SQL statements
to be specified within the SQL control statement. These statements
are defined as SQL procedure statements.
Syntax
Syntax (continued)
Notes:
A COMMIT, ROLLBACK, CONNECT, DISCONNECT, SET CONNECTION, or SET
RESULT SETS statement is only allowed in an SQL procedure.
An ALTER PROCEDURE (SQL), ALTER FUNCTION (SQL scalar), or ALTER
FUNCTION (SQL table) statement with a REPLACE keyword is not allowed
in an SQL-routine-body.
A SET TRANSACTION statement is only allowed in an SQL function
or trigger.
A SET transition-variable-statement is
only allowed in a trigger. A fullselect and VALUES-statement can
also be specified in a trigger.
Notes
Comments:
Comments can be included within the body of an SQL procedure. In
addition to the double-dash form of comments (--), a comment can
begin with /* and end with */. The following rules apply to this
form of a comment.
The beginning characters /* must be adjacent and on the same line.
The ending characters */ must be adjacent and on the same line.
Comments can be started wherever a space is valid.
Comments can be continued to the next line.
Detecting
and processing error and warning conditions: As an SQL statement
is executed, the database manager stores information about the processing
of the statement in a diagnostics area (including the SQLSTATE and
SQLCODE), unless otherwise noted in the description of the SQL statement.
A completion condition indicates the SQL statement completed successfully,
completed with a warning condition, or completed with a not found
condition. An exception condition indicates that the SQL statement
was not successful.
A condition handler can be defined in a
compound statement to execute when an exception condition, a warning
condition, or a not found condition occurs. The declaration of a condition
handler includes the code that is to be executed when the condition
handler is activated. When a condition other than a successful completion
occurs in the processing of SQL-procedure-statement, if a condition
handler that could handle the condition is within scope, one such
condition handler will be activated to process the condition. See compound-statement for information about defining
condition handlers. The code in the condition handler can check for
a warning condition, not found condition, or exception condition and
take the appropriate action. Use one of the following methods at the
beginning of the body of a condition handler to check the condition
in the diagnostics area that caused the handler to be activated:
Issue a GET DIAGNOSTICS statement to request the condition information.
See GET DIAGNOSTICS statement.
Test the SQL variables SQLSTATE and SQLCODE.
If the condition is a warning and there is not a handler
for the condition, the above two methods can also be used outside
of the body of a condition handler immediately following the statement
for which the condition is wanted. If the condition is an error and
there is not a handler for the condition, the routine or trigger terminates
with the error condition.