MySQL Stored Procedure Variables

Summary: in this tutorial, you will learn about variables in 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 you can use 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.
• 首先，必须在DECLARE关键字后指定变量名。变量名必须遵从mysql表中列名的名字规则
  
• Second, you specify the data type of the variable and its size. A variable can have any MySQL data types such as INT, VARCHAR, DATETIME, etc.
• 然后，指定变量类型和大小，一个变量可以是mysql数据类型中的任一类型如int、varchar、datetime等
  
• Third, when you declare a variable, its initial value is NULL. You can assign the variable a default value by using DEFAULT keyword.
• 最后，当声明变量后，初始值为null，可以使用default关键字指定默认值。
  

For example, we can declare a variable named  total_sale with the data type INT and default value0 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 singleDECLARE statement as following:

mysql允许同一类型的多个变量一起声明。

1	DECLARE x , y INT DEFAULT 0

We declared two INT 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 theSET statement, for example:

使用set声明 为变量赋值：

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 SELECT INTO statement to assign the result of a query to a variable. Notice that the query must return a scalar value.

除了使用set声明，还可以使用select into声明将查询结果赋值给变量。注意的是查询必须返回数量值。

1 2 3 4

DECLARE total_products INT DEFAULT 0   SELECT COUNT ( * ) INTO total_products FROM products

In the example above:

• First, we declare a variable named total_products and initialize its value to 0.
• Then, we used the SELECT INTO statement to assign the total_products variable the number of products that we selected from the products from the products table.

Variables scope

A variable has its own scope, which defines its life time. If you declare a variable inside a stored procedure, it will be out of scope when the END statement of stored procedure reached.

变量有自己的范围，如果在存储过程中定义一个变量，它将在存储过程end声明后失效。

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.

如果在BEGIN END块内声明变量，那么至end之间为其范围。在不同的范围内，可以命名相同名字的变量。当然，这不是个好的编程习惯。

A variable that begins with the @ sign at the beginning is 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 about the variable scopes.

Related Tutorials

• Getting Started with MySQL Stored Procedures
• Hints for Choosing Between IF and CASE Statements
• Loop in Stored Procedures
• Introduction to MySQL Stored Procedures
• MySQL Cursor
• Listing Stored Procedures in a MySQL Database

Share this:

•