linux复制sql语句自带行数,在动态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