循环遍历表并调用存储过程

Right now, i am trying to loop over a temporary table, that an import process created. I've tried using a cursor, but the tablename must be passed to the stored procedure.

现在，我试图循环一个临时表，创建一个导入过程。我尝试过使用游标，但必须将tablename传递给存储过程。

CREATE PROCEDURE `do_update`(tablename VARCHAR(100))
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE c_ean VARCHAR(20);
    DECLARE c_sku VARCHAR(20);
    DECLARE c_mpn VARCHAR(20);
    DECLARE c_manufacturerName VARCHAR(100);
    DECLARE c_manufacturerUniqueId VARCHAR(100);
    DECLARE c_images TEXT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
    -- move declare cursor into sql to be executed
    SET @sqlstatement = CONCAT('DECLARE import_cursor CURSOR FOR SELECT ean, sku, mpn, manufacturerName, manufacturerUniqueId, images FROM ', tablename);

    PREPARE stmt FROM @sqlstatement;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;    

    OPEN import_cursor;
    importloop: LOOP
        FETCH import_cursor INTO c_ean, c_sku, c_mpn, c_manufacturerName, c_manufacturerUniqueId, c_images;
        IF done THEN
            LEAVE importloop;
        END IF;
        CALL upsert_article(c_ean, c_sku, c_mpn, c_manufacturerName, c_manufacturerUniqueId, c_images);
    END LOOP testloop;
    CLOSE import_cursor;
END

Note: This import script will be called several times over the day and could be run concurrently. So a defined tablename is not an option.

注意：此导入脚本将在一天内多次调用，并且可以同时运行。因此，定义的表名不是一个选项。

What i have tried so far:

到目前为止我尝试了什么：

1. Creating a cursor with given tablename. --> Works, but the name must be dynamic.
2. 使用给定的表名创建游标。 - > Works，但名称必须是动态的。
3. Creating a cursor with dynamic sql. --> Failed
4. 使用动态sql创建游标。 - >失败了
5. Skip using cursors and loop over table in a different way. --> No method found for using dynamic sql
6. 使用游标跳过并以不同的方式循环遍历表。 - >找不到使用动态sql的方法

So, is there a way to make this work with a cursor or a workaround?

那么，有没有办法使用游标或变通方法来完成这项工作？

1 个解决方案

#1

Creating a temporary table and inserting the results of my dynamic query did the trick at the end. With that i was able to use mysql cursor with the static named temporary table. And since it is an temporary table, multiple import-jobs won't interfere with each other, because they are hidden to other sessions.

创建一个临时表并插入我的动态查询的结果最后就成了诀窍。有了这个我能够使用mysql游标与静态命名临时表。由于它是一个临时表，因此多个导入作业不会相互干扰，因为它们对其他会话是隐藏的。

In the end this is the code, that worked for me:

最后这是代码，对我有用：

CREATE PROCEDURE `do_update`(IN 

tablename VARCHAR(100))
BEGIN

    DECLARE done INT;

    DECLARE c_ean VARCHAR(20);
    DECLARE c_sku VARCHAR(20);
    DECLARE c_mpn VARCHAR(20);
    DECLARE c_manufacturerName VARCHAR(100);
    DECLARE c_manufacturerUniqueId VARCHAR(100);
    DECLARE c_images NVARCHAR(3000);
    DECLARE import_cursor CURSOR FOR SELECT ean, sku, mpn, manufacturerName, manufacturerUniqueId, images FROM importjob;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

    DROP TEMPORARY TABLE IF EXISTS importjob;

    SET @sqlstatement = CONCAT('CREATE TEMPORARY TABLE importjob SELECT ean, sku, mpn, manufacturerName, manufacturerUniqueId, images FROM ', tablename);
    PREPARE stmt FROM @sqlstatement;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @DEBUG = true;

    OPEN import_cursor;
    importloop: LOOP
        FETCH import_cursor INTO c_ean, c_sku, c_mpn, c_manufacturerName, c_manufacturerUniqueId, c_images;
        IF done THEN
            LEAVE importloop;
        END IF;
        CALL upsert_article(c_ean, c_sku, c_mpn, c_manufacturerName, c_manufacturerUniqueId, c_images);
    END LOOP importloop;
    CLOSE import_cursor;

    DROP TEMPORARY TABLE IF EXISTS importjob;
END