blocks|key|756462|text|您可以使用sql%25rowcount变量。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|756463|您需要在语句后面直接调用它，您需要找到受影响的行数。|756464|例如：|756465|set+serveroutput+ON;+
DECLARE+
++++i+NUMBER;+
BEGIN+
++++UPDATE+employees+
++++SET++++status+=+'fired'+
++++WHERE++name+LIKE+'%25Bloggs';+
++++i+:=+SQL%25rowcount;+
++++--note+that+assignment+has+to+precede+COMMIT
++++COMMIT;+
++++dbms_output.Put_line(i);+
END;+|code-block|syntax|javascript|756466|entityMap^0|5|C|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|U|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|V|8|@]|D|@]|E|$M|N]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

You use the <code>sql%rowcount</code> variable.

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

For example:

<pre><code>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; 
</code></pre>

blocks|key|3975634|text|或者，您可以在过程中使用SQL%25ROWCOUNT，而不需要声明变量|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3975635|entityMap^0|C|C|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

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

blocks|key|3975664|text|对于那些想要从普通命令中获得结果的人来说，解决方案可能是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3975665|begin
++DBMS_OUTPUT.PUT_LINE(TO_Char(SQL%25ROWCOUNT)%7C%7C'+rows+affected.');
end;|code-block|syntax|javascript|3975666|基本问题是SQL%25ROWCOUNT是一个PL/SQL变量(或函数)，不能通过SQL命令直接访问。通过使用noname+PL/SQL块，可以实现这一点。|3975667|..。如果任何人有在SELECT命令中使用它的解决方案，我会很感兴趣。|3975668|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

For those who want the results from a plain command, the solution could be:

<pre><code>begin
 DBMS_OUTPUT.PUT_LINE(TO_Char(SQL%ROWCOUNT)||' rows affected.');
end;
</code></pre>

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.

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

blocks|key|4819466|text|使用Count(*)分析函数OVER+PARTITION+BY+NULL这将计算总行数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4819467|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|1236932|text|SQL%25ROWCOUNT也可以在没有分配的情况下使用(至少在Oracle11g中是这样)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1236933|只要在当前块中没有执行任何操作(更新、删除或插入)，SQL%25ROWCOUNT就会设置为null。然后，它停留在受最后一次DML操作影响的行数：|1236934|假设我们有表客户端|1236935|create+table+client+(
++val_cli+integer
+,status+varchar2(10)
)
/|code-block|syntax|javascript|1236936|我们会这样测试它：|1236937|begin
++dbms_output.put_line('Value+when+entering+the+block:'%7C%7Csql%25rowcount);

++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:'%7C%7Csql%25rowcount);

++for+val+in+1..10
++++loop
++++++update+client+set+status+=+'updated'+where+val_cli+=+val;
++++++if+sql%25rowcount+=+0+then
++++++++dbms_output.put_line('no+client+with+'%7C%7Cval%7C%7C'+val_cli.');
++++++elsif+sql%25rowcount+=+1+then
++++++++dbms_output.put_line(sql%25rowcount%7C%7C'+client+updated+for+'%7C%7Cval);
++++++else+--+>1
++++++++dbms_output.put_line(sql%25rowcount%7C%7C'+clients+updated+for+'%7C%7Cval);
++++++end+if;
++end+loop;++
end;|1236938|结果是：|1236939|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|1236940|entityMap^0|0|C|0|Q|C|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|14|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|15|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|16|8|@]|D|@]|E|$]]|$1|Q|3|R|5|L|7|17|8|@]|D|@]|E|$M|N]]|$1|S|3|T|5|6|7|18|8|@]|D|@]|E|$]]|$1|U|3|V|5|L|7|19|8|@]|D|@]|E|$M|N]]|$1|W|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|X|$]]

<code>SQL%ROWCOUNT</code> can also be used without being assigned (at least from Oracle 11g).

As long as no operation (updates, deletes or inserts) has been performed within the current block, <code>SQL%ROWCOUNT</code> is set to null. Then it stays with the number of line affected by the last DML operation:

say we have table CLIENT

<pre><code>create table client (
 val_cli integer
 ,status varchar2(10)
)
/
</code></pre>

We would test it this way:

<pre><code>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 -- &gt;1
 dbms_output.put_line(sql%rowcount||' clients updated for '||val);
 end if;
 end loop; 
end;
</code></pre>

Resulting in:

<pre><code>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
</code></pre>

blocks|key|1236951|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1236952|请试试这个..|blockquote|1236953|1236954|1236955|1236956|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%25rowcount+=+0+then
++++++++dbms_output.put_line('no+client+with+'%7C%7Cval%7C%7C'+val_cli.');
++++++else
++++++++dbms_output.put_line(sql%25rowcount%7C%7C'+client+updated+for+'%7C%7Cval);
++++++++counter+:=+counter+%2B+sql%25rowcount;
++++++end+if;
++end+loop;
+++dbms_output.put_line('Number+of+total+lines+affected+update+operation:+'%7C%7Ccounter);
end;

---------------------
select+*+from+client;

--------------------------------------------------------|code-block|syntax|javascript|1236957|1236958|结果将如下所示：|1236959|1236960|1236961|1236962|%252客户端已为%251更新|1236963|没有具有2个val_cli的客户端。|1236964|没有具有3个val_cli的客户端。|1236965|%251客户端已为%254更新|1236966|没有具有5+val_cli的客户端。|1236967|%251客户端已为%256更新|1236968|没有具有7+val_cli的客户端。|1236969|没有具有8+val_cli的客户端。|1236970|没有具有9+val_cli的客户端。|1236971|1个客户端已更新为10|1236972|受更新操作影响的总行数:5|1236973|1236974|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|1G|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|1H|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|1I|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|1J|7|@]|8|@]|9|$]]|$1|F|3|-4|4|5|6|1K|7|@]|8|@]|9|$]]|$1|G|3|H|4|I|6|1L|7|@]|8|@]|9|$J|K]]|$1|L|3|-4|4|5|6|1M|7|@]|8|@]|9|$]]|$1|M|3|N|4|C|6|1N|7|@]|8|@]|9|$]]|$1|O|3|-4|4|5|6|1O|7|@]|8|@]|9|$]]|$1|P|3|-4|4|5|6|1P|7|@]|8|@]|9|$]]|$1|Q|3|-4|4|5|6|1Q|7|@]|8|@]|9|$]]|$1|R|3|S|4|5|6|1R|7|@]|8|@]|9|$]]|$1|T|3|U|4|5|6|1S|7|@]|8|@]|9|$]]|$1|V|3|W|4|5|6|1T|7|@]|8|@]|9|$]]|$1|X|3|Y|4|5|6|1U|7|@]|8|@]|9|$]]|$1|Z|3|10|4|5|6|1V|7|@]|8|@]|9|$]]|$1|11|3|12|4|5|6|1W|7|@]|8|@]|9|$]]|$1|13|3|14|4|5|6|1X|7|@]|8|@]|9|$]]|$1|15|3|16|4|5|6|1Y|7|@]|8|@]|9|$]]|$1|17|3|18|4|5|6|1Z|7|@]|8|@]|9|$]]|$1|19|3|1A|4|5|6|20|7|@]|8|@]|9|$]]|$1|1B|3|1C|4|5|6|21|7|@]|8|@]|9|$]]|$1|1D|3|-4|4|5|6|22|7|@]|8|@]|9|$]]|$1|1E|3|-4|4|5|6|23|7|@]|8|@]|9|$]]]|1F|$]]

<blockquote>
 Please try this one..
</blockquote>

<hr>

<pre><code>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;

--------------------------------------------------------
</code></pre>

<blockquote>
 Result will be like below:
</blockquote>

<hr>

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 

<hr>

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.

Number of rows affected by an UPDATE in PL/SQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个PL/SQL函数(在Oracle10g上运行)，其中我更新了一些行。有没有办法找出有多少行受到更新的影响？当手动执行查询时，它会告诉我有多少行受到影响，我希望在PL/SQL中获得这个数字。

问在PL/SQL中受UPDATE影响的行数
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在PL/SQL中受UPDATE影响的行数EN