MySQL - DECLARE Statement

---

---

MySQL DECLARE Statement

When you are working with BEGIN ... END compound statements such as variable declarations, conditions, cursors, including loops, conditional tests, functions and procedures, if you need to define items locally in it you can do so using the DECLARE Statement.

Using this statement, you can create Local variables, Conditions, handlers and, Cursors. While declaring these locally you need to follow the below order −

Variable and condition declarations > Cursor declarations-> handler declarations.

Syntax

Following is the syntax of the MySQL DECLARE statement −

DECLARE var_name [, var_name] ... type [DEFAULT value]

You can use the MySQL DECLARE statement to define local variables, Conditions and handlers and, cursors.

Example

Following is an example of declaring local variables −

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,

Example

Following is an example of declaring conditions variables −

DELIMITER //
   CREATE PROCEDURE sample(IN dividend INT, IN divisor INT, OUT res double)
   BEGIN
      DECLARE dividedByZero CONDITION FOR SQLSTATE '22012';
      DECLARE CONTINUE HANDLER FOR dividedByZero
      RESIGNAL SET MESSAGE_TEXT = 'Error Denominator given is zero';
   IF divisor = 0 THEN
      SIGNAL dividedByZero;
   ELSE
      SET res:= dividend / divisor;
   END IF;
   END//
DELIMITER ;

If you call the above procedure by passing '0' as the second parameter.

CALL sample(25, 0, @res);

an error will be displayed as −

ERROR 1644 (22012): Error Denominator given is zero

Example

Assume we have created a table with name mobile_sales in MySQLdatabase using CREATE statement as shown below −

CREATE TABLE mobile_sales (
   SNO INT,
   mobile_brand VARCHAR(255),
   unit_sale INT
);

Now, we will insert 11 records in mobile_sales table using INSERT statements −

Insert into mobile_sales values
(1, 'Iphone', 3000),
(2, 'Samsung', 4000),
(3, 'Nokia', 5000),
(4, 'Vivo', 1500),
(5, 'Oppo', 900),
(6, 'MI', 6400),
(7, 'MotoG', 4360),
(8, 'Lenovo', 4100),
(9, 'RedMI', 4000),
(10, 'MotoG', 4360);

Following query is an example of the cursors statement −

DELIMITER //
CREATE PROCEDURE cursorExample(num INT)
   BEGIN
      DECLARE number_of_units varchar(255);
      DECLARE cur1 CURSOR for SELECT unit_sale FROM mobile_sales 
	  WHERE SNO=num;
      OPEN cur1;
         FETCH cur1 into number_of_units;
         SELECT number_of_units;
      CLOSE cur1;
   END //
DELIMITER ;

You can call the above procedure as shown below −

CALL cursorExample(5);

Output

The above query produces the following output −

number_of_units
900