ANBOB™

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

打赏

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