Table of Contents Previous Next


4 Stored Procedure Language : 4.8 Static Cursors : 4.8.6 Cursor Attributes

Each cursor has a set of attributes associated with it that allows the program to test the state of the cursor. These attributes are %ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT. These attributes are described in the following sections.
4.8.6.1 %ISOPEN
The %ISOPEN attribute is used to test whether or not a cursor is open.
cursor_name is the name of the cursor for which a BOOLEAN data type of TRUE will be returned if the cursor is open, FALSE otherwise.
4.8.6.2 %FOUND
The %FOUND attribute is used to test whether or not a row is retrieved from the result set of the specified cursor after a FETCH on the cursor.
cursor_name is the name of the cursor for which a BOOLEAN data type of TRUE will be returned if a row is retrieved from the result set of the cursor after a FETCH.
After the last row of the result set has been FETCHed the next FETCH results in %FOUND returning FALSE. FALSE is also returned after the first FETCH if there are no rows in the result set to begin with.
Referencing %FOUND on a cursor before it is opened or after it is closed results in an INVALID_CURSOR exception being thrown.
%FOUND returns null if it is referenced when the cursor is open, but before the first FETCH.
4.8.6.3 %NOTFOUND
The %NOTFOUND attribute is the logical opposite of %FOUND.
cursor_name%NOTFOUND
cursor_name is the name of the cursor for which a BOOLEAN data type of FALSE will be returned if a row is retrieved from the result set of the cursor after a FETCH.
After the last row of the result set has been FETCHed the next FETCH results in %NOTFOUND returning TRUE. TRUE is also returned after the first FETCH if there are no rows in the result set to begin with.
Referencing %NOTFOUND on a cursor before it is opened or after it is closed, results in an INVALID_CURSOR exception being thrown.
%NOTFOUND returns null if it is referenced when the cursor is open, but before the first FETCH.
4.8.6.4 %ROWCOUNT
The %ROWCOUNT attribute returns an integer showing the number of rows FETCHed so far from the specified cursor.
cursor_name%ROWCOUNT
cursor_name is the name of the cursor for which %ROWCOUNT returns the number of rows retrieved thus far. After the last row has been retrieved, %ROWCOUNT remains set to the total number of rows returned until the cursor is closed at which point %ROWCOUNT will throw an INVALID_CURSOR exception if referenced.
Referencing %ROWCOUNT on a cursor before it is opened or after it is closed, results in an INVALID_CURSOR exception being thrown.
%ROWCOUNT returns 0 if it is referenced when the cursor is open, but before the first FETCH. %ROWCOUNT also returns 0 after the first FETCH when there are no rows in the result set to begin with.
INVALID_CURSOR Exception
INVALID_CURSOR Exception
INVALID_CURSOR Exception
After OPEN & Before 1st FETCH
After nth Successful FETCH (last row)
After n+1st FETCH (after last row)
After CLOSE
INVALID_CURSOR Exception
INVALID_CURSOR Exception
INVALID_CURSOR Exception
The cursor FOR loop opens a previously declared cursor, fetches all rows in the cursor result set, and then closes the cursor.
The syntax for creating a cursor FOR loop is as follows.
FOR record IN cursor
statements
record is an identifier assigned to an implicitly declared record with definition, cursor%ROWTYPE. cursor is the name of a previously declared cursor. statements are one or more SPL statements. There must be at least one statement.
So for example if we pass the value 2000 as max_wage, then we will only be shown the name and salary of all employees that have a salary less than 2000. The result of the above query is the following:

4 Stored Procedure Language : 4.8 Static Cursors : 4.8.6 Cursor Attributes

Table of Contents Previous Next