The attributes of cursor in Oracle PLSQL helps us to determine the state or status of a cursor.
Below is the list of attributes of cursors that we can use in Oracle PLSQL to determine the status of cursor.
Attribute | Explanation |
%ISOPEN | If the cursor is open the %ISOPEN returns TRUE, else returns FALSE. |
%FOUND | Returns INVALID_CURSOR if the cursor is declared but has been closed or not opened.
Returns NULL if no fetch has been executed after the opening of cursor. Returns TRUE if fetch executed has been successful. Returns FALSE if there is no row has been returned. |
%NOTFOUND | Returns INVALID_CURSOR if the cursor is declared but has been closed or not opened.
Returns NULL if no fetch has been executed after the opening of cursor. Returns FALSE if fetch executed has been successful. Returns TRUE if there is no row has been returned. |
%ROWCOUNT | Returns INVALID_CURSOR if the cursor is declared but has been closed or not opened.
Returns the number of rows fetched. |
Let’s understand, how to use cursor attributes from the help of the below PLSQL function:
CREATE OR REPLACE FUNCTION GetSalary IS cur_sal NUMBER; CURSOR cur_salary IS SELECT salary FROM employee; BEGIN CLOSE cur_salary; FETCH cur_salary IN cur_sal; IF cur_salary%NOTFOUND THEN cur_sal := 100000; END IF; CLOSE cur_salary; END;
The statement IF cur_salary%NOTFOUND; shows us the use of %NOTFOUND attribute of cursor cur_salary.