存储过程 100w提交

2024-03-14 13:10:16

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(5000);
begin
return_result := 0; --开始初始化为0
--核必逻辑内容，可根据具体的业务逻辑来定义
s_sql := 'select count(1) from (' || ip_table_select || ')';
execute immediate s_sql
into amount;
--每100万提交一次
runTime := amount mod 1000000;
if (runTime > 0) then
runTime := 1 + trunc(amount / 1000000);
end if;
if (runTime = 0) then
runTime := 0 + trunc(amount / 1000000);
end if;
FOR i IN 1 .. runTime LOOP
execute immediate '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 * 1000000 || ')
WHERE rownumType > ' || (i - 1) * 1000000;
--提交
commit;
END LOOP;
return_result := 1;
dbms_output.put_line('结束' || to_char(sysdate, 'yyyymmddhh24miss'));
return;
exception
when others then
return_result := 0;
raise;
return;
end;