java_Oralce

时间：2015-11-28 01:04:04 阅读：292 评论：0 收藏：0 [点我收藏+]

简单范例

create or replace procedure delete_table
is
i number(10);
begin
  for x in (select * from emp where DEPTNO like ‘a%‘)
  loop
      delete emp where emp.id = x.id
      i:=i+1;
      if i>1000 then
         commit;
         i:=0;
      end if;
  end loop;
exception
    when others then
         dbms_out.put_line(sqlcode);
         rollback;
end delete_table;

　　Oracle中删除超过50w条记录的数据，如果直接使用delete，效率就严重受到了影响。那么首先我们需要了解对于这个表的数据，我们到底是全部删除，还是部分删除。这里有三个关键字我们需要注意：truncate,delete,drop，他们之间的异同点可以参考这篇文章——truncate,delete,drop的异同点。

　　全部删除，不保留数据结构就直接drop最好。如果是部分删除，一般就这样一些办法：

　　1. 如果删除的数据是大部分，分段提交删除的数据。

create or replace procedure
delete_table is i number(10);
begin
for x in (select * from emp where DEPTNO like ‘a%’)
loop
delete emp where emp.id = x.id i:=i+1;
if i>1000 then
commit;
i:=0;
end if;
end loop;
exception when others then dbms_out.put_line(sqlcode);
rollback;
end delete_table;

　　或者

–每500条数据提交一次
DECLARE CNT NUMBER(10):=0;
I NUMBER(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM ep_arrearage_bak
WHERE TO_CHAR(DF_DATE,‘MM’)=’01′;
FOR I IN 1..TRUNC(CNT/500)+1
LOOP
DELETE FROM ep_arrearage_bak WHERE TO_CHAR(DF_DATE,‘MM’)=’01′ ANDROWNUM<=500;
COMMIT;
END LOOP;
END;

　　2、把要保留的数据放在一个临时表里,truncate table原表后再放回来；

create table t_back as select * from t where ….
drop table t;
rename t_back to t;

　　3. 专门使用一个大回滚段，比如定义：undo tablespace 2G

　　4、如果将方法1做一点修改，可以这么做：

有条件的分步删除数据表中的记录

–创建测试表
create table test as select * from dba_objects;

–创建删除表的存储过程
create or replace procedure deleteTab

–插入语句
SQL> insert into test select * from dba_objects;
6374 rows created.
SQL> / 6374 rows created.
SQL> / 6374 rows created.
SQL> commit;
– 创建删除的存储过程
create or replace procedure deleteTab
/**
** Usage: run the script to create the proc deleteTab
**        in SQL*PLUS, type "exec deleteTab(‘Foo’,‘ID>=1000000′,’3000′);"
**        to delete the records in the table "Foo", commit per 3000 records.
**       Condition with default value ’1=1′ and default Commit batch is 10000.
**/
(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 varchar2 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;   /

–执行语句
SQL> exec deleteTab(‘TEST’,‘object_id >0′,’10000′)

java_Oralce

原文：http://www.cnblogs.com/MarchThree/p/5002063.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)