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;