利用隐式游标分批删除数据的procedure
来源:互联网 发布:淘宝比较知名的包包 编辑:程序博客网 时间:2024/04/28 16:30
刚一个人在南京过完了元宵佳节,和同学去了一趟书店帮他选了一本三思的涂抹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;
- 利用隐式游标分批删除数据的procedure
- Oracle利用存储过程批量insert造数据和分批提交删除的例子
- 数据的分批加载
- huawei-大量数据分批处理思想,自定义游标,游标开关
- 存储过程中如何利用游标分批提交
- 【ORACLE】大数据快速更新(游标分批提交)
- mysql 利用游标循环删除数据报错:No data
- ListView的分批加载数据
- oracle游标的更新与删除数据
- 利用游标update数据
- Oracle 下 1万和5万分批删除数据的性能差异
- 删除表的重复记录(利用游标)
- 删除正在运行的procedure
- Oracle 参数 游标[游标更新删除数据]
- Oracle 参数 游标[游标更新删除数据]
- Android:ListView数据的分批加载
- 119.ListView数据的分批显示
- 游标对应的表的数据删除时,如何保持游标与表数据的一致性
- OCP-1Z0-053-V12.02-200题
- 目录和目录项
- hadoop最基本配置及build(ant代理)
- 虚拟化技术简介
- SWT/Jface Text Combo 添加代码辅助功能
- 利用隐式游标分批删除数据的procedure
- 关于glDrawArrays的第一个参数
- vc 运行库
- 交叉编译器arm-linux-gcc-4.4.3安装
- linux中tomcat内存溢出解决办法
- ubuntu旧版本源失效的处理方法
- 关于对《Spring Security3》翻译 (第一章 - 第三章)
- NFS for LINUX
- scp免密码