发文号的括号怎么打: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;