[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
statement_list represents a list of one or more statements, each
terminated by a semicolon (
;) 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. See Delimiters in the mysql client for more.
BEGIN ... END constructs can be nested. Each block can define its own variables,
HANDLERs and CURSORs, 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.
Very simple anonymous block:
BEGIN NOT ATOMIC SET @a=1; CREATE TABLE test.t1(a int); END|
Example of nested blocks:
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 x variable is declared in the outmost 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.