ound 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/https://mariadb.com/kb/en/begin-end/€