oracle 获取更新数目,plsql 轻松得到insert,delete,update更新记录数

今天刚好有人问我这个问题 记录一下，在plsql中有时做几个dml操作但又想知道整个过程的操作的记录数，那可以用SQL%ROWCOUNT变量轻松实现，无论是不是拼的sql,不多说直接看例子

SQL> exec P_inittab('TESTCNT');

PL/SQL procedure successfully completed.

--这是我自己写的建测试表过程

SQL> DESC TESTCNT;

Name Null? Type

------------------------------ -------- ---------------

ID NUMBER(38)

NAME

SQL> begin

2 for i in 1..100 loop

3 insert into testcnt values(i,i||'anbob');

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> create table testcnt1 as select * from testcnt where 1=0;

Table created.

SQL> l

1 declare

2 v_sql varchar2(200);

3 v_rownum int;

4 begin

5 insert into testcnt1

6 select * from testcnt where rownum<50;

7 v_rownum := SQL%ROWCOUNT;

8 dbms_output.put_line('current insert records:'||v_rownum);

9 v_sql := 'delete testcnt1 where id<10';

10 execute immediate v_sql;

11 v_rownum := SQL%ROWCOUNT;

12 dbms_output.put_line('current delete:'||v_rownum);

13 select count(*) into v_rownum from testcnt1;

14 dbms_output.put_line('current testcnt1 rowcount:'||v_rownum);

15* end;

---------------下面是执行结果

current insert records:49

current delete:9

current testcnt1 rowcount:40

PL/SQL procedure successfully completed.

note:sql%rowcount在oracle与sql server中通用；

打赏

微信扫一扫，打赏作者吧～