如何在MYSQL中创建存储过程以执行另一个输入查询字符串?

huangapple go评论62阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年4月13日 16:23:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76003237.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定