2-11. Deleting Rows Returned by a Cursor
Problem
There are a series of database rows that you'd like to delete. You've created a cursor FOR LOOP, and you want to delete some or all rows that have been queried with the cursor.
Solution
Use a DELETE statement within a FOR loop to delete the rows that are retrieved by the cursor. If you create a cursor using the FOR UPDATE clause, then you will be able to use the WHERE CURRENT OF clause along with the DELETE statement to eliminate the current row within each iteration of the cursor. The following example shows how this can be done to remove all job history for a given department ID:
CREATE OR REPLACE PROCEDURE remove_job_history(dept_id IN NUMBER) AS
CURSOR job_history_cur IS
SELECT *
FROM job_history
WHERE department_id = dept_id
FOR UPDATE;
job_history_rec job_history_cur%ROWTYPE;
BEGIN
FOR job_history_rec IN job_history_cur LOOP
DELETE FROM job_history
WHERE CURRENT OF job_history_cur;
DBMS_OUTPUT.PUT_LINE('Job history removed for department ' ||
dept_id);
END LOOP;
END;
Using this technique, the job history for the department with the given ID will be removed from the JOB_HISTORY table.
How It Works
Much like updating rows using a cursor, the deletion of rows uses the WHERE CURRENT OF clause within the DELETE statement to remove each row. The cursor query must contain the FOR UPDATE clause in order to lock the rows that you are reading until a COMMIT or ROLLBACK has been issued. As mentioned in the previous recipe, the NOWAIT keyword is optional, and it can be used to allow control to be immediately returned to your program if someone else already has locks on the rows that you are interested in updating.
In each iteration of the loop, the DELETE statement is used along with the WHERE CURRENT OF clause to remove the current cursor record from the database. Once the loop has been completed, then all the rows that had been queried via the cursor should have been deleted. This technique is especially useful if you are going to be performing some further processing on each of the records and then deleting them. One such case would be if you wanted to write each of the records to a history table prior to deleting them. In any case, the cursor FOR loop deletion technique is a great way to remove rows from the database and work with the data along the way.