DB2 Version 10.1 for Linux, UNIX, and Windows

Cursor attributes (PL/SQL)

Each cursor has a set of attributes that enables an application program to test the state of the cursor.

These attributes are %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT.

%ISOPEN

This attribute is used to determine whether a cursor is in the open state. When a cursor is passed as a parameter to a function or procedure, it is useful to know (before attempting to open the cursor) whether the cursor is already open.

%FOUND

This attribute is used to determine whether a cursor contains rows after the execution of a FETCH statement. If FETCH statement execution was successful, the %FOUND attribute has a value of true. If FETCH statement execution was not successful, the %FOUND attribute has a value of false. The result is unknown when:

The value of cursor-variable-name is null
The underlying cursor of cursor-variable-name is not open
The %FOUND attribute is evaluated before the first FETCH statement was executed against the underlying cursor
FETCH statement execution returns an error

The %FOUND attribute provides an efficient alternative to using a condition handler that checks for the error that is returned when no more rows remain to be fetched.

%NOTFOUND

This attribute is the logical opposite of the %FOUND attribute.

%ROWCOUNT

This attribute is used to determine the number of rows that have been fetched since a cursor was opened.

Table 1 summarizes the attribute values that are associated with certain cursor events.

Table 1. Summary of cursor attribute values
Cursor attribute	%ISOPEN	%FOUND	%NOTFOUND	%ROWCOUNT
Before OPEN	False	Undefined	Undefined	"Cursor not open" exception
After OPEN and before 1st FETCH	True	Undefined	Undefined	0
After 1st successful FETCH	True	True	False	1
After `n`th successful FETCH (last row)	True	True	False	`n`
After `n`+1st FETCH (after last row)	True	False	True	`n`
After CLOSE	False	Undefined	Undefined	"Cursor not open" exception