LAST_ROW_COUNT procedure - return the cumulative number of rows fetched
The LAST_ROW_COUNT procedure returns the number of rows that have been fetched.
Syntax
Parameters
- ret
- An output argument of type INTEGER that returns the number of rows that have been fetched so far in the current session. A call to DBMS_SQL.PARSE resets the counter.
Authorization
EXECUTE privilege on the DBMS_SQL module.
Usage notes
This procedure can be invoked using function invocation syntax in a PL/SQL assignment statement.
Examples
Example 1: The following
example uses the LAST_ROW_COUNT procedure to display the total number
of rows fetched in the query.
SET SERVEROUTPUT ON@
CREATE TABLE emp (
empno DECIMAL(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr DECIMAL(4),
hiredate TIMESTAMP(0),
sal DECIMAL(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm DECIMAL(7,2) )@
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL)@
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300)@
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500)@
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL)@
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400)@
BEGIN
DECLARE curid INTEGER;
DECLARE v_empno DECIMAL(4);
DECLARE v_ename VARCHAR(10);
DECLARE v_hiredate DATE;
DECLARE v_sal DECIMAL(7, 2);
DECLARE v_comm DECIMAL(7, 2);
DECLARE v_sql VARCHAR(50);
DECLARE v_status INTEGER;
DECLARE v_rowcount INTEGER;
SET v_sql = 'SELECT empno, ename, hiredate, sal, ' || 'comm FROM emp';
CALL DBMS_SQL.OPEN_CURSOR(curid);
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.DEFINE_COLUMN_VARCHAR(curid, 2, v_ename, 10);
CALL DBMS_SQL.DEFINE_COLUMN_DATE(curid, 3, v_hiredate);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 4, v_sal);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 5, v_comm);
CALL DBMS_SQL.EXECUTE(curid, v_status);
CALL DBMS_OUTPUT.PUT_LINE('EMPNO ENAME HIREDATE SAL
COMM');
CALL DBMS_OUTPUT.PUT_LINE('----- ---------- ---------- --------
' || '--------');
FETCH_LOOP: LOOP
CALL DBMS_SQL.FETCH_ROWS(curid, v_status);
IF v_status = 0 THEN
LEAVE FETCH_LOOP;
END IF;
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.COLUMN_VALUE_VARCHAR(curid, 2, v_ename);
CALL DBMS_SQL.COLUMN_VALUE_DATE(curid, 3, v_hiredate);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 4, v_sal);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 5, v_comm);
CALL DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename,
10) || ' ' || TO_CHAR(v_hiredate,
'yyyy-mm-dd') || ' ' || TO_CHAR(v_sal,
'9,999.99') || ' ' || TO_CHAR(NVL(v_comm,
0), '9,999.99'));
END LOOP FETCH_LOOP;
CALL DBMS_SQL.LAST_ROW_COUNT( v_rowcount );
CALL DBMS_OUTPUT.PUT_LINE('Number of rows: ' || v_rowcount);
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END@This example results in the following output:
SET SERVEROUTPUT ON
DB20000I The SET SERVEROUTPUT command completed successfully.
CREATE TABLE emp ( empno DECIMAL(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR(10), job VARCHAR(9),
mgr DECIMAL(4),
hiredate TIMESTAMP(0),
sal DECIMAL(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm DECIMAL(7,2) )
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL)
DB20000I The SQL command completed successfully.
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400)
DB20000I The SQL command completed successfully.
BEGIN
DECLARE curid INTEGER;
DECLARE v_empno DECIMAL(4);
DECLARE v_ename VARCHAR(10);
DECLARE v_hiredate DATE;
DECLARE v_sal DECIMAL(7, 2);
DECLARE v_comm DECIMAL(7, 2);
DECLARE v_sql VARCHAR(50);
DECLARE v_status INTEGER;
DECLARE v_rowcount INTEGER;
SET v_sql = 'SELECT empno, ename, hiredate, sal, ' || 'comm FROM emp';
CALL DBMS_SQL.OPEN_CURSOR(curid);
CALL DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.DEFINE_COLUMN_VARCHAR(curid, 2, v_ename, 10);
CALL DBMS_SQL.DEFINE_COLUMN_DATE(curid, 3, v_hiredate);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 4, v_sal);
CALL DBMS_SQL.DEFINE_COLUMN_NUMBER(curid, 5, v_comm);
CALL DBMS_SQL.EXECUTE(curid, v_status);
CALL DBMS_OUTPUT.PUT_LINE('EMPNO ENAME HIREDATE SAL
COMM');
CALL DBMS_OUTPUT.PUT_LINE('----- ---------- ---------- --------
' || '--------');
FETCH_LOOP: LOOP
CALL DBMS_SQL.FETCH_ROWS(curid, v_status);
IF v_status = 0 THEN
LEAVE FETCH_LOOP;
END IF;
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 1, v_empno);
CALL DBMS_SQL.COLUMN_VALUE_VARCHAR(curid, 2, v_ename);
CALL DBMS_SQL.COLUMN_VALUE_DATE(curid, 3, v_hiredate);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 4, v_sal);
CALL DBMS_SQL.COLUMN_VALUE_NUMBER(curid, 5, v_comm);
CALL DBMS_OUTPUT.PUT_LINE(
v_empno || ' ' || RPAD(v_ename, 10) || ' ' || TO_CHAR(v_hiredate,
'yyyy-mm-dd') || ' ' || TO_CHAR(v_sal,
'9,999.99') || ' ' || TO_CHAR(NVL(v_comm,
0), '9,999.99'));
END LOOP FETCH_LOOP;
CALL DBMS_SQL.LAST_ROW_COUNT( v_rowcount );
CALL DBMS_OUTPUT.PUT_LINE('Number of rows: ' || v_rowcount);
CALL DBMS_SQL.CLOSE_CURSOR(curid);
END
DB20000I The SQL command completed successfully.
EMPNO ENAME HIREDATE SAL COMM
----- ---------- ---------- -------- --------
7369 SMITH 1980-12-17 800.00 0.00
7499 ALLEN 1981-02-20 1,600.00 300.00
7521 WARD 1981-02-22 1,250.00 500.00
7566 JONES 1981-04-02 2,975.00 0.00
7654 MARTIN 1981-09-28 1,250.00 1,400.00
Number of rows: 5