Syntax

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram                      .-NOT ATOMIC-.
>>-+--------+--BEGIN--+------------+---------------------------->
   '-label:-'         '-ATOMIC-----'
 
>--+--------------------------------------+--------------------->
   | .----------------------------------. |
   | V                                  | |
   '---+-SQL-variable-declaration-+-- ;-+-'
       +-condition-declaration----+
       '-return-codes-declaration-'
 
>--+----------------------------------+------------------------->
   | .------------------------------. |
   | V                              | |
   '---DECLARE CURSOR-statement-- ;-+-'
 
>--+-----------------------------+------------------------------>
   | .-------------------------. |
   | V                         | |
   '---handler-declaration-- ;-+-'
 
   .---------------------------------.
   V                                 |
>----+-----------------------------+-+--END--+-------+---------><
     '-SQL-procedure-statement-- ;-'         '-label-'
 
SQL-variable-declaration:
 
            .-,-----------------.
            V                   |
|--DECLARE----SQL-variable-name-+------------------------------->
 
              .-DEFAULT NULL-------------------------.
>--data-type--+--------------------------------------+----------|
              '-DEFAULT--constant--+---------------+-'
                                   |          (1)  |
                                   '-NOT NULL------'
 
condition-declaration:
 
|--DECLARE--SQL-condition-name---------------------------------->
 
                               .-VALUE-.
                   .-SQLSTATE--+-------+-.
>--CONDITION--FOR--+---------------------+--string-constant-----|
 
return-codes-declaration:
 
|--DECLARE------------------------------------------------------>
 
                                 .-DEFAULT--'00000'---------.
>--+-SQLSTATE--+-CHARACTER(5)-+--+--------------------------+-+--|
   |           '-CHAR(5)------'  '-DEFAULT--string-constant-' |
   |                       .-DEFAULT--0----------------.      |
   '-SQLCODE--+-INTEGER-+--+---------------------------+------'
              '-INT-----'  '-DEFAULT--integer-constant-'
 
handler-declaration:
 
|--DECLARE--+-CONTINUE-+--HANDLER FOR--------------------------->
            +-EXIT-----+
            '-UNDO-----'
 
                                (2)
>--+-specific-condition-value-+-------SQL-procedure-statement---|
   '-general-condition-value--'
 
specific-condition-value:
 
   .-,-------------------------------.
   V             .-VALUE-.           |
|----+-SQLSTATE--+-------+--string-+-+--------------------------|
     '-condition-name--------------'
 
general-condition-value:
 
|--+-SQLEXCEPTION-+---------------------------------------------|
   +-SQLWARNING---+
   '-NOT FOUND----'
 
data-type:
 
|--+-built-in-type------+---------------------------------------|
   '-distinct-type-name-'
 
Notes:
  1. The DEFAULT and NOT NULL clauses can be specified in either order.
  2. specific-condition-value and general-condition-value cannot be specified in the same handler declaration.
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--integer-'            | |
   | |                                 .-(--1M--)-------------.                     | |
   | '---+-CLOB-------------------+----+----------------------+--+----------------+-' |
   |     +-CHAR LARGE OBJECT------+    '-(--integer--+---+--)-'  +-FOR SBCS DATA--+   |
   |     '-CHARACTER LARGE OBJECT-'                  +-K-+       +-FOR MIXED DATA-+   |
   |                                                 +-M-+       '-CCSID--integer-'   |
   |                                                 '-G-'                            |
   |                .-(--1--)-------.                                                 |
   +-+---GRAPHIC----+---------------+-------+--+----------------+---------------------+
   | |              '-(--integer--)-'       |  '-CCSID--integer-'                     |
   | +-+-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------+--------------------------------------------------------------------+
   | +-TIME------+                                                                    |
   | '-TIMESTAMP-'                                                                    |
   |             .-(--200--)-----.                                                    |
   +---DATALINK--+---------------+--+----------------+--------------------------------+
   |             '-(--integer--)-'  '-CCSID--integer-'                                |
   '---ROWID--------------------------------------------------------------------------'