最近项目需要把其他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;