Syntax

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>-CREATE PROCEDURE--procedure-name----------------------------->
 
>--+-----------------------------------------+------------------>
   '-(--+-------------------------------+--)-'
        | .-,-------------------------. |
        | V                           | |
        '-----parameter-declaration---+-'
 
>--LANGUAGE SQL--option-list--+----------------------+--SQL-routine-body-><
                              '-SET OPTION-statement-'
 
Read syntax diagramSkip visual syntax diagramparameter-declaration:
 
   .-IN----.
|--+-------+--parameter-name--data-type-------------------------|
   +-OUT---+
   '-INOUT-'
 
data-type:
 
|--+-built-in-type------+---------------------------------------|
   '-distinct-type-name-'
 
Read syntax diagramSkip visual syntax diagramoption-list:
 
   .-NOT DETERMINISTIC-. (1)   .-MODIFIES SQL DATA-.
|--+-------------------+-------+-------------------+------------>
   '-DETERMINISTIC-----'       +-READS SQL DATA----+
                               '-CONTAINS SQL------'
 
   .-CALLED ON NULL INPUT-.  .-INHERIT SPECIAL REGISTERS-.
>--+----------------------+--+---------------------------+------>
 
   .-DYNAMIC RESULT SETS--0-------.
>--+------------------------------+----------------------------->
   '-DYNAMIC RESULT SETS--integer-'
 
>--+-------------------------+--+---------------------+--------->
   '-SPECIFIC--specific-name-'  +-DISALLOW DEBUG MODE-+
                                +-ALLOW DEBUG MODE----+
                                '-DISABLE DEBUG MODE--'
 
   .-FENCED-----.  .-OLD SAVEPOINT LEVEL-.
>--+------------+--+---------------------+---------------------->
   '-NOT FENCED-'  '-NEW SAVEPOINT LEVEL-'
 
   .-COMMIT ON RETURN NO--.
>--+----------------------+-------------------------------------|
   '-COMMIT ON RETURN YES-'
 
Notes:
  1. The optional clauses can be specified in a different order.
Read syntax diagramSkip visual syntax diagramSQL-routine-body:
 
|--+-SQL-control-statement-----------------------+--------------|
   +-ALLOCATE DESCRIPTOR-statement---------------+
   +-ALTER PROCEDURE (External)-statement--------+
   +-ALTER PROCEDURE (SQL)-statement-------------+
   +-ALTER SEQUENCE-statement--------------------+
   +-ALTER TABLE-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 IMMEDIATE-statement-----------------+
   +-GET DESCRIPTOR-statement--------------------+
   +-GRANT-statement-----------------------------+
   +-INSERT-statement----------------------------+
   +-LABEL-statement-----------------------------+
   +-LOCK TABLE-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-----------------------'
 
Read syntax diagramSkip visual syntax diagrambuilt-in-type:
 
|--+-+---SMALLINT---+--------------------------------------------------------------------------+--|
   | +-+-INTEGER-+--+                                                                          |
   | | '-INT-----'  |                                                                          |
   | '---BIGINT-----'                                                                          |
   |                  .-(5,0)------------------------.                                         |
   +-+-+-DECIMAL-+-+--+------------------------------+-----------------------------------------+
   | | '-DEC-----' |  |             .-,0--------.    |                                         |
   | '-NUMERIC-----'  '-(--integer--+-----------+--)-'                                         |
   |                                '-, integer-'                                              |
   |          .-(--53--)------.                                                                |
   +-+-FLOAT--+---------------+-+--------------------------------------------------------------+
   | |        '-(--integer--)-' |                                                              |
   | +-REAL---------------------+                                                              |
   | |         .-PRECISION-.    |                                                              |
   | '-DOUBLE--+-----------+----'                                                              |
   |                    .-(--1--)-------.                                                      |
   +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+-+
   | | | '-CHAR------'  '-(--integer--)-'          |  +-FOR BIT DATA---+                     | |
   | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-'  +-FOR SBCS DATA--+                     | |
   | |   | '-CHAR------'          |                   +-FOR MIXED DATA-+                     | |
   | |   '-VARCHAR----------------'                   '-ccsid-clause---'                     | |
   | |                                          .-(--1M--)-------------.                     | |
   | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+----------------+-' |
   |       | '-CHAR------'               |      '-(--integer--+---+--)-'  +-FOR SBCS DATA--+   |
   |       '-CLOB------------------------'                    +-K-+       +-FOR MIXED DATA-+   |
   |                                                          +-M-+       '-ccsid-clause---'   |
   |                                                          '-G-'                            |
   |                .-(--1--)-------.                                                          |
   +-+---GRAPHIC----+---------------+-------+--+--------------+--------------------------------+
   | |              '-(--integer--)-'       |  '-ccsid-clause-'                                |
   | +-+-GRAPHIC VARYING-+--(--integer--)---+                                                  |
   | | '-VARGRAPHIC------'                  |                                                  |
   | |             .-(--1M--)-------------. |                                                  |
   | '---DBCLOB----+----------------------+-'                                                  |
   |               '-(--integer--+---+--)-'                                                    |
   |                             +-K-+                                                         |
   |                             +-M-+                                                         |
   |                             '-G-'                                                         |
   |             .-(--1--)-------.                                                             |
   +-+-+-BINARY--+---------------+---------+-----------------+---------------------------------+
   | | |         '-(--integer--)-'         |                 |                                 |
   | | '-+-BINARY VARYING-+--(--integer--)-'                 |                                 |
   | |   '-VARBINARY------'                                  |                                 |
   | |                              .-(--1M--)-------------. |                                 |
   | '---+-BLOB----------------+----+----------------------+-'                                 |
   |     '-BINARY LARGE OBJECT-'    '-(--integer--+---+--)-'                                   |
   |                                              +-K-+                                        |
   |                                              +-M-+                                        |
   |                                              '-G-'                                        |
   +-+-DATE-------------------+----------------------------------------------------------------+
   | |       .-(--0--)-.      |                                                                |
   | +-TIME--+---------+------+                                                                |
   | |            .-(--6--)-. |                                                                |
   | '-TIMESTAMP--+---------+-'                                                                |
   |             .-(--200--)-----.                                                             |
   +---DATALINK--+---------------+--+--------------+-------------------------------------------+
   |             '-(--integer--)-'  '-ccsid-clause-'                                           |
   '---ROWID-----------------------------------------------------------------------------------'
 
ccsid-clause:
 
                   .-NOT NORMALIZED-.
|--CCSID--integer--+----------------+---------------------------|
                   '-NORMALIZED-----'