我想问一下是否可以在动态查询中使用DECLARE
变量。
即:
CREATE PROCEDURE `storedProcedureName`()
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SET @i = 0;
PREPARE dynamicQuery FROM 'DECLARE x INT(11);';
EXECUTE dynamicQuery;
PREPARE dynamicQuery FROM 'SET x := ?;';
EXECUTE dynamicQuery USING @i;
DEALLOCATE PREPARE dynamicQuery;
/* More Queries Here */
END
但是我收到语法错误:
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 'DECLARE x INT(11)' at line 1
谢谢
最佳答案
首先-仅在BEGIN ... END复合语句中允许使用DECLARE。
其次-并非所有语句都可以在PREPARE语句中使用,此处有更多详细信息-Prepared Statements。
但是您可以使用类似这样的内容-
SET @text = 'something';
SET @var = NULL;
SET @query = 'SELECT ? INTO @var';
PREPARE s FROM @query;
EXECUTE s USING @text;
DEALLOCATE PREPARE s;
SELECT @var;
+-----------+
| @var |
+-----------+
| something |
+-----------+
关于mysql - MySQL动态查询与声明,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/8398838/