oracle 存储过程,该如何处理

oracle 存储过程
insert into ch_flat (colunm1,column2) select column1,column2 from ch_flat@test 
这条语句如何在存储过程中加入循环，插入1W条数据commit

[解决办法]

SQL code

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 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;


[解决办法]
你可以定个变量 查出一万提交一次  170万就是 170次   当变量等于 170的时候 就跳出循环
[解决办法]
bulk collect加limit,再用forall
[解决办法]
探讨

SQL code


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@tes……

[解决办法]
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;
[解决办法]
SQL code
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;
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html