分步骤删除大表数据(转自eygle)

最新推荐文章于 2021-04-10 20:18:58 发布

原创最新推荐文章于 2021-04-10 20:18:58 发布 · 241 阅读

0 ·

CC 4.0 BY-SA版权

ORA-Dev 专栏收录该内容

59 篇文章

订阅专栏

本文介绍了一个Oracle存储过程，用于批量删除指定表中的记录。该过程接受表名、删除条件及每次提交的记录数作为参数，并通过循环执行DELETE语句来实现高效的数据清理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create or replace procedure deleteTab
(
p_TableName    in    varchar2,    -- The TableName which you want to delete from
p_Condition    in    varchar2 default '1=1',    -- Delete condition, such as "id>=100000"
p_Count        in    pls_interger default 10000    -- Commit after delete How many records
)
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
   EXECUTE IMMEDIATE
     'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
   USING p_Count;
   if SQL%NOTFOUND then
   exit;
   else
        n_delete:=n_delete + SQL%ROWCOUNT;
   end if;
   commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;
/