CREATE OR REPLACE PROCEDURE "LARGEDATA_INSERT" (ip_table_name in varchar2, --目标表
ip_table_column in varchar2, --目标字段
ip_table_select in varchar2, --SELECT 查询语句
return_result out number --返回的结果1,表示成功，０表示失败
) as
--适合大数据量的插入模板 create Templates by chenzhoumin 20110614
runTime number;--运行总次数
i number;--当前行数
amount number;--总行数
s_sql varchar2(10000);--SQL语句
e_sql varchar2(10000);--执行SQL语句
countNumber number;--一次插入的数据量
begin
--set serveroutput on size 20000000000000
countNumber := 10000;
return_result := 0; --开始初始化为0
--核必逻辑内容，可根据具体的业务逻辑来定义,统计数据总行数
s_sql := 'select count(1) from (' || ip_table_select || ')';
execute immediate s_sql
into amount;
--每100万提交一次
runTime := amount mod countNumber;
if (runTime > 0) then
runTime := 1 + trunc(amount / countNumber);
end if;
if (runTime = 0) then
runTime := 0 + trunc(amount / countNumber);
end if;
FOR i IN 1 .. runTime LOOP
e_sql := 'insert into '||ip_table_name ||'
('||ip_table_column ||')
select '|| ip_table_column ||'
from
(select selectSec.*, rownum rownumType
from ('|| ip_table_select ||') selectSec
WHERE ROWNUM <= '|| i * countNumber ||')
WHERE rownumType > '||(i - 1) * countNumber;
dbms_output.enable(99999999999999);
dbms_output.put_line(e_sql);
execute immediate e_sql;
--提交
commit;
END LOOP;
return_result := 1;
return;
exception
when others then
return_result := 0;
raise;
dbms_output.enable(99999999999999);
dbms_output.put_line('结束');
return;
end;
以上测试通过。