In this tutorial, we will learn about the variables in MySQL stored procedures. This is going to be a detailed guide on the variables in the stored procedure where you will learn what are local and session variables, how to declare and use them inside the stored procedure. Along with this, you will also learn what is the scope of variables. So, let’s get started with the introduction!
Also read: Create MySQL Stored Procedure [With Examples]
Introduction to Variables in Stored Procedures
By the definition, variables are named object that holds a particular value and that value can be changed throughout its life. Variables in MySQL stored procedure acts similar to variables in other programming languages such as C, C++, and Java.
We can use the variables in the stored procedure to hold the input parameters, values inside the block and the immediate results. In the stored procedure, variables have local scope. Before using any variable inside the stored procedure, you must define it.
Now let’s see how to declare a variable inside the stored procedure.
How to Declare Variables in Stored Procedures
In MySQL, we use the DECLARE keyword to specify a variable. Following is the syntax to declare a variable-
DECLARE variable_name datatype(size) [DEFAULT default_value];Code language: SQL (Structured Query Language) (sql)Where,
- DECLARE – It declares the variable.
- variable_name – You can choose any name as a variable that fulfills the conditions.
- datatype – It defines the datatype of a variable that you will use throughout the block of code. Depending on the datatype, you must specify the size of a variable.
- DEFAULT default_value – If you want to assign a default value to the variable, you can specify using the DEFAULT option. It is optional and you can set the value to variable without the DEFAULT option using the SET statement.
You can declare a local variable only inside the BEGIN-END block unlike the session variable. Session variable can be declared outside the BEGIN-END block too.
For example,
DECLARE website VARCHAR(100) DEFAULT "mysqlcode.com";Code language: SQL (Structured Query Language) (sql)The above statement will declare a variable of name “website” and assign a default value to it as “mysqlcode.com”.
Besides, you can declare more than one variable of similar datatype in the same line by the following method-
DECLARE var1, var2 datatype(size) [DEFAULT default_value];Code language: SQL (Structured Query Language) (sql)For Example,
DECLARE no1, no2 INT DEFAULT 10;Code language: SQL (Structured Query Language) (sql)The above statement will declare two variables of type INT and assign 10 as a default value to them.
How to Assign Value to Variables?
As soon as you declare a variable, you can assign a value to it. Even if you assign a default value to the variable, you can change it and assign a new value using the SET statement. following is the syntax to assign a value to the variable.
SET variable_name = value;Code language: SQL (Structured Query Language) (sql)For example,
DECLARE website VARCHAR(100) DEFAULT "dotcom";
SET website = "mysqlcode.com";Code language: SQL (Structured Query Language) (sql)In the above example, the variable “website” will hold a default value “dotcom” before assigning it a value using the SET statement. As soon as we use the SET statement, the value of the “website” variable will change to “mysqlcode.com”.
Along with the SET statement, you can use the SELECT INTO statement to copy the value into the variable. Check the below example-
DECLARE website VARCHAR(100);
SELECT website_name INTO website
FROM websites_data
WHERE website_id = 1;Code language: SQL (Structured Query Language) (sql)In the above example, we will fetch the website_name of id 1 using the SELECT statement from the table “website_data” and copy it into the “website” variable.
Scope of a Variable in Stored Procedures
In simple terms, scope means the lifetime of a variable or how long the variable can be used throughout the program/block of code.
when you declare a variable in the stored procedure, it works as a local variable because you can’t use it outside the procedure, unlike the session variables. The session variables last throughout the sessions.
When you declare a variable inside the BEGIN END block, the variable will not work after the END statement.
The session variables are declared using @ sign. Note that, the session variables are declared without the DECLARE statement and directly values are assigned to them. Check the below example-
SET @website = "mysqlcode.com";
SELECT @website;Code language: SQL (Structured Query Language) (sql)
As you can see, we don’t need the DECLARE statement to declare a session variable and it works as long as the session is active.
The session variables are useful when the OUT parameters are used in the stored procedures.
Example of MySQL Stored Procedure Variables
We have seen the syntax and basic examples of variables in the stored procedure. Let’s put them all together in the stored procedure in the below example-
A stored procedure to print 0 to 9 digits.
DELIMITER //
CREATE PROCEDURE proc_demo()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE output VARCHAR(100) DEFAULT '';
WHILE i < 10 DO
SET output = CONCAT(output, i , ',');
SET i = i + 1;
END WHILE;
SELECT output;
END //Code language: SQL (Structured Query Language) (sql)
In the above example, we have declared two variables i and output and assigned them default values 0 and ‘ ‘ respectively.
Then we used a while loop to increment the value of i from 0 to 9. We used the CONCAT function to concatenate the previous output and the new digit which finally makes a string of 0 to 9 digits.
We set the value of i to i+1 every time until i becomes 10 and the loop terminates.
In the end, we use the SELECT statement to print the result.
When we call the above procedure, we get the following output-
call proc_demo();Code language: SQL (Structured Query Language) (sql)
As you can see, we have got the expected result of a string containing digits from 0 to 9.
Conclusion
In this tutorial, we learned about the variables in the stored procedure. Although, this is a sub-topic of MySQL variables and this tutorial is mainly focused on local variables in stored procedure instead of all types of variables in MySQL. If you want to learn more about the variables in MySQL, you can search it using the search bar on our website and find the tutorial. Stay tuned with us for more informative and interesting tutorials!