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
215
请试一下..
create table client (
val_cli integer
,status varchar2(10)
);
---------------------
begin
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;
end;
---------------------
select * from client;
---------------------
declare
counter integer := 0;
begin
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.');
else
dbms_output.put_line(sql%rowcount||' client updated for '||val);
counter := counter + sql%rowcount;
end if;
end loop;
dbms_output.put_line('Number of total lines affected update operation: '||counter);
end;
---------------------
select * from client;
--------------------------------------------------------
6 回答
您使用
sql%rowcount
变量 .您需要在需要查找受影响的行数的语句之后直接调用它 .
例如:
对于那些想要普通命令的结果的人来说,解决方案可能是:
基本问题是SQL%ROWCOUNT是PL / SQL变量(或函数),不能直接从SQL命令访问 . 通过使用noname PL / SQL块,可以实现这一点 .
...如果有人有解决方案在SELECT命令中使用它,我会感兴趣 .
或者,
SQL%ROWCOUNT
您可以在过程中使用它而无需声明变量SQL%ROWCOUNT
也可以在未分配的情况下使用(至少从Oracle 11g开始) .只要在当前块中未执行任何操作(更新,删除或插入),
SQL%ROWCOUNT
就会设置为null . 然后它保持受最后一次DML操作影响的行数:说我们有表CLIENT
我们会这样测试:
导致:
2客户更新为1
没有2 val_cli的客户 .
没有3 val_cli的客户 .
1个客户更新为4
没有5 val_cli的客户 .
1个客户更新为6
没有7 val_cli的客户 .
没有8 val_cli的客户 .
没有9 val_cli的客户 .
1个客户更新了10个
受影响更新操作的总行数:5
使用Count(*)分析函数OVER PARTITION BY NULL这将计算总行数