Oracle批量导入数据（附带脚本）

最近项目需要把其他Oracle库的表数据导入项目库，数据量在十亿级别，如果用ETL工具导入测试比较慢，考虑使用存储过程的方式，批量导入，下面是脚本

create or replace procedure p_insert_data(s_table in varchar2 , t_table in varchar2,n in int)
as
v_sql varchar2(4000);
begin
v_sql := '
declare cursor cur is select * from ('||s_table||');
  type rec is table of '||t_table||'%rowtype;
  recs rec;
begin
  open cur;
  while (true) loop
    fetch cur bulk collect into recs limit '||n||';
    forall i in 1 .. recs.count
      insert into '||t_table||' values recs (i);
      dbms_output.put_line(recs.count);
    commit;
    exit when cur%notfound;
  end loop;
  close cur;
end;
';
execute immediate(v_sql);

end;