Updating row using cursor oracle dating asia com
The following example creates a table with correct employee IDs but garbled names.
Then it runs an -- Create a table with all the right IDs, but messed-up names.
Based on that value, I update another table's column.
What I love about writing SQL Tuning articles is that I very rarely end up publishing the findings I set out to achieve. We have a table containing years worth of data, most of which is static; we are updating selected rows that were recently inserted and are still volatile. For the purposes of the test, we will assume that the target table of the update is arbitrarily large, and we want to avoid things like full-scans and index rebuilds.
The record locks are released when the next commit or rollback statement is issued.
You are not required to make changes to the records in order to use this statement.For instance, I might have the following psuedo code: Excuse my psuedo code off the top of my head.In the above sample, I am selecting a value from TABLEX.I'd tend to move the update outside of the loop in order to maximize SQL since that's the most efficient way to process data.If your business logic is amenable to it, I'd also suggest doing bulk operations to fetch the data into local collections that your business logic can iterate through in order to minimize context shifts between SQL and PL/SQL. LAST LOOP IF( l_modified_employees(i).status = 'Active' ) THEN -- Custom logic 1 ELSE -- Custom logic 2 END IF; END LOOP; END; Justin the table employee has processed and non processed records and the only way to know this is by the fact that non processed records have a status of null.
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.