Oracle 高效的SQL：oracle存储过程大数据量insert,分批提交

oracle存储过程大数据量insert,分批提交（转）

http://www.dataguru.cn/thread-139511-1-1.html

declare

vv_cur_sql varchar2(4000);

vv_table varchar2(32);

vv_original_table varchar2(32);

type t_cursor is ref cursor;

cur t_cursor;

type rec ia table of t_orig_ref_cfg_tab_bak%ROWTYPE;

recs rec;

begin

vv_table:='t_orig_ref_cfg_tab'

vv_original_table:='tb_dic_region';

vv_cur_sql:='select * from '||vv_table||' t where t.original_table='''||vv_original_table||'''';

 

open cur for vv_cur_sql;

 

while (true) loop

  fetch cur bulk collect into recs limit 2;

  forall i in 1..recs.COUNT

    insert into t_orig_ref_cfg_tab_bak values recs(i);

    commit;

    exit when cur%NOTFOUND;

end loop;

 

close cur;

end;

/