MySQL Stored Procedure Variables

 MySQL Stored Procedure Variables

Summary: in this tutorial, you will learn about variables in the stored procedure, and 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.

Declaring variables

To declare a variable inside a stored procedure, you use the DECLARE  a statement as follows:

DECLARE variable_name datatype(size) [DEFAULT default_value];

In this syntax:

• First, specify the name of the variable after the DECLARE keyword. 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 INT, VARCHAR , and DATETIME.
• Third, assign a variable a default value using the DEFAULT option.  If you declare a variable without specifying a default value, its value is NULL.

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  x and  y, and set their default values to zero.

DECLARE x, y INT DEFAULT 0;

Assigning variables

Once a variable is declared, it is ready to use. To assign a variable a value, you use the SET statement:

SET variable_name = value;

For example:

DECLARE total INT DEFAULT 0;
SET total = 10;

The value of the total variable is 10  after the assignment.

In addition to the SET the 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;

SELECT COUNT(*) 
INTO productCount
FROM products;

In this example:

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

Variable scopes

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:

DELIMITER $$

CREATE PROCEDURE GetTotalOrder()
BEGIN
	DECLARE totalOrder INT DEFAULT 0;
    
    SELECT COUNT(*) 
    INTO totalOrder
    FROM orders;
    
    SELECT totalOrder;
END$$

DELIMITER ;

How it works.

First, declare a variable totalOrder with a default value of zero. This variable will hold the number of orders from the orders table.

DECLARE totalOrder INT DEFAULT 0;

Second, use the SELECT INTO  statement to assign the variable totalOrder the number of orders selected from the orders table:

SELECT COUNT(*) 
INTO totalOrder 
FROM orders;

Third, select the value of the variable totalOrder.

SELECT totalOrder;

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 GetTotalOrder():

CALL GetTotalOrder();

Here is the output:

In this tutorial, you have learned how to declare and use variables inside stored procedures.