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

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursor Attributes

Introduction

%ROWCOUNT returns the following values:

  • Zero after the explicit cursor is opened but before the first fetch
  • Otherwise, the number of rows fetched (a PLS_INTEGER)

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

The following code numbers and prints the rows fetched and prints a message after fetching the fifth row.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   ww  w  .j a v  a  2s  .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-2000', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-2010', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL>
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT last_name FROM emp;
  4
  5    name  emp.last_name%TYPE;
  6  BEGIN
  7    OPEN c1;
  8    LOOP
  9      FETCH c1 INTO name;
 10      EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
 11      DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name);
 12      IF c1%ROWCOUNT = 5 THEN
 13         DBMS_OUTPUT.PUT_LINE('--- Fetched 5th row ---');
 14      END IF;
 15    END LOOP;
 16    CLOSE c1;
 17  END;
 18  /
1. King
2. Lee

PL/SQL procedure successfully completed.

SQL>

Related Topic