Attributes of CURSOR in Oracle PLSQL

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.


Tagged , , , , . Bookmark the permalink.