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”.
The SIGNAL and
RESIGNAL statements are not
supported until MySQL 5.5.
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.