What do you mean by Scope of variables inside MySQL stored procedure?

Suppose if we declare a variable inside a BEGIN/END block then the scope of this variable would be in this particular block. We can also declare a variable with the same name inside another BEGIN/END block which will be totally legal but its scope would be inside its BEGIN/END block. It can be understood with the help of the following example in which we are creating a procedure to show the scope of the variables −

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 i.e. A. Here the inner variable declaration takes precedence as long as it is in scope. The point is that the inner variable disappears when the first END is reached and it is called ‘OUT OF SCOPE’. To understand the concept invoke 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)

Jennifer Nicholas

Updated on 22-Jun-2020 07:38:09

• Related Questions & Answers
• What do you mean by a dynamic initialization of variables?
• How can a MySQL stored procedure call another MySQL stored procedure inside it?
• What do you mean by corporate culture?
• What do you mean by Teen Stress?
• What do you mean by compliance specialist?
• What do you mean by C++ Tokens?
• What do you mean by performance testing?
• What do you mean by timeOut in TestNG?
• What do you mean by Listeners in TestNG?
• What do you mean by glue in Cucumber?
• What do you mean by congestion control algorithm?
• What do you mean by schedule in DBMS?
• What do you mean by interfaces and services?
• What do you mean by default MySQL database for the user?
• What do you mean by database view and how do MySQL views work?