删除231万数据2-3分钟的过程

最新推荐文章于 2025-08-07 14:59:42 发布

原创最新推荐文章于 2025-08-07 14:59:42 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#delete #action #table #statistics #sql #测试

Oracle 开发专栏收录该内容

128 篇文章

订阅专栏

这个语句根据ROWID

create or replace procedure delBigTab
(
p_TableName       in    varchar2,
p_Condition       in    VARCHAR2
)
AS
type mycur is ref cursor;
        v_cur     mycur;
v_cur_sql VARCHAR2(2000);
l_sql VARCHAR2(2000);
pragma autonomous_transaction;
n_delete number:=0;
Type v_rowid is table of varchar2(100) index by binary_integer;
var_rowid v_rowid;
BEGIN
v_cur_sql :='select rowid from '||p_TableName||' where '||p_Condition||' order by rowid' ;
OPEN v_cur FOR v_cur_sql;
LOOP
FETCH v_cur BULK COLLECT
                INTO var_rowid LIMIT 20000 ;
FORALL i IN 1 .. var_rowid.count
/* DELETE FROM datasync_prc.ax_log_mail WHERE ROWID=var_rowid(i);*/
EXECUTE IMMEDIATE 'delete from '||p_TableName||' where rowid=:1' USING var_rowid(i);
COMMIT;
EXIT WHEN v_cur%NOTFOUND OR v_cur%NOTFOUND IS NULL;
END LOOP;
CLOSE v_cur;
end;

第二个根据rownum 限制数据量来删除

create or replace procedure delete_big_table(pi_table_name in varchar2,pi_condition in varchar2) is
pragma autonomous_transaction;
lv_delete_sql varchar2(2000);
lv_select_sql varchar2(2000);
ln_delete_num number;
type mycur is ref cursor;
        v_select_cur     mycur;
        v_delete_cur     mycur;
begin
   ln_delete_num:=0;
    lv_select_sql:=' select count(1) from '||trim(pi_table_name)||' where '||pi_condition;
    open v_select_cur for lv_select_sql;
       fetch v_select_cur into ln_delete_num;
    close v_select_cur;

    lv_delete_sql:=' DELETE '||trim(pi_table_name)||' where rownum<=:num_count and '||pi_condition;

    while 1=1 loop
          execute immediate lv_delete_sql using 2000;
          ln_delete_num:=ln_delete_num-2000;
          commit;
          EXIT WHEN ln_delete_num <=0;
    end loop;

end delete_big_table;