13.6.4 Variables in Stored Programs
System variables and user-defined variables can be used in stored programs, just as they can be used outside stored-program context. In addition, stored programs can use
DECLARE to define local variables, and stored routines (procedures and functions) can be declared to take parameters that communicate values between the routine and its caller.
To declare local variables, use the
DECLAREstatement, as described in Section 22.214.171.124, “Local Variable DECLARE Statement”.
Variables can be set directly with the
SETstatement. See Section 126.96.36.199, “SET Syntax for Variable Assignment”.
Results from queries can be retrieved into local variables using
SELECT ... INTOor by opening a cursor and using
FETCH ... INTO. See Section 188.8.131.52, “SELECT ... INTO Statement”, and Section 13.6.6, “Cursors”.
For information about the scope of local variables and how MySQL resolves ambiguous names, see Section 184.108.40.206, “Local Variable Scope and Resolution”.
It is not permitted to assign the value
DEFAULT to stored procedure or function parameters or stored program local variables (for example with a
SET statement). In MySQL 5.7, this results in a syntax error.
var_name = DEFAULT