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

I have a two tables:

我有两张桌子:

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:

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

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.

如果select给出3行,那么我们必须循环遍历行并使用每行中的值执行某些操作。别的什么东西(不重要的是什么,但是我把这个让你明白我需要在循环之前有一个if语句。

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.

我已经看过并阅读过关于游标的信息,但是找不到由concat创建的选项(它有关系吗?),特别是用一个准备好的语句创建。如何遍历结果列表并使用每行的值?谢谢。

2 个解决方案

#1

I have some bad and good news for you.

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

First the bad news.

首先是坏消息。

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.

MySQL手册说游标不能用于使用PREPARE和EXECUTE准备和执行的动态语句。在游标创建时检查游标的语句,因此该语句不能是动态的。

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

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

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

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

And to test the procedure:

并测试程序:

CALL myproc('people_en');

#2

@clickstefan, you will have problems with two or more users trying to execute your script at the same time. The second user will get error message 'View vw_myproc already exists' for the line:

@clickstefan,您将遇到两个或更多用户同时尝试执行脚本的问题。第二个用户将收到该行的“查看vw_myproc已存在”错误消息:

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

The solution is temporary table - it exists for the lifetime of current connection only, and users may simultaneously create temporary tables with the same name. So, code may looks like:

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

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

#1