--插入数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | declare --定义cursor把数据全部读入内存中 cursor cur is select t.pk_account_detail_id from Ecm_Member_Account_Details t; --自定义集合类型 type rec is table of cur%rowtype; recs rec; begin open cur; while (true) loop fetch cur bulk collect into recs limit 10000;--10000行执行一次游标读取操作 --把数据逐条插入 forall i in 1 .. recs.count Insert /*+parllel(t,4)*/ Into test_a Select * from test_b t Where t.pk_payrefu_id=Recs(i).pk_payrefu_id; commit; exit when cur%notfound; end loop; close cur; end; |
--更新操作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | declare type rowid_list is table of Number index by binary_integer; -- i number; rowid_infos rowid_list; cursor c_rowids is select t.pk_account_detail_id from Ecm_Member_Account_Details t ; begin open c_rowids; loop fetch c_rowids bulk collect into rowid_infos limit 10000; Forall i in 1..rowid_infos.count Update /*+parllel(t,4)*/ Ecm_Member_Account_Details Tt Set Tt.Fk_Order_Class_Id = 1 where tt.pk_account_detail_id=rowid_infos(i); commit; exit when rowid_infos.count<10000; end loop; close c_rowids; end; |
--删除操作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | declare --定义cursor把数据全部读入内存中 cursor cur is select t.id from test_a t; --自定义集合类型 type rec is table of cur%rowtype; recs rec; begin open cur; while (true) loop fetch cur bulk collect into recs limit 10000;--10000行执行一次游标读取操作 --把数据逐条插入 forall i in 1 .. recs.count delete From Ecm_Order_Payrefus t Where t.pk_id=Recs(i).id; commit; exit when cur%notfound; end loop; close cur; end; |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31448824/viewspace-2139355/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31448824/viewspace-2139355/