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. You typically use variables in stored procedures to hold immediate results. These variables are local to the stored procedure.
Before using a variable, you must declare it.
To declare a variable inside a stored procedure, you use the
DECLARE statement as follows:
DECLARE variable_name datatype(size) [DEFAULT default_value];
In this syntax:
- First, specify the name of the variable after the
DECLAREkeyword. The variable name must follow the naming rules of MySQL table column names.
- Second, specify the data type and length of the variable. A variable can have any MySQL data types such as
- Third, assign a variable a default value using the
DEFAULToption. If you declare a variable without specifying a default value, its value is
The following example declares a variable named
totalSale with the data type
DEC(10,2) and default value
0.0 as follows:
DECLARE totalSale DEC(10,2) DEFAULT 0.0;
MySQL allows you to declare two or more variables that share the same data type using a single
DECLARE statement. The following example declares two integer variables
y, and set their default values to zero.
DECLARE x, y INT DEFAULT 0;
Once a variable is declared, it is ready to use. To assign a variable a value, you use the
SET variable_name = value;
DECLARE total INT DEFAULT 0;
SET total = 10;
The value of the
total variable is
10 after the assignment.
In addition to the
SET statement, you can use the
SELECT INTO statement to assign the result of a query to a variable as shown in the following example:
DECLARE productCount INT DEFAULT 0;
In this example:
- First, declare a variable named
productCountand initialize its value to
- Then, use the
SELECT INTOstatement to assign the
productCountvariable the number of products selected from the
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.
When you declare a variable inside the block
BEGIN END, it will be out of scope if the
END is reached.
MySQL allows you to declare two or more variables that share the same name in different scopes. Because a variable is only effective in its 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.
Putting it all together
The following example illustrates how to declare and use a variable in a stored procedure:
CREATE PROCEDURE GetTotalOrder()
DECLARE totalOrder INT DEFAULT 0;
How it works.
First, declare a variable
totalOrder with a default value of zero. This variable will hold the number of orders from the
DECLARE totalOrder INT DEFAULT 0;
Second, use the
SELECT INTO statement to assign the variable
totalOrder the number of orders selected from the
Third, select the value of the variable
Note that you will learn how to use variables practically in the subsequent tutorials. The example in this tutorial is just an illustration so that you understand the concept.
This statement calls the stored procedure
Here is the output:
In this tutorial, you have learned how you how to declare and use variables inside stored procedures.