--插入数据:
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;
|
转:http://www.cndba.cn/account/article/details/129
本文转自 张冲andy 博客园博客,原文链接: http://www.cnblogs.com/andy6/p/5840700.html ,如需转载请自行联系原作者