oracle存储过程大数据量insert

对于大数据量的insert into select的做法，我个人来说不赞成这样做，现实也没这样做，对此，我通过oracle的存储过程写了个大数据量的insert into select 的分批插入方法，代码如下

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;

以上代码的意思是每100W就向数据库提交一次

转载请标明出处http://writeblog.csdn.net/PostEdit.aspx?entryId=6544640