Oracle中隱式遊標和顯式遊標的教訓[同事的經曆]

一直以為以下這種語句（執行一）就是隱式遊標，跟顯式定義遊標（執行二）運行速度差不了多少。為方便簡單起見，我通常都是用執行一的方式來寫迴圈操作，資料量較小沒有覺得有什麼，只到有一天，一個海量資料的更新執行了一晚上都沒出來結果，才知道自己犯了多大的錯誤：
執行一：
begin
    for c in (
     select ca.org_code, m.material_code, sum(cd.num) ammount
             from mas_admin.ASC_STOCK_CHANGE_BILL c
         join mas_admin.ASC_STOCK_CHANGE_DETAIL cd on c.STOCK_CHANGE_BILL_ID =
                                     cd.STOCK_CHANGE_BILL_ID
          join mas_admin.asc_materiel m on m.materiel_id = cd.MATERIAL_ID --物料表
          join mas_admin.asc_stock s on s.STOCK_ID = c.STOCK_ID
          join mas_admin.asc_agent ca on ca.ORG_ID = s.org_id
           where c.ENABLE_FLAG = 'Y'
            and s.STOCK_TYPE_ID = 1
             and c.BILL_STATE = 3
            and substr(ca.org_code, 1, 3) <> 'BAK'
           and c.STOCK_CHANGE_REASON_ID=3 -- 3=系統錯誤
           group by ca.org_code, m.material_code
  ) loop
       update asc_ca_stock_good_usable
    set ADJUST_NUM_ERROR=c.ammount
    where ORG_CODE=c.org_code and MATERIAL_CODE=c.MATERIAL_CODE and action_date=curr_date;
      end loop;
    commit; 
    end;
執行二：
 declare
    var_org_code varchar2(50);
    var_material_code varchar2(50);
    var_ammount number;
    cursor c is
       select ca.org_code, m.material_code, sum(cd.num) ammount
             from mas_admin.ASC_STOCK_CHANGE_BILL c
         join mas_admin.ASC_STOCK_CHANGE_DETAIL cd on c.STOCK_CHANGE_BILL_ID =
                                     cd.STOCK_CHANGE_BILL_ID
          join mas_admin.asc_materiel m on m.materiel_id = cd.MATERIAL_ID --物料表
          join mas_admin.asc_stock s on s.STOCK_ID = c.STOCK_ID
          join mas_admin.asc_agent ca on ca.ORG_ID = s.org_id
           where c.ENABLE_FLAG = 'Y'
            and s.STOCK_TYPE_ID = 1
             and c.BILL_STATE = 3
            and substr(ca.org_code, 1, 3) <> 'BAK'
           and c.STOCK_CHANGE_REASON_ID=3 -- 3=系統錯誤
           group by ca.org_code, m.material_code;
begin
    open c;
    loop
       fetch c into var_org_code,var_material_code,var_ammount;
       exit when c%notfound;
       update asc_ca_stock_good_usable
    set ADJUST_NUM_ERROR=var_ammount
    where ORG_CODE=var_org_code and MATERIAL_CODE=var_material_code and action_date=trunc(sysdate);
      commit;
      end loop;    
    end;

執行一10多分鐘算不完，執行二隻需0.2秒