转载自:http://www.mysqltutorial.org/variables-in-stored-procedures.aspx
MySQL Stored Procedure Variables
Summary: in this tutorial, you will learn about variables in the stored procedure, how to declare, and use variables. In addition, you will learn about the scopes of variables.
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. You must declare a variable before using it.
Declaring variables
To declare a variable inside a stored procedure, you use the DECLARE
statement as follows:
1
|
DECLARE variable_name datatype(size) DEFAULT default_value;
|
Let’s examine the statement above in more detail:
- First, you specify the variable name after the
DECLARE
keyword. The variable name must follow the naming rules of MySQL table column names. - Second, you specify the data type of the variable and its size. A variable can have any MySQL data types such as
INT
,VARCHAR
, andDATETIME
. - Third, when you declare a variable, its initial value is
NULL
. You can assign the variable a default value using theDEFAULT
keyword.
For example, we can declare a variable named total_sale
with the data type INT
and default value 0
as follows:
1
|
DECLARE total_sale INT DEFAULT 0;
|
MySQL allows you to declare two or more variables that share the same data type using a single DECLARE
statement as follows:
1
|
DECLARE x, y INT DEFAULT 0;
|
In this example, we declared two integer variables x
and y
, and set their default values to zero.
Assigning variables
Once you declared a variable, you can start using it. To assign a variable another value, you use the SET
statement, for example:
1
2
|
DECLARE total_count INT DEFAULT 0;
SET total_count = 10;
|
The value of the total_count
variable is 10
after the assignment.
Besides the SET
statement, you can use the SELECT INTO
statement to assign the result of a query, which returns a scalar value, to a variable. See the following example:
1
2
3
4
5
6
|
DECLARE total_products INT DEFAULT 0;
SELECT
COUNT(*) INTO total_products
FROM
products;
|
In the example above:
- First, we declared a variable named
total_products
and initialized its value to0
. - Then, we used the
SELECT INTO
statement to assign thetotal_products
variable the number of products that we selected from theproducts
table in the sample database.
Variables scope
A variable has its own scope that defines its lifetime. If you declare a variable inside a stored procedure, it will be out of scope when the END
statement of stored procedure reaches.
If you declare a variable inside BEGIN END
block, it will be out of scope if the END
is reached. You can declare two or more variables with the same name in different scopes because a variable is only effective in its own scope. However, declaring variables with the same name in different scopes is not good programming practice.
A variable whose name begins with the @
sign is a session variable. It is available and accessible until the session ends.
In this tutorial, we have shown you how to declare a variable inside stored procedures and discussed the variable scopes.