- 18.2 Stored Routine Syntax
- 18.2.1 CREATE PROCEDURE and CREATE FUNCTION Syntax
- 18.2.2 ALTER PROCEDURE and ALTER FUNCTION Syntax
- 18.2.3 DROP PROCEDURE and DROP FUNCTION Syntax
- 18.2.4 CALL Statement Syntax
- 18.2.5 BEGIN ... END Compound Statement Syntax
- 18.2.6 DECLARE Statement Syntax
- 18.2.7 Variables in Stored Routines
- 18.2.8 Conditions and Handlers
- 18.2.9 Cursors
- 18.2.10 Flow Control Constructs
The DECLARE statement is used to define
various items local to a routine:
Local variables. See Section 18.2.7, “Variables in Stored Routines”.
Conditions and handlers. See Section 18.2.8, “Conditions and Handlers”.
Cursors. See Section 18.2.9, “Cursors”.
The SIGNAL and RESIGNAL
statements are not currently supported.
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.