oracle PL/SQL 中受 UPDATE 影响的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/861983/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Number of rows affected by an UPDATE in PL/SQL
提问by Thomas L?tzer
I have a PL/SQL function (running on Oracle 10g) in which I update some rows. Is there a way to find out how many rows were affected by the UPDATE? When executing the query manually it tells me how many rows were affected, I want to get that number in PL/SQL.
我有一个 PL/SQL 函数（在 Oracle 10g 上运行），我在其中更新了一些行。有没有办法找出受 UPDATE 影响的行数？手动执行查询时，它会告诉我有多少行受到影响，我想在 PL/SQL 中获取该数字。
回答by Clive
You use the sql%rowcountvariable.
你使用sql%rowcount变量。
You need to call it straight after the statement which you need to find the affected row count for.
您需要在需要查找受影响行数的语句之后直接调用它。
For example:
例如：
set serveroutput ON;
DECLARE
i NUMBER;
BEGIN
UPDATE employees
SET status = 'fired'
WHERE name LIKE '%Bloggs';
i := SQL%rowcount;
--note that assignment has to precede COMMIT
COMMIT;
dbms_output.Put_line(i);
END;
回答by CLS
For those who want the results from a plain command, the solution could be:
对于那些想要从简单命令中获得结果的人，解决方案可能是：
begin
DBMS_OUTPUT.PUT_LINE(TO_Char(SQL%ROWCOUNT)||' rows affected.');
end;
The basic problem is that SQL%ROWCOUNT is a PL/SQL variable (or function), and cannot be directly accessed from an SQL command. By using a noname PL/SQL block, this can be achieved.
基本问题是 SQL%ROWCOUNT 是一个 PL/SQL 变量（或函数），不能从 SQL 命令直接访问。通过使用无名 PL/SQL 块，可以实现这一点。
... If anyone has a solution to use it in a SELECT Command, I would be interested.
...如果有人有在 SELECT 命令中使用它的解决方案，我会感兴趣。
回答by Ali H
alternatively, SQL%ROWCOUNTyou can use this within the procedure without any need to declare a variable
或者，SQL%ROWCOUNT您可以在过程中使用它而无需声明变量
回答by J. Chomel
SQL%ROWCOUNTcan also be used without being assigned (at least from Oracle 11g).
SQL%ROWCOUNT也可以在未分配的情况下使用（至少从Oracle 11g 开始）。
As long as no operation (updates, deletes or inserts) has been performed within the current block, SQL%ROWCOUNTis set to null. Then it stays with the number of line affected by the last DML operation:
只要在当前块内没有执行任何操作（更新、删除或插入），SQL%ROWCOUNT就设置为 null。然后它保留受上次 DML 操作影响的行数：
say we have table CLIENT
说我们有表 CLIENT
create table client (
val_cli integer
,status varchar2(10)
)
/
We would test it this way:
我们会这样测试：
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;
Resulting in:
导致：
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
回答by Arun Sundriyal
Please try this one..
请试试这个..
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;
--------------------------------------------------------
Result will be like below:
结果将如下所示：
2 client 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
Number of total lines affected update operation: 5
2 客户端更新为 1
无客户端 2 val_cli。
没有 3 val_cli 的客户端。
1 个客户端更新为 4 个
没有客户端，带有 5 个 val_cli。
1 个客户
端使用 7 val_cli更新为 6 个没有客户端。
没有 8 val_cli 的客户端。
没有 9 val_cli 的客户端。
1 个客户端更新了 10
行影响更新操作的总行数：5
回答by guest
Use the Count(*) analytic function OVER PARTITION BY NULL This will count the total # of rows
使用 Count(*) 分析函数 OVER PARTITION BY NULL 这将计算总行数
