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 ...Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
