在CREATE PROCEDURE中使用DECLARE和预处理语句时的语法错误 [英] Syntax Error on using DECLARE and prepared statement inside CREATE PROCEDURE

问题描述

我正在尝试在存储过程中使用MySQL准备的语句.我一直在尝试调试此CREATE代码两个小时，现在感到沮丧.

I am trying to work with MySQL prepared statements inside of a stored procedure. I have been trying to debug this CREATE code for a couple of hours, now frustrated.

DELIMITER //
DROP PROCEDURE IF EXISTS doSomething//
CREATE PROCEDURE doSomething(IN tblname CHAR(32))
BEGIN
    DECLARE str1 TEXT DEFAULT '';
    SET str1 = CONCAT("SELECT * FROM ", tblname);
    PREPARE stmt1 FROM str1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END//
DELIMITER ;

它引发了各种各样的语法"错误.更糟糕的是，确切的错误消息似乎在phpMyAdmin和MySQL Console之间有所不同.

It is throwing up all sorts of "syntax" errors. Worse still, the exact error message seems to vary between phpMyAdmin and the MySQL Console.

请有人帮我找出语法错误.

Please, someone, help me pinpoint the syntax error.

谢谢.

PS:经过进一步测试...

这似乎可行:

DELIMITER //
DROP PROCEDURE IF EXISTS doSomething//
CREATE PROCEDURE doSomething(IN tblname CHAR(32))
BEGIN
    SET @str1 = CONCAT("SELECT * FROM ", tblname);
    PREPARE stmt1 FROM @str1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END//
DELIMITER ;

唯一的区别是第一种形式使用的是@variable vs DECLARE.嗯，那么DECLARE怎么了?

And the only difference is the use of @variable vs DECLARE in the first form. Hmmm, so what's wrong with DECLARE here?

推荐答案

原因是

在存储程序上下文中准备的语句不能引用存储的 过程或函数参数或局部变量，因为它们 程序结束时超出范围，并且如果该程序不可用 语句，稍后在程序外部执行.作为解决方法， 而是引用用户定义的变量，该变量也具有会话 范围；

A statement prepared in stored program context cannot refer to stored procedure or function parameters or local variables because they go out of scope when the program ends and would be unavailable were the statement to be executed later outside the program. As a workaround, refer instead to user-defined variables, which also have session scope;

在第一种情况下

DECLARE str1 TEXT DEFAULT '';
SET str1 = CONCAT("SELECT * FROM ", tblname);

str1的范围是本地的.因此mysql抛出错误，您需要使用 用户-定义的变量 ，您将在第二种情况下使用会话范围进行操作.

The scope of str1 is local. So mysql is throwing the error and you need to use User-Defined Variables , which you are doing in the 2nd case with the session scope.

这篇关于在CREATE PROCEDURE中使用DECLARE和预处理语句时的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！