id="sqlprocstmt">SQL-procedure-statement

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

                                                   (1)
>>-+-SQL-control-statement-----------------------+-------------><
   +-ALLOCATE DESCRIPTOR-statement---------------+
   +-ALTER PROCEDURE (External)-statement--------+
   +-ALTER PROCEDURE (SQL)-statement-------------+
   +-ALTER SEQUENCE-statement--------------------+
   +-ALTER TABLE-statement-----------------------+
   +-CLOSE-statement-----------------------------+
   +-COMMENT-statement---------------------------+
   +-COMMIT-statement----------------------------+
   +-CONNECT-statement---------------------------+
   +-CREATE ALIAS-statement----------------------+
   +-CREATE DISTINCT TYPE-statement--------------+
   +-CREATE FUNCTION (External Scalar)-statement-+
   +-CREATE FUNCTION (External Table)-statement--+
   +-CREATE FUNCTION (Sourced)-statement---------+
   +-CREATE INDEX-statement----------------------+
   +-CREATE PROCEDURE (External)-statement-------+
   +-CREATE SCHEMA-statement---------------------+
   +-CREATE SEQUENCE-statement-------------------+
   +-CREATE TABLE-statement----------------------+
   +-CREATE VIEW-statement-----------------------+
   +-DEALLOCATE DESCRIPTOR-statement-------------+
   +-DECLARE GLOBAL TEMPORARY TABLE-statement----+
   +-DELETE-statement----------------------------+
   +-DESCRIBE-statement--------------------------+
   +-DESCRIBE INPUT-statement--------------------+
   +-DESCRIBE TABLE-statement--------------------+
   +-DISCONNECT-statement------------------------+
   +-DROP-statement------------------------------+
   +-EXECUTE-statement---------------------------+
   +-EXECUTE IMMEDIATE-statement-----------------+
   +-FETCH-statement-----------------------------+
   +-GET DESCRIPTOR-statement--------------------+
   +-GRANT-statement-----------------------------+
   +-INSERT-statement----------------------------+
   +-LABEL-statement-----------------------------+
   +-LOCK TABLE-statement------------------------+
   +-OPEN-statement------------------------------+
   +-PREPARE-statement---------------------------+
   +-REFRESH TABLE-statement---------------------+
   +-RELEASE-statement---------------------------+
   +-RELEASE SAVEPOINT-statement-----------------+
   +-RENAME-statement----------------------------+
   +-REVOKE-statement----------------------------+
   +-ROLLBACK-statement--------------------------+
   +-SAVEPOINT-statement-------------------------+
   +-SELECT INTO-statement-----------------------+
   +-SET CONNECTION-statement--------------------+
   +-SET CURRENT DEBUG MODE-statement------------+
   +-SET CURRENT DEGREE-statement----------------+
   +-SET DESCRIPTOR-statement--------------------+
   +-SET ENCRYPTION PASSWORD-statement-----------+
   +-SET PATH-statement--------------------------+
   +-SET RESULT SETS-statement-------------------+
   +-SET SCHEMA-statement------------------------+
   +-SET TRANSACTION-statement-------------------+
   +-UPDATE-statement----------------------------+
   '-VALUES INTO-statement-----------------------'
 

Notes:

1. COMMIT, ROLLBACK, CONNECT, DISCONNECT, SET CONNECTION, and SET RESULT SETS statements are only allowed in SQL procedures. The SET TRANSACTION statement is allowed in SQL procedures and triggers. ALTER PROCEDURE (SQL) with a REPLACE keyword is not allowed in an SQL-routine-body.

 

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.

Labels: Labels can be specified on most SQL procedure statements. If a label is specified on an SQL procedure statement, it must be unique from other labels within the same scope.

• The label must not be the same as any other label within the same compound-statement.

• The label must not be the same as a label specified on the compound-statement itself.

• If the compound-statement is nested within another compound-statement, the label must not be the same as the label specified on any higher level compound-statement.

• The label must not be the same as the name of the SQL function, SQL procedure, or SQL trigger that contains the SQL procedure statement.

Handling errors and warnings: Conditions can be detected within an SQL procedure by the following methods:

• testing the special SQL variables SQLSTATE and SQLCODE.

• issuing a GET DIAGNOSTICS statement to request the condition information. See GET DIAGNOSTICS statement.

• defining condition handlers to detect and process conditions. See compound-statement.

---

[ Top of Page | Previous Page | Next Page | Contents |
Index ]