MySQL BEGIN ... END Compound and DECLARE Statement Syntax

BEGIN ... END Compound Statement Syntax

[begin_label:] BEGIN
 [statement_list]
END [end_label]

BEGIN ... END syntax is used for writing compound statements, which can appear within stored routines and triggers. A compound statement can contain multiple statements, enclosed by the BEGIN and END keywords


DECLARE Statement Syntax

DECLARE var_name[,...] type [DEFAULT value]

This statement is used to declare local variables. To provide a default value for the variable, include a DEFAULT clause. The value can be specified as an expression; it need not be a constant. If the DEFAULT clause is missing, the initial value is NULL.
The scope of a local variable is within the BEGIN ... END block where it is declared. The variable can be referred to in blocks nested within the declaring block, except those blocks that declare a variable with the same name.

Variable SET Statement

SET var_name = expr [, var_name = expr] ...

The SET statement in stored routines is an extended version of the general SET statement. Referenced variables may be ones declared inside a routine, or global system variables.

The SET statement in stored routines is implemented as part of the pre-existing SET syntax. This allows an extended syntax of SET a=x, b=y, ... where different variable types (locally declared variables and global and session server variables) can be mixed. This also allows combinations of local variables and some options that make sense only for system variables; in that case, the options are recognized but ignored.

SELECT ... INTO Statement

SELECT col_name[,...] INTO var_name[,...] table_expr 

This SELECT syntax stores selected columns directly into variables. Therefore, only a single row may be retrieved.

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
 

Important

SQL variable names should not be the same as column names. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable. For example, in the following statement, xname is interpreted as a reference to the xname variable rather than the xnamecolumn:

Ex
CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
 DECLARE xname VARCHAR(5) DEFAULT 'bob';
 DECLARE newname VARCHAR(5);
 DECLARE xid INT;

 SELECT xname,id INTO newname,xid
   FROM table1 WHERE xname = xname;
 SELECT newname;
  END;