[begin_label:] BEGIN [NOT ATOMIC] [statement_list] END [end_label]
NOT ATOMIC is required when used outside of a stored procedure. Inside stored procedures or within an anonymous block,
BEGIN alone starts a new anonymous block.
BEGIN ... END syntax is used for writing compound statements. A compound statement can contain multiple statements, enclosed by the
END keywords. statement_list represents a list of one or more statements, each
terminated by a semicolon (i.e.,
;) statement delimiter. statement_list is
optional, which means that the empty compound statement (
BEGIN END) is
END will perform a commit. If you are running in autocommit mode, every statement will be committed separately. If you are not running in
autocommit mode, you must execute a COMMIT or ROLLBACK after
END to get the database up to date.
Use of multiple statements requires that a client is able to send statement strings containing the ; statement delimiter. This is handled in the mysql command-line client with the DELIMITER command.
; end-of-statement delimiter (for example, to
; to be used in a program body.
BEGIN ... END constructs can be nested. Each block can define its own variables, a
HANDLER and a CURSOR, which don't exist in the outer blocks. The most local declarations override the outer objects which use the same name (see example below).
The declarations order is the following:
DECLARE HANDLER contains another
BEGIN ... END construct.
Here is an example of a very simple, anonymous block:
BEGIN NOT ATOMIC SET @a=1; CREATE TABLE test.t1(a INT); END|
Below is an example of nested blocks in a stored procedure:
CREATE PROCEDURE t( ) BEGIN DECLARE x TINYINT UNSIGNED DEFAULT 1; BEGIN DECLARE x CHAR(2) DEFAULT '02'; DECLARE y TINYINT UNSIGNED DEFAULT 10; SELECT x, y; END; SELECT x; END;
In this example, a TINYINT variable,
x is declared in the outter block. But in the inner block
x is re-declared as a CHAR and an
y variable is declared. The inner SELECT shows the "new" value of
x, and the value of
y. But when x is selected in the outer block, the "old" value is returned. The final SELECT doesn't try to read
y, because it doesn't exist in that context.