多值游标循环更新表

Question

DECLARE
   CURSOR contacts
   IS
        SELECT SUM (budget) AS budget
          FROM et_bp_gl_account a, et_bp_fact f
         WHERE     f.gl_account_id = a.gl_account_id
               AND total_flag = 0
      GROUP BY month_id, org_unit_id;
BEGIN
   FOR r IN contacts
   LOOP
      UPDATE et_bp_fact
         SET budget = r.budget
       WHERE gl_account_id IN (SELECT total_element
                                 FROM et_bp_gl_account g, et_bp_fact f
                                WHERE f.gl_account_id = g.gl_account_id);
   END LOOP;
END;

我希望用从游标返回的许多值示例(25，50，75)更新表ET_BP_FACT，但是当我执行由(25,25,25)更新的表时

我认为在循环中有一个问题

Answer 1

这是因为UPDATE的WHERE子句没有“引用”游标的值。

DECLARE
   CURSOR contacts
   IS
        SELECT month_id, org_unit_id,         --> include additional columns here ... 
               SUM (budget) AS budget
          FROM et_bp_gl_account a, et_bp_fact f
         WHERE     f.gl_account_id = a.gl_account_id
               AND total_flag = 0
      GROUP BY month_id, org_unit_id;
BEGIN
   FOR r IN contacts
   LOOP
      UPDATE et_bp_fact
         SET budget = r.budget
       WHERE gl_account_id IN 
         (SELECT total_element
          FROM et_bp_gl_account g, et_bp_fact f
          WHERE f.gl_account_id = g.gl_account_id
            --
            AND f.org_unit_id = r.org_unit_id   --> ... and reference them here ...
            AND g.month_id = r.month_id);
                                                --> ... or, possibly, here
   END LOOP;
END;

我建议您始终在列名之前加上表别名，因为--查看您的代码，无法猜测表MONTH_ID和ORG_UNIT_ID属于哪个表，所以我的代码可能(或可能)不起作用，但我希望您有了大致的想法。