MySQL 5.4 Reference Manual :: 12 SQL Statement Syntax :: 12.7 MySQL Compound-Statement Syntax :: 12.7.2 DECLARE Syntax
The DECLARE statement is used to
define various items local to a program:
Local variables. See Section 12.7.3, “Variables in Stored Programs”.
Conditions and handlers. See Section 12.7.4, “Conditions and Handlers”.
Cursors. See Section 12.7.5, “Cursors”.
DECLARE is allowed only inside a
BEGIN ... END
compound statement and must be at its start, before any other
statements.
Declarations must follow a certain order. Cursors must be declared before declaring handlers, and variables and conditions must be declared before declaring either cursors or handlers.
User Comments
CREATE PROCEDURE p8 ()
BEGIN DECLARE a INT;
DECLARE b INT; SET a = 5;
SET b = 5; INSERT INTO t VALUES (a);
SELECT s1 * a FROM t WHERE s1 >= b;
END;
You don't really define variables within the stored procedure. You define them within the BEGIN/END block.
You must declare them explicitly at the start of the BEGIN/END block, along with their data types. Once you've declared a variable, you can use it anywhere that you would otherwise use any session variable, or literal, or column name.
Add your own comment.