CREATE OR REPLACE PROCEDURE "LARGEDATA_INSERT" (ip_table_name in varchar2, --目標(biāo)表
ip_table_column in varchar2, --目標(biāo)字段
ip_table_select in varchar2, --SELECT 查詢(xún)語(yǔ)句
return_result out number --返回的結(jié)果1,表示成功，０表示失敗
) as
--適合大數(shù)據(jù)量的插入模板 create Templates by chenzhoumin 20110614
runTime number;--運(yùn)行總次數(shù)
i number;--當(dāng)前行數(shù)
amount number;--總行數(shù)
s_sql varchar2(10000);--SQL語(yǔ)句
e_sql varchar2(10000);--執(zhí)行SQL語(yǔ)句
countNumber number;--一次插入的數(shù)據(jù)量
begin
--set serveroutput on size 20000000000000
countNumber := 10000;
return_result := 0; --開(kāi)始初始化為0
--核必邏輯內(nèi)容，可根據(jù)具體的業(yè)務(wù)邏輯來(lái)定義,統(tǒng)計(jì)數(shù)據(jù)總行數(shù)
s_sql := 'select count(1) from (' || ip_table_select || ')';
execute immediate s_sql
into amount;
--每100萬(wàn)提交一次
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('結(jié)束');
return;
end;
以上測(cè)試通過(guò)。