英文:
HOW TO CREATE PROCEDURE TO execute queries from another input query string in MYSQL?
问题
I want to create a procedure which accepts a string as query0, generating a number of queries, and then executes them. However, it prompts an error as below:
Error Code: 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 'DECLARE curs CURSOR FOR stmt0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SE' at line 10
What's wrong with my code? Or is it possible?
Before working on the above case, I have created another procedure with a fixed query successfully as below:
DELIMITER $$
USE `zzz_test`$$
DROP PROCEDURE IF EXISTS `test2`$$
CREATE DEFINER=`root`@`%` PROCEDURE `test2`()
BEGIN
DECLARE bDone INT;
DECLARE qry VARCHAR(65535);
DECLARE curs CURSOR FOR SELECT CONCAT('INSERT INTO zzz_test.test2 SELECT "',TABLE_NAME,'" as tb_name, ... FROM zzz_test0.',TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='zzz_test0';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
OPEN curs;
SET bDone = 0;
REPEAT
FETCH curs INTO qry;
SET @query = qry;
PREPARE stmt FROM @query;
EXECUTE stmt;
UNTIL bDone END REPEAT;
CLOSE curs;
END$$
DELIMITER ;
英文:
I want to create a procedure which accept a string as query0 generating a number of queries then execute them:
DELIMITER $$
CREATE
PROCEDURE `zzz_test`.`nest_query`(IN qry_str VARCHAR(65535))
BEGIN
DECLARE bDone INT;
DECLARE qry VARCHAR(65535);
SET @query0 = qry_str;
PREPARE stmt0 FROM @query;
DECLARE curs CURSOR FOR stmt0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
OPEN curs;
SET bDone = 0;
REPEAT
FETCH curs INTO qry;
SET @query = qry;
PREPARE stmt FROM @query;
EXECUTE stmt;
UNTIL bDONE END REPEAT;
CLOSE curs;
END$$
DELIMITER ;
However, it prompt error as below:
Error Code: 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 'DECLARE curs CURSOR FOR stmt0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SE' at line 10
What's wrong with my code? Or is it possible?
Before working on above case, I have create another procedure with fixed query successfully as below:
DELIMITER $$
USE `zzz_test`$$
DROP PROCEDURE IF EXISTS `test2`$$
CREATE DEFINER=`root`@`%` PROCEDURE `test2`()
BEGIN
DECLARE bDone INT;
DECLARE qry VARCHAR(65535);
DECLARE curs CURSOR FOR SELECT CONCAT('INSERT INTO zzz_test.test2 SELECT "',TABLE_NAME,'" as tb_name, ... FROM zzz_test0.',TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='zzz_test0';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
OPEN curs;
SET bDone = 0;
REPEAT
FETCH curs INTO qry;
SET @query = qry;
PREPARE stmt FROM @query;
EXECUTE stmt;
UNTIL bDONE END REPEAT;
CLOSE curs;
END$$
DELIMITER ;
答案1
得分: 1
不能在游标中使用动态SQL。不清楚您为什么想要使用游标或存储过程,因为这对您没有任何优势。
英文:
You cannot use dynamic SQL with a cursor. It is unclear why you want to use cursor or a stored procedure as is gives no advantage for you.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论