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 (;) statement delimiter. statement_list is optional, which means that the empty compound statement (BEGIN END) is legal.

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. See Delimiters in the mysql client for more.

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, CONDITIONs, 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 local variables;
• DECLARE CONDITIONs;
• DECLARE HANDLERs;
• DECLARE CURSORs;

Note that DECLARE HANDLER contains another BEGIN ... END construct.

Examples:

Very simple anonymous block:

BEGIN NOT ATOMIC
SET @a=1;
CREATE TABLE test.t1(a int);
END|
<</code>

Example of nested blocks:
<<code>>
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.

See also

• Using compound statements outside of stored programs