受PL / SQL中UPDATE影响的行数

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中获取该数字。

6 个解决方案

#1

You use the sql%rowcount variable.

您使用sql%rowcount变量。

You need to call it straight after the statement which you need to find the affected row count for.

您需要在需要查找受影响的行数的语句之后直接调用它。

For example:

DECLARE
    i number;
BEGIN
    UPDATE employees
    SET status = 'fired'
    WHERE name like '%Bloggs';
    i := sql%rowcount;
END;

#2

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命令访问。通过使用noname PL / SQL块,可以实现这一点。

... If anyone has a solution to use it in a SELECT Command, I would be interested.

...如果有人有解决方案在SELECT命令中使用它,我会感兴趣。

#3

alternatively, SQL%ROWCOUNT you can use this within the procedure without any need to declare a variable

或者,SQL%ROWCOUNT可以在过程中使用它,而无需声明变量

#4

SQL%ROWCOUNT can 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%ROWCOUNT is 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

#5

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个客户端更新为6个没有客户端,7个val_cli。没有8 val_cli的客户。没有9 val_cli的客户。 1个客户端更新为10个受影响的总行数更新操作:5

#6

Use the Count(*) analytic function OVER PARTITION BY NULL This will count the total # of rows

使用Count(*)分析函数OVER PARTITION BY NULL这将计算总行数

#1