1.Oracle批量提交得必要性
Oracle在给大量数据进行DML操作时,经常会占用大量得内存和temp空间,一旦空间不够就会报错,解决得方法往往是去扩大temp空间得大小。其实我们把一个DML分成多次去commit就能很好得解决这个问题。
2.代码实现
下面以一个insert语句为例,我们需要exec_sql形成得结果数据插入到target_table中,由于结果数据过大,我们采用每1000w条数据commit一次得方式插入到目标表中。这里我们需要注意的是,commit执行的太频繁也会降低语句运行的速度,所以要根据情况来控制每次提交的数据条数。代码如下:
create or replace procedure submit_pro(exec_sql in varchar2 , target_table in varchar2)
as
v_sql VARCHAR2(4000);
begin
v_sql := '
DECLARE CURSOR cur IS SELECT * FROM ('||exec_sql||');
TYPE rec IS TABLE OF '||target_table||'%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT INTO recs LIMIT 10000000;
FORALL i IN 1 .. recs.COUNT
INSERT INTO '||target_table||' VALUES recs (i);
dbms_output.put_line(recs.COUNT);
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
';
EXECUTE IMMEDIATE(v_sql);
end;