в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:17:10Z

1 версия

Новая страница

==Inner variable shadows the outter variable==



<source lang="sql">

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>
</source>

==LOCAL, SESSION, AND GLOBAL VARIABLES IN MYSQL==



<source lang="sql">

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>
</source>

==Nested blocks==



<source lang="sql">

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>
</source>

==Using Declare statement to declare the local variable==



<source lang="sql">

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)
</source>

==Variable scope==



<source lang="sql">

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>
</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL/MySQL/Procedure Function/Variable Scope - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия