Group multiple SQL statements into a logical block. This construct defines a compound statement, creating a new scope for variables and exception handling.
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 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 mode, every statement will be committed separately. If you are not running in autocommit mode, you must execute a or 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 with the command.
Changing the ; end-of-statement delimiter (for example, to//) allows ; to be used in a program body.
A compound statement within a can be . 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 , 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:
Below is an example of nested blocks in a stored procedure:
In this example, a variable, x is declared in the outter block. But in the inner block x is re-declared as a and an y variable is declared. The inner 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 doesn't try to read y, because it doesn't exist in that context.
This page is licensed: GPLv2, originally from
[begin_label:] BEGIN [NOT ATOMIC]
[statement_list]
END [end_label]BEGIN NOT ATOMIC
SET @a=1;
CREATE TABLE test.t1(a INT);
END|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;