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;
以上測試通過。