How to determine if a cursor is empty in PL/SQL?

In PL/SQL, you can use the cursor attributes %ISOPEN and %NOTFOUND to determine if a cursor is empty.

The %ISOPEN attribute is used to determine if a cursor is open. It returns TRUE if the cursor is open and FALSE if it is closed.

The %NOTFOUND attribute is used to determine if the cursor has reached the last row of data. It will return TRUE if it has reached the last row, and FALSE if it hasn’t.

Here is a sample code demonstrating how to check if a cursor is empty.

Begin by declaring a cursor named ‘cur_emp’ that selects the employee ID, first name, and last name from the employees table. Create a record ’emp_rec’ with the same structure as the cursor. Initialize a boolean variable ‘is_empty’. Open the cursor ‘cur_emp’.

Retrieve the current employee into the employee record. If no employee is found, set the is_empty variable to true; otherwise, set it to false.

If the cursor is empty, then DBMS_OUTPUT.PUT_LINE(‘Cursor is empty’); else DBMS_OUTPUT.PUT_LINE(‘Cursor is not empty’); END IF;

Close the cur_emp cursor and end the process.

In the example code above, a cursor named cur_emp is first defined, then the cursor is opened and a FETCH statement is executed to read data into the emp_rec variable. The %NOTFOUND attribute is then used to determine if the cursor has reached the last row of data. If it has, is_empty is set to TRUE, otherwise it is set to FALSE. Finally, the corresponding result is output based on the value of is_empty. Lastly, the cursor is closed.