将 oracle for 循环转换为 mysql 循环 [英] Convert oracle for loop to mysql loop

问题描述

我正在将 oracle 数据库迁移到 mysql.当子查询获取多条记录时，我面临更新特定表列的问题.

I am migrating the oracle database to mysql. I am facing the issue in updating a column of perticular table when subquery fetching multiple records.

DECLARE

BEGIN
    FOR TENORTYPE in (select gto.string_value, gtl.rowid from gtp_template_objectdata gto, gtp_template_lc gtl
                      where gto.template_id=gtl.template_id and gto.company_id = gtl.company_id and gto.name='tenor_type')
    LOOP
        update gtp_template_lc set tenor_type = TENORTYPE.string_value where rowid = TENORTYPE.rowid;
    END LOOP;
    COMMIT;
END;
/

以上是oracle代码.我试过如下

Above is the oracle code. I have tried like below

  DELIMITER //
 CREATE PROCEDURE do_loop(i VARCHAR)
 BEGIN
       SET i=1; 
   loop_loop: LOOP
   FOR TENORTYPE in (select gto.string_value,@rowid:=@rowid+1 as rowid from gtp_template_objectdata gto, gtp_template_lc gtl
                      where gto.template_id=gtl.template_id and gto.company_id = gtl.company_id and gto.name='tenor_type');

     IF i <= select count(gto.string_value) from gtp_template_objectdata gto, gtp_template_lc gtl
                      where gto.template_id=gtl.template_id and gto.company_id = gtl.company_id and gto.name='tenor_type' 
    THEN
            update gtp_template_lc set tenor_type = TENORTYPE.string_value where rowid = TENORTYPE.rowid;
            SET i=i+1
    ITERATE loop_loop;
     END IF;
     LEAVE loop_loop;
   END LOOP loop_loop;
 END//
  DELIMITER ;

但是在 mysql 中得到错误的语法.子查询返回 2 列.

But getting incorrect syntax in mysql. Subquery returns 2 columns.

推荐答案

您可以使用单个 DML 语句执行此操作，使用多表 UPDATE 语法:

You can perform this operation with a single DML statement, using the multiple-table UPDATE syntax:

UPDATE gtp_template_lc gtl
  JOIN gtp_template_objectdata gto USING (template_id, company_id)
SET    gtl.tenor_type = gto.string_value
WHERE  gto.name = 'tenor_type'

在程序循环中执行相同的操作需要一个游标:

To undertake the same operation in a procedural loop requires a cursor:

DELIMITER //
CREATE PROCEDURE do_loop() BEGIN

  DECLARE cur CURSOR FOR
    SELECT gto.string_value, gtl.rowid
    FROM   gtp_template_objectdata gto
      JOIN gtp_template_lc gtl USING (template_id, company_id)
    WHERE  gto.name='tenor_type';

  DECLARE _string_value VARCHAR(255), _rowid INT;
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

  OPEN cur;
  updateLoop: LOOP

    FETCH cur INTO _string_value, _rowid;

    IF done THEN
      LEAVE updateLoop;
    END IF;

    UPDATE gtp_template_lc
    SET    tenor_type = _string_value
    WHERE  rowid = _rowid;

  END LOOP updateLoop;
  CLOSE cur;

END//
DELIMITER ;

这篇关于将 oracle for 循环转换为 mysql 循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！