在动态SQL语句中获得被处理的记录行数

在动态SQL语句中获得被处理的记录行数，可以简单的通过 SQL%ROWCOUNT 系统变量来实现。请参考下面的例子：
create table test (id number,name varchar2(10));
set serveroutput on;
DECLARE
  V_ROWCOUNT NUMBER;
  V_SQL      VARCHAR2(100);
  V_ID       test.ID%TYPE;
  V_NAME     test.NAME%TYPE;
BEGIN
  -- INSERT A ROW
  V_SQL := 'INSERT INTO TEST VALUES (:a1,:b1)';
  V_ID := 1;
  V_NAME := 'NAME1';
  EXECUTE IMMEDIATE V_SQL USING V_ID,V_NAME;
  V_ROWCOUNT := sql%rowcount;
  DBMS_OUTPUT.PUT_LINE(V_ROWCOUNT || 'row Inserted!' );
 
  V_ID := 2;
  V_NAME := 'NAME2';
  EXECUTE IMMEDIATE V_SQL USING V_ID,V_NAME;
  V_ROWCOUNT := sql%rowcount;
  DBMS_OUTPUT.PUT_LINE(V_ROWCOUNT || 'row Inserted!' );
  COMMIT;
  
  -- INSERT MANY ROWS
  V_SQL := 'INSERT INTO TEST SELECT * FROM test';
  EXECUTE IMMEDIATE V_SQL ;
  V_ROWCOUNT := sql%rowcount;
  DBMS_OUTPUT.PUT_LINE(V_ROWCOUNT || 'rows Inserted!' );
  COMMIT;
   
  --UPDATE
  V_NAME := 'ALLNAME';
  V_SQL := 'UPDATE  test  SET  name = :a1';
  EXECUTE IMMEDIATE V_SQL USING V_NAME;
  V_ROWCOUNT := sql%rowcount;
  DBMS_OUTPUT.PUT_LINE(V_ROWCOUNT || 'rows Updated!' );
  COMMIT;
   
  --DELETE
  V_ID := 1;
  V_SQL := 'DELETE FROM  TEST  WHERE  ID = :a1';
  EXECUTE IMMEDIATE V_SQL USING V_ID;
  V_ROWCOUNT := sql%rowcount;
  DBMS_OUTPUT.PUT_LINE(V_ROWCOUNT || 'rows Deleted!' );
  COMMIT;
END;

--执行结果：
1row Inserted!
1row Inserted!
2rows Inserted!
4rows Updated!
2rows Deleted!

PL/SQL procedure successfully completed

Executed in 0.03 seconds

SQL> SELECT * FROM test;

        ID NAME
---------- ----------
         2 ALLNAME
         2 ALLNAME

Executed in 0.02 seconds

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15779287/viewspace-675632/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15779287/viewspace-675632/