表中的mysql存储过程设置值 [英] mysql stored procedure set value from table
本文介绍了表中的mysql存储过程设置值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个简单的下表:
mysql> select * from version;
+----+---------+
| id | version |
+----+---------+
| 1 | 1 |
+----+---------+
1 row in set (0.00 sec)
我需要创建一个存储过程,该存储过程将根据此表的值(确切地说,该表的唯一行)执行某些操作(或不执行任何操作).
I need to create a stored procedure that will do something (or nothing) depending on the value of this table (precisely, the only row of this table).
DELIMITER $$
DROP PROCEDURE IF EXISTS upgrade_version $$
CREATE PROCEDURE upgrade_version(current_version INTEGER, script TEXT)
BEGIN
DECLARE version INT(11);
SET version = (SELECT `version` FROM `version` WHERE `id` = 1 LIMIT 1);
SELECT version;
IF version = current_version + 1 THEN
PREPARE upgrade_stmt FROM script;
EXECUTE upgrade_stmt;
UPDATE `version` SET `version` = `version` + 1 WHERE `id` = 1;
ENDIF;
END $$
DELIMITER ;
在以下位置被调用
CALL upgrade_version(1,'ALTER TABLE ...');
语法上不正确,mysql返回:
It is syntactically incorrect, mysql returns:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'script;
EXECUTE upgrade_stmt;
UPDATE `version` SET `version` = `version`' at line 7
怎么了?
推荐答案
尝试一下:
DELIMITER $$
DROP PROCEDURE IF EXISTS upgrade_version $$
CREATE PROCEDURE upgrade_version(current_version INT, script TEXT)
BEGIN
SELECT `version` into @ver FROM `version` WHERE `id` = 1 LIMIT 1;
SET @s = script;
IF @ver = current_version + 1 THEN
PREPARE upgrade_stmt FROM @s;
EXECUTE upgrade_stmt;
DEALLOCATE PREPARE upgrade_stmt;
UPDATE `version` SET `version` = `version` + 1 WHERE `id` = 1;
END IF;
END $$
DELIMITER ;
这篇关于表中的mysql存储过程设置值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文