MySQL 如何在MySQL存储过程中避免变量值在记录更新时更改?
我们将创建一个存储过程,每当值更新时都不会更改变量值。
让我们首先创建一个表 −
mysql> create table DemoTable
(
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Value int
);
Query OK, 0 rows affected (0.63 sec)
Mysql
使用插入命令将一些记录插入表中−
mysql> insert into DemoTable(Value) values(100);
Query OK, 1 row affected (0.13 sec)
使用select语句显示表中的所有记录:
mysql> select *from DemoTable;
Mysql
阅读更多:MySQL 教程
输出
+----+-------+
| Id | Value |
+----+-------+
| 1 | 100 |
+----+-------+
1 row in set (0.00 sec)
Mysql
以下是在更新后显示旧值的存储过程−
mysql> DELIMITER //
mysql> CREATE PROCEDURE updateValue100()
BEGIN
DECLARE myValue int;
select @myValue :=(select Value from DemoTable where Id=1);
select @myValue;
update DemoTable set Value=200 where Id=1;
select @myValue :=(select Value from DemoTable where Id=1);
select @myValue;
END
//
Query OK, 0 rows affected (0.21 sec)
mysql> DELIMITER ;
Mysql
现在可以使用CALL命令调用存储过程−
mysql> call updateValue100();
Mysql
输出
+-------------------------------------------------------+
| @myValue :=(select Value from DemoTable where Id=1) |
+-------------------------------------------------------+
| 100 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
+----------+
| @myValue |
+----------+
| 100 |
+----------+
1 row in set (0.01 sec)
+-------------------------------------------------------+
| @myValue :=(select Value from DemoTable where Id=1) |
+-------------------------------------------------------+
| 200 |
+-------------------------------------------------------+
1 row in set (0.16 sec)
+----------+
| @myValue |
+----------+
| 200 |
+----------+
1 row in set (0.17 sec)
Query OK, 0 rows affected (0.18 sec)
Mysql