Oracle PL/SQL - SQL%ROWCOUNT Attribute: How Many Rows Were Affected?

Introduction

SQL%ROWCOUNT returns:

NULL if no SELECT or DML statement has run
Otherwise, the number of rows returned by a SELECT statement or affected by a DML statement

If the number of rows exceeds the maximum value for a PLS_INTEGER, then SQL%ROWCOUNT returns a negative value.

The following code uses SQL%ROWCOUNT to determine the number of rows that were deleted.

Demo

SQL>
SQL> drop table emp;

Table dropped.--  w  w  w .j av a2 s .com

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL>
SQL> DROP TABLE emp_temp;

Table dropped.

SQL> CREATE TABLE emp_temp AS
  2    SELECT * FROM emp;
SQL>
SQL> DECLARE
  2    mgr_no NUMBER(6) := 122;
  3  BEGIN
  4    DELETE FROM emp_temp WHERE manager_id = mgr_no;
  5    DBMS_OUTPUT.PUT_LINE
  6      ('Number of emp deleted: ' || TO_CHAR(SQL%ROWCOUNT));
  7  END;
  8  /
Number of emp deleted: 0

PL/SQL procedure successfully completed.

SQL>