MySQL Tutorial/Procedure Function/Variable Scope

Inner variable shadows the outter variable

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc()
    -> BEGIN
    ->         DECLARE my_variable VARCHAR(20);
    ->         SET my_variable="This value was set in the outer block";
    ->         BEGIN
    ->                 DECLARE my_variable VARCHAR(20);
    ->                 SET my_variable="This value was set in the inner block";
    ->         END;
    ->         SELECT my_variable, "Can""t see changes made in the inner block";
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call myProc();
+----------------------+-------------------------------------------+
| my_variable          | Can"t see changes made in the inner block |
+----------------------+-------------------------------------------+
| This value was set i | Can"t see changes made in the inner block |
+----------------------+-------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>

LOCAL, SESSION, AND GLOBAL VARIABLES IN MYSQL

MySQL has three different kinds of variables:

Local variables

Local variables are set in the scope of a statement or block of statements.

Once that statement or block of statements has completed, the variable goes out of scope.

Session variables

Session variables are set in the scope of your session with the MySQL server.

A session starts with a connection to the server and ends when the connection is closed.

Variables go out of scope once the connection is terminated.

Variables created during your connection cannot be referenced from other sessions.

To declare or reference a session variable, prefix the variable name with an @ symbol:

SET @count = 100;.

Nested blocks

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc()
    -> BEGIN
    ->         DECLARE my_variable VARCHAR(20);
    ->         SET my_variable="This value was set in the outer block";
    ->         BEGIN
    ->                 DECLARE my_variable VARCHAR(20);
    ->                 SET my_variable="This value was set in the inner block";
    ->         END;
    ->         SELECT my_variable, "Can""t see changes made in the inner block";
    -> END$$
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql>
mysql> call myProc();
+----------------------+-------------------------------------------+
| my_variable          | Can"t see changes made in the inner block |
+----------------------+-------------------------------------------+
| This value was set i | Can"t see changes made in the inner block |
+----------------------+-------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>

Using Declare statement to declare the local variable

mysql>
mysql> DELIMITER //
mysql>
mysql> CREATE FUNCTION myFunction (cost DECIMAL(10,2)) RETURNS DECIMAL(10,2)
    -> BEGIN
    ->
    ->     DECLARE shipping_cost DECIMAL(10,2);
    ->
    ->     SET shipping_cost = 0;
    ->     IF cost < 25.00 THEN
    ->             SET shipping_cost = 10.00;
    ->     ELSEIF cost < 100.00 THEN
    ->             SET shipping_cost = 20.00;
    ->     ELSEIF cost < 200.00 THEN
    ->             SET shipping_cost = 30.00;
    ->
    ->     ELSE
    ->             SET shipping_cost = 40.00;
    ->     END IF;
    ->
    ->     RETURN shipping_cost;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
mysql> select myFunction(123.123);
+---------------------+
| myFunction(123.123) |
+---------------------+
|               30.00 |
+---------------------+
1 row in set, 1 warning (0.02 sec)
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)

Variable scope

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc()
    -> BEGIN
    ->         DECLARE outer_variable VARCHAR(20);
    ->         BEGIN
    ->                 DECLARE inner_variable VARCHAR(20);
    ->                 SET inner_variable="This is my private data";
    ->         END;
    ->         SELECT inner_variable," This statement causes an error ";
    ->
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call myProc();
ERROR 1054 (42S22): Unknown column "inner_variable" in "field list"
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>