Updating row selected by cursor
A cursor is a special kind of loop for traversing through an SQL resultset one row at a time.
That allows us to perform operations on every record on a one-by-one basis.
In earlier versions of Oracle, this error was ignored which lead to occasionally incorrect results.
2) Do you need to update the table on a row-by-row basis?
If one of these conditions occurs, the specified statement executes.
The statement can be a simple statement such as SET var_name = value, or a compound statement written using BEGIN and END.
So, based on your comments, in your example, there should be a predicate in the definition of your cursor, right? DECLARE CURSOR cur IS SELECT emp_no, status FROM employee WHERE status IS NULL; TYPE l_employee_array IS TABLE OF cur%rowtype; l_modified_employees l_employee_array; BEGIN UPDATE employee e SET status = (select es.status from employee_status es where es.emp_no = e.emp_no) WHERE status IS NULL AND EXISTS (SELECT 1 FROM employee_status es WHERE es.emp_no = e.emp_no) RETURNING emp_no, status BULK COLLECT INTO l_modified_employees; FOR i IN l_modified_employees. So I only need to update and then subsequently process those records and not the rest.
If I updated everything then there would be no way to know which records to process since I cannot modify the employee table to add a column to it.
The example shows how to associate a vector (which contains the data) with a row cursor (which is the navigation object).
Oracle uses cursors under the hood for the update and for selects, which is going a bit beyond what you're asking.