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 Salary: ' || emp_sal_rec.last_name ||
' - ' || emp_sal_rec.salary);
UPDATE employees
SET salary = salary + (salary * .025)
WHERE CURRENT OF emp_sal_cur;
END LOOP;
-- Display the updated salaries
FOR emp_sal_rec IN emp_sal_cur LOOP
DBMS_OUTPUT.PUT_LINE('New Salary: ' || emp_sal_rec.last_name ||
' - ' || emp_sal_rec.salary);
END LOOP;
END;
An update on the EMPLOYEES table occurs with each iteration of the loop. The second loop in this example simply displays the new salary result for each employee that was returned by the cursor query.
How It Works
Updating values when iterating a cursor can be handy, especially when working with a number of rows. There is one main difference between a cursor that allows updating and one that does not. That difference is the addition of the FOR UPDATE clause in the cursor declaration. By using the FOR UPDATE clause of the SELECT statement, you are causing the database to lock the rows that have been read by the query. This lock is to ensure that nobody else can modify the rows while you are working with them. The lock creates a read-only block on the table rows so that if someone else attempts to modify them while you have them locked, then they will have to wait until you have performed either a COMMIT or a ROLLBACK.
The FOR UPDATE clause has an optional NOWAIT keyword. By including this keyword, you will ensure that your query does not block your transaction if someone else already has the rows that you are querying blocked. The NOWAIT keyword tells Oracle not to wait if the requested rows are already locked, and control is immediately passed back to your program so that it can continue to run. If the NOWAIT keyword is omitted and the rows are already locked, then your program will stop and wait until the lock has been released.
You can use the cursor with any style of loop, as you've seen in previous recipes. No matter which type of loop you choose, the UPDATE must be coded using the WHERE CURRENT OF clause to update the current row in the cursor iteration. You will need to be sure to commit the changes after this block has been run, and in many circumstances the COMMIT statement can be coded into this block once it has been tested and verified to work correctly. As with any UPDATE statement, if you fail to COMMIT your changes, then the UPDATE will not save any changes to the database, and the updated data will be visible to your schema only until you disconnect. Issuing a COMMIT after your UPDATE statements have been issued is also a good practice in this case because it will release the lock on the rows you had queried via the cursor so that someone else can update them if needed. If you determine the data that was updated by the code block is incorrect, then a ROLLBACK will also release the lock.