SQL/MySQL/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>
mysql> SET @cost = 5, @cost1 = 8.00;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select @cost;
+-------+
| @cost |
+-------+
| 5     |
+-------+
1 row in set (0.01 sec)
mysql>
mysql> select @cost1;
+--------+
| @cost1 |
+--------+
| 8.00   |
+--------+
1 row in set (0.00 sec)
mysql>

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>