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秒
