利用隐式游标分批删除数据的procedure

利用隐式游标分批删除数据的procedure

刚一个人在南京过完了元宵佳节，和同学去了一趟书店帮他选了一本三思的涂抹oracle，个人来看对他还是很不错的，不然直接看eygle的深入浅出 dba3手记那是太吃力了，涂抹oracle还是很不错的，至少能认认真真把那本书啃完是非常受益的。

即将迎来自己的未知的第二份工作，当然首先必须跟oracle相关，如果可以有管理类的工作那最好了，不过自己在技术和经验方面还存在远远不足，虽然这一年来自己很努力的去肯文档 做测试但是知识漏洞还是太多太多。

不瞎扯了，同事这些天需要删除大量的数据，如果直接删除对回滚段冲击太大，一个运行半天delete很可能因为ora-01555而以悲剧告终。分批批量删除提交则可以很有效的避免ora-01555错误而高效的删除大量数据。

Sql>Select count(*) from test_objects01;

Count(*)

3330987

Sql>select count(*) from test_objects01 where object_id<63792;

Count(*)

697867

此时需要只保留object_id>=63792,当然由于删除数据较多，可以利用create table test01保留不需要删除的数据，然后truncate原表段，再insert到test_object01中。

不过需要保留的数据依然占用很大比例，就不适用了！还是利用游标的特性来对批量删除进行控制吧！

Create or replace procedure del_proc01

As

Begin

loop

Delete from test01 where object_id<=63792 and rownum<=100000;

Exit when sql%notfound;--dml语句中隐式游标属性进行控制

Commit;

End loop;

Commit;

End;

/

注意dml语句中才存在隐式游标，利用常用的sql%found sql%notfound sql%rowcount sql%isopen(隐式游标中isopen查询任何时间都返回false，所以一般不用于控制循环)。

同样也可以利用sql%rowcount来控制循环。

Create or replace procedure del_proc02

As

Begin

Loop

Delete from test01 where object_id<=63792 and rownum<=100000;

Exit when sql%rowcount=0;

Commit;

End loop;

Commit;

End;

/

http://www.eygle.com/archives/2005/04/oracleoeouaeeae.html

这里摘要以下eygle的blog关于批量删除的一个procedure，和上述自己所写procedure加入了参数控制table_name 、where检索条件、rownum批量删除行数，然后后续输出删除的行数。（关于for all删除的可实现性还未有清晰的理解）

SQL> create or replace procedure delBigTab

  2  (

  3    p_TableName       in    varchar2,

  4    p_Condition       in    varchar2,

  5    p_Count        in    varchar2

  6  )

  7  as

  8   pragma autonomous_transaction;

  9   n_delete number:=0;

 10  begin

 11   while 1=1 loop

 12     EXECUTE IMMEDIATE

 13       'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'

 14     USING p_Count;

 15     if SQL%NOTFOUND then

 16        exit;

 17     else

 18              n_delete:=n_delete + SQL%ROWCOUNT;

 19     end if;

 20     commit;

 21   end loop;

 22   commit;

 23   DBMS_OUTPUT.PUT_LINE('Finished!');

 24   DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');

 25  end;

 26  /

======================================================================

oracle批量update和delete

--insert操作无论是1条或者多条，一次产生一条undo数据
SQL> create table a (b number, c varchar2(30));

Table created

SQL> insert into a values (1,'zhangsan');

1 row inserted

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
1 1

SQL> insert into a select rownum,rownum from dual connect by rownum<=50;

50 rows inserted

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
1 2

--delete操作产生的undo条数取决于delete操作本身影响的记录数SQL> delete from a;

51 rows deleted

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
1 53

SQL> insert into a select rownum,rownum from dual connect by rownum<=50;

50 rows inserted

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
1 54

--update操作产生的undo条数取决于被update的数据的原值和新值存在差异的记录数量
SQL> update a set c=rownum;

50 rows updated

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
2 57

SQL> update a set c=rownum+1000;

50 rows updated

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
2 107

SQL> update a set c=rownum;

50 rows updated

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
3 157

SQL> update a set c=rownum;

50 rows updated

SQL>
SQL> select used_ublk,used_urec from v$session s, v$transaction t
2 where s.audsid=sys_context('userenv', 'sessionid') and s.taddr = t.addr;

USED_UBLK USED_UREC
---------- ----------
3 160

测试中表没有主键,如果有主键,所有操作都会增加一条undo数据.在删除或者update大量数据时候会产生大量undo,
所以建议这两种操作进行分批提交.

--批量更新

create or replace procedure updateattr(in_number number)

is
cursor attr is
select *
from src_attr
where status = 'Y'
and mod(row_id, 10) = in_number;

begin

for rec in attr loop

update src_attr
set status = 'N'
where status = 'Y'
and rownum <= 1000000;
commit;
end loop;
commit;

end updateattr;

--批量删除

create or replace procedure delBigTab--分批提交删除
(
p_TableName in varchar2,--表名
p_Condition in varchar2,--条件
p_Count in varchar2--每批提交的条数
)
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;