An SQL control statement can allow multiple SQL statements
to be specified within the SQL control statement. These statements
are defined as SQL procedure statements.
Syntax
>>-+-SQL-control-statement---------------------------------------------------------------------------------+-><
+-ALLOCATE CURSOR statement-----------------------------------------------------------------------------+
| (1) |
+-ALTER DATABASE statement------------------------------------------------------------------------------+
| (1) (2) |
+-ALTER FUNCTION statement (external scalar, external table, sourced, SQL scalar, or SQL table)---------+
| (1) |
+-ALTER INDEX statement---------------------------------------------------------------------------------+
| (1) |
+-ALTER MASK statement----------------------------------------------------------------------------------+
| (1) |
+-ALTER PERMISSION statement----------------------------------------------------------------------------+
| (1) (2) |
+-ALTER PROCEDURE statement (external, SQL - external, or SQL - native)---------------------------------+
| (1) |
+-ALTER SEQUENCE statement------------------------------------------------------------------------------+
| (1) |
+-ALTER STOGROUP statement------------------------------------------------------------------------------+
| (1) |
+-ALTER TABLE statement---------------------------------------------------------------------------------+
| (1) |
+-ALTER TABLESPACE statement----------------------------------------------------------------------------+
| (1) |
+-ALTER TRUSTED CONTEXT statement-----------------------------------------------------------------------+
| (1) |
+-ALTER VIEW statement----------------------------------------------------------------------------------+
+-ASSOCIATE LOCATORS statement--------------------------------------------------------------------------+
+-CALL statement----------------------------------------------------------------------------------------+
+-CLOSE statement---------------------------------------------------------------------------------------+
| (1) |
+-COMMENT statement-------------------------------------------------------------------------------------+
| (3) |
+-COMMIT statement--------------------------------------------------------------------------------------+
| (3) |
+-CONNECT statement-------------------------------------------------------------------------------------+
| (1) |
+-CREATE ALIAS statement--------------------------------------------------------------------------------+
| (1) |
+-CREATE DATABASE statement-----------------------------------------------------------------------------+
| (1) |
+-CREATE FUNCTION statement (external scalar, external table, or sourced)-------------------------------+
| (1) |
+-CREATE GLOBAL TEMPORARY TABLE statement---------------------------------------------------------------+
| (1) |
+-CREATE INDEX statement--------------------------------------------------------------------------------+
| (1) |
+-CREATE PROCEDURE statement (external)-----------------------------------------------------------------+
| (1) |
+-CREATE ROLE statement---------------------------------------------------------------------------------+
| (1) |
+-CREATE SEQUENCE statement-----------------------------------------------------------------------------+
| (1) |
+-CREATE STOGROUP statement-----------------------------------------------------------------------------+
| (1) |
+-CREATE SYNONYM statement------------------------------------------------------------------------------+
| (1) (4) |
+-CREATE TABLE statement--------------------------------------------------------------------------------+
| (1) |
+-CREATE TABLESPACE statement---------------------------------------------------------------------------+
| (1) |
+-CREATE TRUSTED CONTEXT statement----------------------------------------------------------------------+
| (1) |
+-CREATE TYPE statement---------------------------------------------------------------------------------+
| (1) |
'-CREATE VIEW statement---------------------------------------------------------------------------------'
Notes:
- The statement is not allowed in an
SQL-routine-body for an SQL function.
- An ALTER FUNCTION statement (SQL scalar) or an ALTER PROCEDURE
statement (SQL - native) with an ADD VERSION or REPLACE clause is not allowed in an
SQL-routine-body.
- The COMMIT, ROLLBACK, CONNECT, RELEASE, and SET CONNECTION
statements must only be specified within the body of an SQL procedure. The COMMIT statement and the
ROLLBACK statement (without the TO SAVEPOINT clause) must not be issued in a routine body if the
routine is in the calling chain of an SQL routine, or an external routine.
- A CREATE TABLE statement must not include a column defined as a LOB
or XML.
>>-+-DECLARE CURSOR statement---------------------+------------><
| (1) |
+-DECLARE GLOBAL TEMPORARY TABLE statement-----+
+-DELETE statement-----------------------------+
| (1) |
+-DROP statement-------------------------------+
| (1) |
+-EXCHANGE statement---------------------------+
| (1) |
+-EXECUTE statement----------------------------+
| (1) |
+-EXECUTE IMMEDIATE statement------------------+
| (2) |
+-FETCH statement------------------------------+
+-GET DIAGNOSTICS statement--------------------+
| (1) |
+-GRANT statement------------------------------+
+-INSERT statement-----------------------------+
| (1) |
+-LABEL statement------------------------------+
| (1) |
+-LOCK TABLE statement-------------------------+
+-MERGE statement------------------------------+
+-OPEN statement-------------------------------+
| (1) |
+-PREPARE statement----------------------------+
+-REFRESH TABLE statement----------------------+
| (3) |
+-RELEASE statement----------------------------+
| (1) |
+-RELEASE SAVEPOINT statement------------------+
| (1) |
+-RENAME statement-----------------------------+
| (1) |
+-REVOKE statement-----------------------------+
| (3) |
+-ROLLBACK statement---------------------------+
| (1) |
+-SAVEPOINT statement--------------------------+
+-SELECT INTO statement------------------------+
| (3) |
+-SET CONNECTION statement---------------------+
+-SET special-register statement---------------+
+-TRUNCATE statement---------------------------+
+-UPDATE statement-----------------------------+
'-VALUES INTO statement------------------------'
Notes:
- The statement is not allowed in an
SQL-routine-body for an SQL function.
- A FETCH statement must not specify a
fetch-orientation clause, multiple-row-fetch
clause, the WITH CONTINUE or the CURRENT CONTINUE clauses.
- The COMMIT, ROLLBACK, CONNECT, RELEASE, and SET CONNECTION
statements must only be specified within the body of an SQL procedure. The COMMIT statement and the
ROLLBACK statement (without the TO SAVEPOINT clause) must not be issued in a routine body if the
routine is in the calling chain of an SQL routine, or an external routine.
Description
- SQL-control-statement
- Specifies an SQL statement that provides the capability to control
logic flow, declare and set variables, and handle warnings and exceptions,
as defined in this section. Control statements are supported in SQL
routines.
- SQL-statement
Specifies
an SQL statement. These statements are described in Statements.
Notes
Comments: Comments
can be included within the body of an SQL routine. 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 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, DB2® 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 can indicate that 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 to execute when an exception condition, a warning
condition, or a not found condition occurs in a compound statement.
The declaration of a condition handler includes the code that is executed
when the condition handler is activated. When a condition other than
a successful completion occurs in the processing of SQL-procedure-statement and
a condition handler that can 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, a not found condition,
or an exception condition and can 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 information from
the diagnostics area. See GET DIAGNOSTICS.
- Test the SQLSTATE and SQLCODE SQL variables.
If the condition is a warning and no handler
exists for the condition, the previous two methods can be used outside
of the body of a condition handler, if they are used immediately following
the statement for which the condition is wanted. If the condition
is an error and no handler exists for the condition, the routine terminates
with the error condition.