What is the initial value of a local variable in a stored procedure in Mysql?

2019-10-22 11:24:50 Sushant Mysql

If a MySQL stored procedure or function declares a local variable that is valid only within the process, and if you do not specify an initial value in the DEFAULT clause, the local variable is initialized with NULL. If an initial value is specified in the DEFAULT clause, it is initialized with the value specified in the DEFAULT clause.

If the TIMESTAMP type is used as a table column and INSERT is performed with NULL, the execution date and time is set as the initial value. However, when used as a local variable data type, the initial value is NULL as with other data types. Is set.

Let's check it by actually executing the code. First, the default value is not specified in the DEFAULT clause.

Sample code (no initial value is specified in the DEFAULT clause)

DELIMITER //
CREATE PROCEDURE testproc ()
BEGIN
 DECLARE var_int1 INT;
 DECLARE var_text1 TEXT;
 DECLARE var_date1 DATE;
 DECLARE var_timestamp1 TIMESTAMP;
  
 SELECT var_int1;
 SELECT var_text1;
 SELECT var_date1;
 SELECT var_timestamp1;
END
//
DELIMITER;

The result of executing the above is as follows.


mysql> CALL testproc ();
+ ---------- +
| var_int1 |
+ ---------- +
| NULL |
+ ---------- +
1 row in set (0.00 sec)

+ ----------- +
| var_text1 |
+ ----------- +
| NULL |
+ ----------- +
1 row in set (0.00 sec)

+ ----------- +
| var_date1 |
+ ----------- +
| NULL |
+ ----------- +
1 row in set (0.01 sec)

+ ---------------- +
| var_timestamp1 |
+ ---------------- +
| NULL |
+ ---------------- +
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

The following is an example when the initial value is specified in the DEFAULT clause. You can also use functions in the DEFAULT clause.

Sample code (specify the initial value in the DEFAULT clause)

DELIMITER //
CREATE PROCEDURE testproc ()
BEGIN
 DECLARE var_int1 INT DEFAULT 35;
 DECLARE var_text1 TEXT DEFAULT 'MySQL';
 DECLARE var_date1 DATE DEFAULT '2020-04-01';
 DECLARE var_timestamp1 TIMESTAMP DEFAULT NOW ();
  
 SELECT var_int1;
 SELECT var_text1;
 SELECT var_date1;
 SELECT var_timestamp1;
END
//
DELIMITER;

The result of executing the above is as follows.


mysql> CALL testproc ();
+ ---------- +
| var_int1 |
+ ---------- +
| 35 |
+ ---------- +
1 row in set (0.00 sec)

+ ----------- +
| var_text1 |
+ ----------- +
| MySQL |
+ ----------- +
1 row in set (0.00 sec)

+ ------------ +
| var_date1 |
+ ------------ +
| 2020-04-01 |
+ ------------ +
1 row in set (0.01 sec)

+ --------------------- +
| var_timestamp1 |
+ --------------------- +
| 2014-12-16 00:43:22 |
+ --------------------- +
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

This post is submitted by one of our members. You may submit a new post here.

Related Tricks