用于准备语句的 MySQL 存储过程游标

Question

我有两个表:

people_en: id, name
people_es: id, name

(请不要为规范化而烦恼。设计是规范化的。表格比这复杂得多但这只是简化我的问题的一种方法。

然后我有一个存储过程:

CREATE PROCEDURE myproc(lang char(2))
BEGIN
    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
        //Here I need to loop through the rows
    ELSE
        //Do something else
    END IF;
   END$$

或多或少，程序中的逻辑是:

如果选择给出 3 行，那么我们必须遍历这些行并对每行中的值执行一些操作。否则还有其他事情(不重要，但我说这个是为了让你明白我需要循环前的 if 语句。

我看过游标，但找不到太多关于 concat 创建的选择(这有关系吗？)尤其是用准备好的语句创建的。如何遍历结果列表并使用每一行的值？谢谢。

Answer 1

我有一些坏消息和好消息要告诉你。

首先是坏消息。

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。

下面我重写了您的代码并应用了 View 来制作“动态”光标。

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');