mysql 存储过程 预处理语句_用于预处理语句的MySQL存储过程游标

我有一些坏消息和好消息.

首先是坏消息.

MySQL manual says a cursor cannot be used for a dynamic statement that

is prepared and executed with PREPARE and EXECUTE. The statement for a

cursor is checked at cursor creation time, so the statement cannot be

dynamic.

所以到目前为止还没有动态游标……在这里你需要这样的东西.

但现在好消息是：至少有两种方法可以绕过它 – 使用vw或tbl.

下面我重写了你的代码和应用视图来制作’动态’光标.

DELIMITER //

DROP PROCEDURE IF EXISTS myproc;

CREATE PROCEDURE myproc(IN lang VARCHAR(400))

BEGIN

DECLARE c VARCHAR(400);

DECLARE done BOOLEAN DEFAULT FALSE;

DECLARE cur CURSOR FOR SELECT name FROM vw_myproc;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

SET @select = concat('CREATE VIEW vw_myproc as SELECT * FROM ', lang, ' limit 3');

PREPARE stm FROM @select;

EXECUTE stm;

DEALLOCATE PREPARE stm;

SET @select = concat('SELECT * FROM ', lang, ' limit 3');

PREPARE stm FROM @select;

EXECUTE stm;

DEALLOCATE PREPARE stm;

SET @cnt = FOUND_ROWS();

SELECT @cnt;

IF @cnt = 3 THEN

OPEN cur;

read_loop: LOOP

FETCH cur INTO c;

IF done THEN

LEAVE read_loop;

END IF;

#HERE YOU CAN DO STH WITH EACH ROW e.g. UPDATE; INSERT; DELETE etc

SELECT c;

END LOOP read_loop;

CLOSE cur;

DROP VIEW vw_myproc;

ELSE

SET c = '';

END IF;

END//

DELIMITER ;

并测试程序：

CALL myproc('people_en');