Updating row using cursor oracle Free8cam
This is a complete book on PL/SQL with everything you need to know to write efficient and complex PL/SQL code.
So far in this series we've been concerned with simply reading data from tables using a cursor.
Since the values in A differ from B, currently, i have to do 2 updates to the table for each record in the cursor. It can take longer than 12 hours to complete (and has been confirmed as such).
There surely must be some way to improve the process. See the code example below:declare CURSOR tran IS SELECT oid, user_account_number FROM user_account001; BEGIN FOR t IN tran LOOP UPDATE transaction001 SET payer_account_number = t.user_account_number WHERE payer_account_oid = t.oid; UPDATE transaction001 SET payee_account_number = t.user_account_number WHERE payee_account_oid = t.oid; END LOOP; END; begin merge into transaction001 t using user_account001 ua on ( t.payer_account_oid = ) when matched then update set payer_account_number = ua.user_account_number; merge into transaction001 t using user_account001 ua on ( t.payee_account_oid = ) when matched then update set payee_account_number = ua.user_account_number; end; November 08, 2012 - am UTC can you tell me - are you updating most/many of the rows in this table?
In this final part we'll look at how you can use a cursor to modify data as well.
The easiest way to declare a cursor that can be used to update records is to simply use the default cursor type: Using the code above will declare a cursor which can used to update records.
and thus I imagine will affect most if not all data blocks. November 09, 2012 - am UTC use create table as select.it'll be a lot better than reading every block of the tables into the buffer cache, generating undo and redo, probably not having sufficient room in the cache to hold everything (hence writing it out) and having to read it back in again (perhaps many times)create table as select will skip undo, can skip redo, will just do direct path reads into your PGA and direct path writes out to the table.
Already for one other table the method was chosen to create a new table as (select) since it was MASSIVE and required only one columns' data to be changed and it did / does not matter what the new value is. But, as you've now pointed out, seems the same route can be followed. You've certainly broadened my knowledge ten fold the past couple of days! Hi Tom, We have a similar scenario in that we have updates coming into a large(ish) table that we're not sure how to make more efficient. It's got about 15 columns, most description-type varchars-256. As part of the business process, each record get updated at least twice as follows after it's been inserted:- for a given order id,- every record in that order gets its status updated (3-char column) by the system (once); usually 1,000 records in 1 shot- then individual records in that order may get updated by the business one to umpteen times across 1 description column and 3 flags over the next few days- once this step in the process has finished, the records are never modified again (though occasionally order-based deletes happen as part of another process)The table is about 500,000 rows now expected to grow to 10-15M over the course of the project.
Asked: November 07, 2012 - am UTC Answered by: Tom Kyte - Last updated: September 09, 2016 - am UTC Category: Database - Version: 10.2.0 Viewed 10K times!
You can make things a little more obvious by explicitly stating that the cursor is to be used for updating, as shown below: This cursor can be used to update any field from the underlying data set.
To make things even more specific you can list which columns from the data set you would like to make updatable, as shown below: The code above ensures that only one field from the underlying data set can be updated.
The Ask TOM team will be busy at Open World this month.
If you are at the conference, drop into the Groundbreaker area and say Hello.
Search for updating row using cursor oracle:
Next, we'll declare our cursor, open it and move it to the first record.