BEGIN END

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:

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.

BEGIN END

Syntax

Description

See Also