602SQL Documentation Index  

Block Statement

block_statement ::= [ label : ] BEGIN [ [ NOT ] ATOMIC ]
[ variables_declaration  ]
[ routine_declaration  ]
[ exception_declaration  ]
[ table_declaration  ]
[ cursor_declaration ]
[ handle_declaration ]
[ statement ]
END [ label ];

The block statement is composed of one or more SQL statements. It starts with the BEGIN keyword and is terminated with the END keyword. You may declare local objects in the block statement (such as variables, routines, cursors, tables), that will exist only when the block statement is being executed and can be used in the statements inside the block statement (including any nested block statements). If a procedure is called inside a block statement, its local objects won't be visible.

Local variables are created when the block statement is executed. Then the statements inside the block statement are executed in turns, unless an error occurs or the LEAVE statement is executed. All open local cursors are closed when the block statement is terminated, as well as all local variables, temporary tables and savepoints created inside the block statement.

The ATOMIC clause designates an atomic block statement, which means that it's executed as a whole and may be rolled back as a whole in the case an error occurs. If neither ATOMIC nor NOT ATOMIC keywords are set, the block statement is not atomic. The transaction statements COMMIT or ROLLBACK (except for the ROLLBACK TO SAVEPOINT to a savepoint created in the atomic statement block) may not be executed in (or even be a part of) that block statement. The REDO or UNDO type handler declaration may be used only in the atomic block statement.

If the ending label is specified, it has to be the same as the beginning label. No statement may be designated with the same label inside the block statement.

You may not specify two identical variables names, routine names, exception names or cursor names in declarations made in the block statement.

Variances from SQL Standard