Syntax
------ 
[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.
 
Description
----------- 
BEGIN ... END syntax is used for writing compound
statements. A compound statement can contain multiple
statements, enclosed by the BEGIN and 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
legal.
 
Note that 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.
Changing the ; end-of-statement delimiter (for example, to
//) allows ; to be used in a program body.
 
A compound statement within a stored program can be
labeled. end_label cannot be given unless begin_label also
is present. If both are present, they must be the same.
 
BEGIN ... END constructs can be nested. Each block can
define its own variables, a CONDITION, 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 local variables;
DECLARE CONDITIONs;
DECLARE CURSORs;
DECLARE HANDLERs;
 
Note that 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.
 


URL: https://mariadb.com/kb/en/begin-end/