Attributes of Cursors

You can obtain the attributes of a cursor in SQL Script. See DECLARE CURSOR of Type Variable, OPEN, FETCH, and CLOSE for details about cursors.

Syntax

<cursor>.<attribute>

Remarks

The following table describes cursor attributes

Attribute	Description
ISOPEN	A boolean that indicates whether the cursor is open or not.
ROWTYPE	The ROW data type for the cursor. NULL for an untyped cursor.
ROWCOUNT	Number of rows fetched from the cursor if it is open. NULL if it is not open.
FOUND	A boolean that is true if the last fetch from the cursor found a row. NULL if not open, or open and not fetched from.

Example

The following example returns the nth value of a cursor of VARCHARs.

PROCEDURE nth (IN n INTEGER, IN crs CURSOR(name VARCHAR), OUT name VARCHAR)

a_lab:

BEGIN

IF NOT crs.ISOPEN THEN

OPEN crs;

END IF;

LOOP

FETCH crs INTO name;

IF NOT crs.FOUND OR nth >=crs.ROWCOUNT THEN

LEAVE a_lab;

END IF;

END LOOP;

CLOSE crs;

END

The following example makes use of the ROWTYPE attribute:

CURSOR m1 IS

	SELECT last_name, hire_date, job_id

	FROM employees

	WHERE employee_id = 5446;

			employee_rec m1%ROWTYPE;

BEGIN

	OPEN m1;

	FETCH m1 INTO employee_rec;

	DBMS_OUTPUT.PUT_LINE('Employee name: ' || employee_rec.last_name);

END;