begin -- Test statements here for i in 1..10000 loop insert into ch_flat (colunm1,column2) select column1,column2 from ch_flat@test; end loop; commit; end;
你这不对,你这样等于循环insert语句,我需要是在执行insert 语句过程中查找出1W条提交一次,insert into select总共有170W数据
declare -- Local variables herebegin -- Test statements here for rec in (select column1, column2 from ch_flat@test where rownum < 10000) loop insert into ch_flat (colunm1, column2) values (rec.column1, rec.column2); end loop; commit; end; 这样??
declare -- Local variables here
begin -- Test statements here for i in 1..10000 loop insert into ch_flat (colunm1,column2) select column1,column2 from ch_flat@test; end loop; if i= 100000 then commit; else null; end;
bulk collect加limit,再用forall
8楼可以不过就是代码多慢。 declare v_cmt_cnt number:=0; begin for c in(select rowid rid,rownum r from ch_flat@test) loop insert into ch_flat (colunm1,column2) select column1,column2 from ch_flat@test where rowid=c.rid; if(mod(c.r,10000)=0)then commit; v_cmt_cnt:=v_cmt_cnt+1; end if; end loop; commit; dbms_output.put_line('commited times:'||v_cmt_cnt); end;
declare n_count number:=0; CURSOR c IS SELECT colunm1,column2 FROM ch_flat; begin for my_c in c loop insert into ch_flat (colunm1,column2) select my_c.column1,my_c.column2; n_count:=n_count+1; if mod(n.count,10000)=0 then commit; end if; end loop; commit; end;
写漏了from dual,现在加上 declare n_count number:=0; CURSOR c IS SELECT colunm1,column2 FROM ch_flat; begin for my_c in c loop insert into ch_flat (colunm1,column2) select my_c.column1,my_c.column2 from dual; n_count:=n_count+1; if mod(n.count,10000)=0 then commit; end if; end loop; commit; end;
declare
-- Local variables here
begin
-- Test statements here
for i in 1..10000
loop
insert into ch_flat (colunm1,column2) select column1,column2 from ch_flat@test;
end loop;
commit;
end;
declare
-- Local variables herebegin
-- Test statements here
for rec in (select column1, column2 from ch_flat@test where rownum < 10000) loop
insert into ch_flat
(colunm1, column2)
values
(rec.column1, rec.column2);
end loop; commit;
end;
这样??
-- Local variables here
begin
-- Test statements here
for i in 1..10000
loop
insert into ch_flat (colunm1,column2) select column1,column2 from ch_flat@test;
end loop;
if i= 100000
then
commit;
else null;
end;
declare
v_cmt_cnt number:=0;
begin
for c in(select rowid rid,rownum r from ch_flat@test)
loop
insert into ch_flat (colunm1,column2) select column1,column2 from ch_flat@test where rowid=c.rid;
if(mod(c.r,10000)=0)then
commit;
v_cmt_cnt:=v_cmt_cnt+1;
end if;
end loop;
commit;
dbms_output.put_line('commited times:'||v_cmt_cnt);
end;
declare
n_count number:=0;
CURSOR c IS SELECT colunm1,column2 FROM ch_flat; begin
for my_c in c
loop
insert into ch_flat (colunm1,column2) select my_c.column1,my_c.column2;
n_count:=n_count+1;
if mod(n.count,10000)=0 then
commit;
end if;
end loop;
commit;
end;
declare
n_count number:=0;
CURSOR c IS SELECT colunm1,column2 FROM ch_flat; begin
for my_c in c
loop
insert into ch_flat (colunm1,column2) select my_c.column1,my_c.column2 from dual;
n_count:=n_count+1;
if mod(n.count,10000)=0 then
commit;
end if;
end loop;
commit;
end;