MySQL - Variables in Stored Programs

---

---

You can create user-defined variables or system variables with in Procedures. You can define a variable with in a stored program using the DECLARE statement. You can use these variables outside the stored-program context. These stored programs can also accept parameters.

Declaring the local variables

You can declare a local variable using the DECLARE statement. The value of a variable can be a constant.

Syntax

Following the syntax to do declare a local variable −

DECLARE var_name type [DEFAULT value]

Where, var_name is the name of the variable and type is the datatype of the variable.

Example

Following is an example of declaring local variables in a procedure −

DELIMITER //
CREATE PROCEDURE RepeatExample()
BEGIN
   DECLARE val INT;
   DECLARE squares INT;
   DECLARE res VARCHAR(100);
   SET val=1;
   SET squares=1;
   SET res = '';
   REPEAT
      SET squares = val*val;
      SET res = CONCAT(res, squares,',');
      SET val = val + 1;
   UNTIL val >= 10
   END REPEAT;
   SELECT res;
END// 
DELIMITER ;

You can call the above procedure as follows −

CALL RepeatExample;

Output

Following is the output of the above program −

res
1,4,9,16,25,36,49,64,81,

Accepting values as parameters

The procedures and functions in MySQL accept parameters and you can pass values to these while calling them.

Example

Following query creates a function that accepts parameters −

DELIMITER //
CREATE FUNCTION sample(a INT, b INT)
   RETURNS INT
   DETERMINISTIC
   BEGIN
      declare RES INT;
      SET RES = a+b;
      return RES;
   END//
DELIMITER ;

You can call the above function as −

ELECT sample(2258, 6695);

Output

The above query produces the output shown below −

sample(2258, 6695)
8953

Example

Following query creates a procedure that accepts parameters −

DELIMITER //
CREATE PROCEDURE sample(IN a INT, IN b INT, OUT RES INT)
   BEGIN
      SET RES = a+b;
   END//
DELIMITER ;

You can call the above procedure as −

CALL sample(1254, 5894, @res);
SELECT @res;

Output

Following is the output of the above mysql query −

@res
7148

Reading values from queries into local variables

You can read values from a query into a local variable using the INTO clause.

Example

Assume we have created a table named dispatches as shown below −

CREATE TABLE Dispatches( 
   Product_Name VARCHAR(255), 
   Name_Of_Customer VARCHAR(255), 
   Month_Of_Dispatch VARCHAR(255), 
   Price INT, Location VARCHAR(255)
);

Let us insert 5 records in it using the INSERT statement.

Insert into dispatches values
('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 
7000, 'Hyderabad'),
('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 
2000, 'Vishakhapatnam'),
('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 
3000, 'Vijayawada'),
('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 
9000, 'Chennai'),
('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 
6000, 'Goa');

Following query creates a procedure that retrieves the Product_Name and location from a SELECT query to local variables. In here we are using two OUT parameters to store these values −

DELIMITER // ;
Create procedure proc (OUT name VARCHAR(255), OUT loc VARCHAR(255))
   BEGIN
      SELECT Product_Name, Location into name, loc FROM Dispatches 
	  where Name_Of_Customer = 'Roja';
   END //
DELIMITER ;

You need to call this procedure by passing two variables and you can retrieve values from them using SELECT statement.

CALL proc(@name, @loc);
SELECT @name, @loc;

Output

The above mysql query will generate the following output −

@name	@loc
Earphones	Vishakhapatnam

Setting values directly

You can also set values to the variables in MySQL directly using the SET statement.

Example

Following query creates a procedure in here we have declared two variables and assigned values to these using the SET statement −

DELIMITER //

CREATE PROCEDURE RepeatExample()
BEGIN
   DECLARE val INT;
   DECLARE squares INT;
   DECLARE res VARCHAR(100);
   SET val=1;
   SET squares=1;
   SET res = '';
   REPEAT
      SET squares = val*val;
      SET res = CONCAT(res, squares,',');
      SET val = val + 1;
   UNTIL val >= 10
   END REPEAT;
   SELECT res;
END//

DELIMITER ;

You can call the above procedure as follows −

CALL RepeatExample; //

Output

Following is the output of the above query −

res
1,4,9,16,25,36,49,64,81,

Scope and resolution of the local variables

The scope of the local variables lies within the block (BEGIN...END) it I declared. If you have nested block statements the internal block can use the variable declared in the external block.

If you create a Prepared statement in a Procedure or a function it cannot use a local variable.

The local variable's name should be different from any of the table's columns (returned by the query) used in the current stored program.