oracle批量提交更新提交数据,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;