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
Read syntax diagramSkip visual syntax diagramSQL-control-statementALLOCATE CURSOR-statementALLOCATE DESCRIPTOR-statementALTER FUNCTION-statement(2)ALTER MASK-statementALTER PERMISSION-statementALTER PROCEDURE-statement(2)ALTER SEQUENCE-statementALTER TABLE-statementALTER TRIGGER-statementASSOCIATE LOCATORS-statementCLOSE-statementCOMMENT-statementCOMMIT-statement(1)CONNECT-statement(1)CREATE ALIAS-statementCREATE FUNCTION (external scalar)-statementCREATE FUNCTION (external table)-statementCREATE FUNCTION (sourced)-statementCREATE INDEX-statementCREATE MASK-statementCREATE PERMISSION-statementCREATE PROCEDURE (external)-statementCREATE SCHEMA-statementCREATE SEQUENCE-statementCREATE TABLE-statementCREATE TYPE-statementCREATE VIEW-statementDEALLOCATE DESCRIPTOR-statementDECLARE GLOBAL TEMPORARY TABLE-statementDELETE-statementDESCRIBE-statementDESCRIBE CURSOR-statementDESCRIBE INPUT-statementDESCRIBE PROCEDURE-statementDESCRIBE TABLE-statementDISCONNECT-statement(1)DROP-statementEXECUTE-statementEXECUTE IMMEDIATE-statementFETCH-statementGET DESCRIPTOR-statementGRANT-statementINCLUDE-statementINSERT-statementLABEL-statementLOCK TABLE-statementMERGE-statement
Syntax (continued)
Read syntax diagramSkip visual syntax diagramOPEN-statementPREPARE-statementREFRESH TABLE-statementRELEASE-statementRELEASE SAVEPOINT-statementRENAME-statementREVOKE-statementROLLBACK-statement(1)SAVEPOINT-statementSELECT INTO-statementSET CONNECTION-statement(1)SET CURRENT DEBUG MODE-statementSET CURRENT DECFLOAT ROUNDING MODE-statementSET CURRENT DEGREE-statementSET CURRENT IMPLICIT XMLPARSE OPTION-statementSET DESCRIPTOR-statementSET ENCRYPTION PASSWORD-statementSET PATH-statementSET RESULT SETS-statement(1)SET SCHEMA-statementSET TRANSACTION-statement(3)SET transition-variable-statement(4)TRANSFER OWNERSHIP-statementTRUNCATE-statementUPDATE-statementVALUES INTO-statement
Notes:
  1. A COMMIT, ROLLBACK, CONNECT, DISCONNECT, SET CONNECTION, or SET RESULT SETS statement is only allowed in an SQL procedure.
  2. 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.
  3. A SET TRANSACTION statement is only allowed in an SQL function or trigger.
  4. 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.