存储过程中的 MySQL PREPARE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5895383/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL PREPARE statement in stored procedures
提问by Lucio Crusca
I have this sql file:
我有这个 sql 文件：
USE mydb;
DROP PROCEDURE IF EXISTS execSql;
DELIMITER //
CREATE PROCEDURE execSql (
IN sqlq VARCHAR(5000)
) COMMENT 'Executes the statement'
BEGIN
PREPARE stmt FROM sqlq;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
When I try to run it with
当我尝试运行它时
# cat file.sql | mysql -p
# cat file.sql | mysql -p
I get
我得到
ERROR 1064 (42000) at line 6: 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 'sqlq;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END' at line 5
What am I doing wrong?
我究竟做错了什么？
回答by mpf
you can only prepare and execute sql that's a string literal or a uservariable that contains the text of the statement. try
您只能准备和执行包含语句文本的字符串文本或用户变量的sql 。尝试
USE mydb;
DROP PROCEDURE IF EXISTS execSql;
DELIMITER //
CREATE PROCEDURE execSql (
IN sqlq VARCHAR(5000)
) COMMENT 'Executes the statement'
BEGIN
SET @sqlv=sqlq;
PREPARE stmt FROM @sqlv;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
回答by Dinesh Vaitage
You can use Concatenate, PREPARE and EXECUTE statements as bellow..
您可以使用 Concatenate、PREPARE 和 EXECUTE 语句，如下所示。
CREATE DEFINER=`products`@`localhost` PROCEDURE `generateMeritList`(
IN `mastercategory_id` INT(11),
IN `masterschools_id` INT(11)
)
NO SQL
begin
declare total int default 0;
declare conditions varchar(255) default '';
declare finalQuery varchar(60000) default '';
if mastercategory_id > 0 THEN
SET conditions = CONCAT(' and app.masterschools_id = ', mastercategory_id);
end if;
SET @finalQuery = CONCAT(
"SELECT * FROM applications app INNER JOIN masterschools school ON school.id = app.masterschools_id
WHERE
(app.status = 'active'", conditions, " LIMIT ",total);
PREPARE stmt FROM @finalQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end
