Variables in MySQL Stored Procedure 

• A variable is a namespace of data objects whose value can store, change value during the stored procedure execution.  
• It is hold immediate results.
• It is local to the stored procedure.
• Before using a variable, one must declare it.

 Declaring variables

Use the DECLARE  statement for declaring a variable

syntax:

DECLARE variable_name datatype(size) [DEFAULT default_value];

In this:

First, specify the variable name, after the DECLARE keyword.

The variable name must follow the naming rules of MySQL table column names.

Specify variable data type and length. (such as MySQL data types:- INT, VARCHAR , and DATETIME.)

Assign a variable a default value by the DEFAULT option.  (without specifying a default value, it is NULL.)

 

Example:-

DECLARE totalSale DEC(10,2) DEFAULT 0.0;

 

Declare two or more variables (share the same data type set default values to zero).

DECLARE x, y INT DEFAULT 0;

 

Assigning variables

To assign a variable a value, use the SET statement:

SET variable_name = value;

 

example:

DECLARE total INT DEFAULT 0;

SET total = 10;

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

SET the statement is also used with the SELECT INTO statement to assign the result of a query to a variable.

example:-

DECLARE productCount INT DEFAULT 0;

 SELECT COUNT(*) INTO productCount FROM products;

example:

declare a variable 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 declare a variable inside the block BEGIN END of the stored procedure, its scope till the END statement of stored procedure.

A variable whose name begins with the @ sign is a session variable.

It is available and accessible until the session ends.

example:

DELIMITER $$

 CREATE PROCEDURE GetTotalOrder()

BEGIN

               DECLARE totalOrder INT DEFAULT 0;

     SELECT COUNT(*) INTO totalOrder FROM orders;

    SELECT totalOrder;

END$$

 DELIMITER ;

 

Output: