OraclePLSQL--Explicit Cursor Attributes

Explicit Cursor Attributes

Explicit Cursor Attributes
Statement	State	%FOUND	%NOTFOUND	%ISOPEN	%ROWCOUNT
OPEN	Before	Exception	Exception	FALSE	Exception
	After	NULL	NULL	TRUE	0
1stFETCH	Before	NULL	NULL	TRUE	0
	After	TRUE	FALSE	TRUE	1
Next FETCH	Before	TRUE	FALSE	TRUE	1
	After	TRUE	FALSE	TRUE	n + 1
Last FETCH	Before	TRUE	FALSE	TRUE	n + 1
	After	FALSE	TRUE	TRUE	n + 1
CLOSE	Before	FALSE	TRUE	TRUE	n + 1
	After	Exception	Exception	FALSE	Exception

The %FOUND cursor attribute signals that rows are available to retrieve from the cursor and the %NOTFOUND attribute signals that all rows have been retrieved from the cursor. The %ISOPEN attribute lets you know that the cursor is already open, and is something you should consider running before attempting to open a cursor. Like implicit cursors, the %ROWCOUNT attribute tells you how many rows you’ve fetched at any given point. Only the %ISOPEN cursor attribute works anytime without an error. The other three raise errors when the cursor isn’t open.

Cursor Attributes Example

Notice how the cursor attributes are used in this example:

Ø  First, although it has no bearing on the use of the cursor attributes, we modify the cursor declaration so that employees are processed in descending order of salary.

Ø  We only open the cursor after testing the %ISOPEN attribute to confirm that the cursor is currently closed. In this context this is an unnecessary step. However, in large and complex PL/SQL applications in which many different concurrent programs access multiple cursors at the same time, such a step may be essential.

Ø  Within the loop which processes each cursor record, we utilize the running value found within %ROWCOUNT.

Ø  For reasons similar to what was just mentioned for opening the cursor, we test the %ISOPEN attribute again before deciding to close the cursor

DECLARE

CURSOR Employees IS

SELECT *

FROM employee

ORDER BY salary DESC;

EmpRecord         employee%ROWTYPE;

BEGIN

IF NOT (Employees%ISOPEN) THEN

OPEN Employees;

END IF;

LOOP

FETCH Employees INTO EmpRecord;

EXIT WHEN Employees%NOTFOUND;

dbms_output.put_line ('Employee number ' ||

Employees%ROWCOUNT || ' ' ||

EmpRecord.LName || ' ' || ' ' || EmpRecord.FName || ' ' ||

'earns ' || EmpRecord.Salary);

END LOOP;

IF Employees%ISOPEN THEN

CLOSE Employees;

END IF;

END;

anonymous block completed

Employee number 1 Bhutta  Ali earns 55000

Employee number 2 Hiraa  Hira earns 43000

Employee number 3 Saaad  Saad earns 40000

Employee number 4 Waseem  Akram earns 38000

Employee number 5 Waqar  Ahmed earns 30000

Employee number 6 Shah  Faz earns 25000

Employee number 7 Khan  Sana earns 25000

Employee number 8 Wardhaa  Alicia earns 25000