What does variable scope in MySQL stored procedures mean?

Suppose if we declare a variable inside BEGIN/END block then the scope of that variable will be in this particular block. We could also declare a variable with the same name inside another BEGIN/END block, which would be perfectly legal, but its scope would be within its BEGIN/END block. This can be understood with the help of the following example, in which we create a procedure to display the scope of a variable -

Example

mysql> Create Procedure Scope_variables()
    -> BEGIN
    -> DECLARE A Varchar(5) Default 'outer';
    -> BEGIN
    -> DECLARE A Varchar(5) Default 'inner';
    -> SELECT A;
    -> END;
    -> SELECT A;
    -> END;
    -> //
Query OK, 0 rows affected (0.08 sec)

In the above procedure, we have two variables with the same name , namely A. Here, as long as the internal variable is declared within the scope, it takes precedence. The main point is that when the first END is reached, the internal variable disappears and is said to go "out of scope". To understand this concept, call this procedure as follows -

mysql> CALL Scope_variables();
+-------+
| A     |
+-------+
| inner |
+-------+
1 row in set (0.00 sec)

+-------+
| A     |
+-------+
| outer |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

The above is the detailed content of What does variable scope in MySQL stored procedures mean?. For more information, please follow other related articles on the PHP Chinese website!