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创建的选择语句（这有关系吗？） 特别是在使用预处理语句时。我该如何迭代结果列表并使用每行的值？ 谢谢。

- user3355182

Answer 1

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

首先是坏消息。

MySQL 手册说不能使用光标来处理由 PREPARE 和 EXECUTE 准备和执行的动态语句。因为光标的语句在光标创建时会被检查，所以该语句不能是动态的。

所以到目前为止还没有动态光标... 在这里你需要像这样做。

但是现在好消息是：至少有两种方法可以绕过它 - 使用 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');

- miko

---

对于这种情况，我会假设在运行存储过程时将检查CREATE TEMPORARY TABLE权限，而不是在创建它的时候检查。对吗？ - Ifedi Okonkwo

Answer 2

@clickstefan，如果有两个或更多用户同时尝试执行您的脚本，则会出现问题。第二个用户将在以下行上收到错误消息“View vw_myproc 已经存在”：

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

解决方案是临时表 - 它仅在当前连接的生命周期内存在，用户可能同时创建具有相同名称的临时表。因此，代码可能如下所示：

DROP TABLE IF EXISTS vw_myproc;
SET @select = concat('CREATE TEMPORARY TABLE vw_myproc AS SELECT * FROM ', lang, ' limit 3');

- TheKeksov

---

对于这种情况，我会假设在运行存储例程时将检查CREATE TEMPORARY TABLE权限，而不是在创建它的时候检查。 - Ifedi Okonkwo

创建视图也可以使用，我们需要基于时间戳来创建它，在存储过程执行后会被删除。 在下面的语句中： DROP TABLE IF EXISTS vw_myproc;表不会被创建，但数据可能会混合。如果多个用户同时调用，则可能会出现此情况。 - Pratik