oracle 获取更新数目,oracle - 受PL / SQL中UPDATE影响的行数

SQL%ROWCOUNT也可以在不分配的情况下使用(至少从Oracle 11g开始)。

只要在当前块中未执行任何操作(更新，删除或插入)，就会将SQL%ROWCOUNT设置为null。 然后它保持受最后一次DML操作影响的行数：

说我们有表CLIENT

create table client (

val_cli integer

,status varchar2(10)

)

/

我们会这样测试：

begin

dbms_output.put_line('Value when entering the block:'||sql%rowcount);

insert into client

select 1, 'void' from dual

union all select 4, 'void' from dual

union all select 1, 'void' from dual

union all select 6, 'void' from dual

union all select 10, 'void' from dual;

dbms_output.put_line('Number of lines affected by previous DML operation:'||sql%rowcount);

for val in 1..10

loop

update client set status = 'updated' where val_cli = val;

if sql%rowcount = 0 then

dbms_output.put_line('no client with '||val||' val_cli.');

elsif sql%rowcount = 1 then

dbms_output.put_line(sql%rowcount||' client updated for '||val);

else -- >1

dbms_output.put_line(sql%rowcount||' clients updated for '||val);

end if;

end loop;

end;

导致：

Value when entering the block:

Number of lines affected by previous DML operation:5

2 clients updated for 1

no client with 2 val_cli.

no client with 3 val_cli.

1 client updated for 4

no client with 5 val_cli.

1 client updated for 6

no client with 7 val_cli.

no client with 8 val_cli.

no client with 9 val_cli.

1 client updated for 10