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.
Local variables are within scope only during stored routine
execution, so references to them are disallowed within prepared
statements because those are global to the current session and
the variables might have gone out of scope when the statement is
executed. For example, SELECT ... INTO
cannot be used as
a prepared statement.
local_var
Local 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
xname
column:
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;
When this procedure is called, the newname
variable returns the value 'bob'
regardless
of the value of the table1.xname
column.
See also Section D.1, “Restrictions on Stored Routines, Triggers, and Events”.
User Comments
Add your own comment.