用于准备语句的 MySQL 存储过程游标 [英] MySQL stored procedure cursor for prepared statements

问题描述

我有两张桌子:

people_en: id, name
people_es: id, name

(请不要担心规范化.设计是规范化的.表格比这复杂得多但这只是简化我的问题的一种方式).

(please, dont bother about normalization. the design is normalized. the tables are much more complex than this but this is just a way to simplify my problem).

然后我有一个存储过程:

I then have a stored procedure:

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$$

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

More or less, the logic in the procedure is:

如果选择给出 3 行，那么我们必须遍历行并对每行中的值做一些事情.否则还有别的(不重要的是什么，但我把这个放在让你明白我需要有循环前的 if 语句.

If the select gives 3 rows, then we have to loop through the rows and do something with the value in each row. Otherwise somwthing else (not important what, but I put this to make you understand that I need to have an if statement before looping.

我已经看到并阅读了有关游标的内容，但找不到很多由 concat 创建的选择(这有关系吗?)尤其是用准备好的语句创建的.如何遍历结果列表并使用每一行的值?谢谢.

I have seen and read about cursors, but couldnt find much for selects created by concat (does it matter?) and especially created with a prepared statement. How can I iterate through the result list and use the values from each row? Thanks.

推荐答案

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

I have some bad and good news for you.

首先是坏消息.

MySQL 手册说游标不能用于动态语句用 PREPARE 和 EXECUTE 准备和执行.声明为在创建游标时检查游标，因此该语句不能被动态.

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.

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

So there are no dynamical cursors so far... Here you would need something like this.

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

But now the good news: there are at least two ways to bypass it - using vw or tbl.

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

Below I rewrote your code and applied view to make 'dynamical' cursor.

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

这篇关于用于准备语句的 MySQL 存储过程游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！