现在,我正在尝试遍历一个临时表,该表是导入进程创建的。我试过使用游标,但是表名必须传递给存储过程。
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注意:这个导入脚本将在一天内被多次调用,并且可以同时运行。因此,定义的表名不是选项。
到目前为止,我已经尝试过:
那么,是否有一种方法可以使用游标或解决方案来使其工作呢?
发布于 2016-02-17 09:09:52
创建一个临时表并插入我的动态查询的结果在最后完成了这个任务。有了这一点,我就能够在静态命名的临时表中使用mysql游标。由于它是一个临时表,多个导入作业不会相互干扰,因为它们隐藏在其他会话中。
最后,这是对我有用的代码:
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;
ENDhttps://stackoverflow.com/questions/35434825
复制相似问题