pl/sql batch update
组里有同事说有一个需要大批量跟新数据库中的功能，批量修改50万数据很慢，于是我改造了一下她的代码，从打开游标的单条修改，改成了批量修改。性能得到了满足。function F_updatebankdata( i_Bankbatchs IN T_bankdatacollect.Bankbatchs%type, --批次号 o_returnstr out varchar2)return number--0成功1失败is type detailid_tab is table of varchar2(20); type bankcode_tab is table of varchar2(3); type bankaccount_tab is table of varchar2(50); type bankno_tab is table of varchar2(2); detailids detailid_tab; bankcodes bankcode_tab; bankaccounts bankaccount_tab; banknos bankno_tab; cursor c_T_bankdatadetail is select b.Bankdatadetailid,b.bankcode,b.bankaccount,b.bankno from T_bankdatadetail b where b.bankbatchs = i_Bankbatchs;begin l_effrow := 0; open c_T_bankdatadetail; loop fetch c_T_bankdatadetail bulk collect into detailids,bankcodes,bankaccounts,banknoslimit 10000; exit when detailids.count() = 0; l_effrow := l_effrow + detailids.count(); forall i in detailids.first .. detailids.last update T_bankcyclebills a seta.bankdatadetailid = detailids(i) wherea.bankbatchs = i_Bankbatchs anda.bankcode = bankcodes(i) anda.bankaccount = bankaccounts(i) anda.bankno = banknos(i); commit; end loop; close c_T_bankdatadetail; return 0;exception when others then return 1;end;
页:
[1]