2-10. Updating Rows Returned by a Cursor
Problem
You've created a cursor to use for querying your data. You want to loop through the results using a cursor for loop and update the data as needed.
Solution
Use the WHERE_CURRENT_OF clause within your loop to update the current data row in the iteration. In the following example, the EMPLOYEES table is queried for all employees in a particular department. The results of the query are then iterated using a FOR loop, and the salary is increased for each employee record that is returned.
DECLARE
CURSOR emp_sal_cur IS
SELECT *
FROM employees
WHERE department_id = 60
FOR UPDATE;
emp_sal_rec emp_sal_cur%ROWTYPE;
BEGIN
FOR emp_sal_rec IN emp_sal_cur LOOP DBMS_OUTPUT.PUT_LINE('Old ...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.
