plsql 轻松得到insert,delete,update更新记录数

原创 Anbob 2012-02-14

505

今天刚好有人问我这个问题记录一下，在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中通用；

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

plsql 轻松得到insert,delete,update更新记录数

评论