A variable is a named data object whose value can change during the stored procedure execution. We typically use the variables in stored procedures to hold the immediate results. These variables are local to the stored procedure.
In addition to allowing you to create, access, and manipulate session variables from within a stored procedure, MySQL offers the DECLARE keyword, which can be used to declare variables that are “local” to a given routine.
CREATE PROCEDURE decl() BEGIN DECLARE count INT; END
A DECLARE statement must be followed by the variable name and its data type. The same rules that govern user-defined variable names also apply to variables in stored routines. Multiple variables of the same type can be initialized in a single DECLARE statement by separating the variable names with commas.
CREATE PROCEDURE decl() BEGIN DECLARE count, retval, x INT; END
The DECLARE statement also supports an optional DEFAULT keyword, which can be used to assign a default value to a variable.
CREATE PROCEDURE decl() BEGIN DECLARE count INT DEFAULT 0; END
Once defined, these variables can be assigned values using either SET or SELECT INTO statements, and can be accessed by name from other statements within the routine. Note that when accessing a local variable defined with DECLARE, there is no need to prefix the variable name with the @ symbol.
CREATE PROCEDURE add_one() BEGIN DECLARE count INT DEFAULT 99; SELECT (count+1); END CALL add_one();