MySQL - Local Variable DECLARE Statement

---

---

MySQL DECLARE Statement

The DECLARE statement of MySQL is used to declare local variables with in a compound statement or a function.

Syntax

Following is the syntax of the DECLARE statement of MySQL −

DECLARE var_name type [DEFAULT value]

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

The values of the local variables can be expressions.

Example

Following is an example of declaring local variables in a procedure. In here we are assigning an expression as a value to the variable named area −

DELIMITER //
CREATE FUNCTION areaOfCircle(radius INT)
   RETURNS INT
   DETERMINISTIC
   BEGIN
      DECLARE area INT;
      SET area= PI() *radius*radius;
      Return area;
   END//
DELIMITER ;

You can call the above function as follows −

SELECT areaOfCircle(25);

Output

Following is the output of the above query −

areaOfCircle(25)
1963

Example

Following is a procedure doing the same task as the above function −

DELIMITER //
CREATE PROCEDURE areaOfCircle(IN radius INT)
   BEGIN
      DECLARE area INT DEFAULT 1;
      SET area= PI() *radius*radius;
      SELECT area;
   END//
DELIMITER ;

You can call the above procedure as follows −

CALL areaOfCircle(25);

Output

The above query produces the following output −

area
1963

The DEFAULT keyword

If you havent passed any values to the declared variables their values will be NULL. You can also provide a default value to the local variables using the DEFAULT keyword.

Example

Following query creates a procedure in here we are declaring 2 variables with default values −

DELIMITER //
CREATE PROCEDURE test()
   BEGIN
      DECLARE num INT default 1;
      DECLARE res Varchar(50) default '';
      REPEAT
         SET res = CONCAT(res,num,',');
         SET num = num*5;
      UNTIL num > 78125
      END REPEAT;
      SELECT res;
   END //
DELIMITER ;

You can call the above created procedure as shown below −

call test;

Output

After executing the above query, it produces the output shown below −

res
1,5,25,125,625,3125,15625,78125,