Oracle存储过程删除大批量数据

蓝瑾

参考：http://www.eygle.com/archives/2005/04/oracleoeouaeeae.html
批量删除海量数据通常都是很复杂及缓慢的，方法也很多，但是通常的概念是:分批删除，逐次提交。
下面是我的删除过程，我的数据表可以通过主键删除，测试过Delete和For all两种方法，for all在这里并没有带来性能提高，所以仍然选择了批量直接删除。
操作如下：

--创建日志记录表
create table TB_ARCHIVE_LOG_RUNNING
(
PROC_NAME          VARCHAR2(30)  null,
PROC_DESC          VARCHAR2(255) null,
TABLE_NAME         VARCHAR2(255) null,
REC_TIME           DATE null,
PROC_RESULT        VARCHAR2(30) null
);
--记录日志存储过程
CREATE OR REPLACE PROCEDURE "P_WRITE_RUNLOG" (I_PROC_NAME   IN VARCHAR2,
I_PROC_DESC   IN VARCHAR2,
I_TABLE_NAME  IN VARCHAR2,
I_REC_TIME    IN DATE,
I_PROC_RESULT IN VARCHAR2) IS
BEGIN
INSERT INTO TB_ARCHIVE_LOG_RUNNING
(PROC_NAME, PROC_DESC, TABLE_NAME, REC_TIME, PROC_RESULT)
VALUES
(I_PROC_NAME, I_PROC_DESC, I_TABLE_NAME, I_REC_TIME, I_PROC_RESULT);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO TB_ARCHIVE_LOG_RUNNING
(PROC_NAME, PROC_DESC, TABLE_NAME, REC_TIME, PROC_RESULT)
VALUES
(I_PROC_NAME, I_PROC_DESC, I_TABLE_NAME, I_REC_TIME, '写日志出错');
COMMIT;
END P_WRITE_RUNLOG;
例1：
--删除ID区间数据存储过程
--参数 p_tablename 表名
--参数 p_max  要删除的最大ID
--参数 p_min  要删除的最小ID
CREATE OR REPLACE PROCEDURE DELETE_TABLE
( p_tablename in varchar2,
p_max  in  number,
p_min in  number ) IS
x  number(20);
t_name varchar2(100);
BEGIN
P_WRITE_RUNLOG('deleteBegin','删除数据从：'||p_min||'到：'||p_max,p_tablename,sysdate,'INFO');
x := p_min;
t_name := p_tablename;
while x + 1000 < p_max loop
x := x + 1000;
execute immediate 'delete from '||t_name||' where id < '||x;
commit;
end loop;
P_WRITE_RUNLOG('deleteEnd','删除数据从：'||p_min||'到：'||p_max,p_tablename,sysdate,'INFO');
END DELETE_TABLE;
--执行 参数：表名，最大ID，最小ID
exec DELETE_TABLE('report_netunit_interfaces' ,6000,3000 );
例2：
--删除指定表格p_num条数据
CREATE OR REPLACE PROCEDURE DELETE_TABLE_NUM (
p_tablename in varchar2,
p_num  in  number ) IS
p_min number(20);
p_max number(20);
t_name varchar2(100);
x number(20);
BEGIN
execute immediate 'select min(ID) from '|| p_tablename into p_min;
p_max := p_min + p_num;
P_WRITE_RUNLOG('deleteBegin','删除数据从：'||p_min||'到：'||p_max,p_tablename,sysdate,'INFO');
x := p_min;
t_name := p_tablename;
while x + 1000 < p_max + 1 loop
x := x + 1000;
execute immediate 'delete from '||t_name||' where id < ' || x;
commit;
end loop;
P_WRITE_RUNLOG('deleteEnd','删除数据从：'||p_min||'到：'||p_max,p_tablename,sysdate,'INFO');
END DELETE_TABLE_NUM;
exec DELETE_TABLE_NUM ('report_netunit_interfaces',1000);