Determining the number of fetched rows for a cursor

Determining the number of rows associated with a cursor can be efficiently done by using the cursor_rowCount scalar function which takes a cursor variable as a parameter and returns an integer value as an output corresponding to the number of rows that have been fetched since the cursor was opened.

Before you begin

The following prerequisites must be met before you use the cursor_rowCount function:

A cursor data type must be created.
A cursor variable of the cursor data type must be declared.
An OPEN statement referencing the cursor must have been executed.

About this task

You can use the cursor_rowCount function within SQL PL contexts and would perform this task whenever in your procedural logic it is necessary to access the count of the number of rows that have been fetched for a cursor so far or the total count of rows fetched. The use of the cursor_rowCount function simplifies accessing the fetched row count which otherwise might require that within looping procedural logic you maintain the count with a declared variable and a repeatedly executed SET statement.

Restrictions

The cursor_rowCount function can only be used in SQL PL contexts.

Procedure

Formulate an SQL statement with a reference to the cursor_rowCount scalar function.
The following is an example of a SET statement that assigns the output of the cursor_rowCount scalar function to a local variable named rows_fetched:
```
SET rows_fetched = CURSOR_ROWCOUNT(curEmp)
```
Include the SQL statement containing the cursor_rowCount function reference within a supported SQL PL context. This might be, for example, within a CREATE PROCEDURE statement or a CREATE FUNCTION statement and compile the statement.

Results

The statement should compile successfully.

Example

The following is an example of an SQL procedure that includes a reference to the cursor_rowCount function:

connect to sample %

set serveroutput on %

create or replace procedure rowcount_test()
language sql
begin
  declare rows_fetched bigint;
  declare Designers cursor;
  declare first anchor to employee.firstnme;
  declare last anchor to employee.lastname;
 
  set Designers = cursor for select firstnme, lastname
    from employee where job = 'DESIGNER' order by empno asc;

  open Designers;

  fetch Designers into first, last;
  call dbms_output.put_line(last || ', ' || first);
  fetch Designers into first, last;
  call dbms_output.put_line(last || ', ' || first);

  set rows_fetched = CURSOR_ROWCOUNT(Designers);
  call dbms_output.put_line(rows_fetched || ' rows fetched.');

  close Designers;
end %

call rowcount_test() %

connect reset %
terminate %

What to do next

Execute the SQL procedure or invoke the SQL function.