What are the cursor attributes used in PL/SQL?

by

%ISOPEN: it checks whether the cursor is open or not.

%ROWCOUNT: returns the number of rows affected by DML operations: INSERT,DELETE,UPDATE,SELECT.

%FOUND: it checks whether cursor has fetched any row. If yes – TRUE.

%NOTFOUND: it checks whether cursor has fetched any row. If no – TRUE.

In PL/SQL, cursor attributes are used to get information about the execution of a cursor. The commonly used cursor attributes in PL/SQL are:

  1. %FOUND: This attribute returns TRUE if the cursor has fetched at least one row, and FALSE otherwise.
  2. %NOTFOUND: This attribute returns TRUE if the cursor has not fetched any rows, and FALSE if it has fetched at least one row.
  3. %ROWCOUNT: This attribute returns the number of rows fetched so far by the cursor.
  4. %ISOPEN: This attribute returns TRUE if the cursor is open, and FALSE if it is closed.

Here is an example of how these cursor attributes can be used:

DECLARE
CURSOR c_employee IS
SELECT employee_id, employee_name FROM employees;
v_employee_id NUMBER;
v_employee_name VARCHAR2(50);
BEGIN
OPEN c_employee;
LOOP
FETCH c_employee INTO v_employee_id, v_employee_name;
EXIT WHEN c_employee%NOTFOUND;— Process the fetched data
DBMS_OUTPUT.PUT_LINE(‘Employee ID: ‘ || v_employee_id || ‘, Employee Name: ‘ || v_employee_name);
END LOOP;

— Display additional information
DBMS_OUTPUT.PUT_LINE(‘Number of Rows Fetched: ‘ || c_employee%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(‘Cursor is Open: ‘ || (c_employee%ISOPEN));

CLOSE c_employee;
END;
/

In this example, %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN are used with a cursor to check the status of the cursor during its execution.