where current of use [message #285421] |
Tue, 04 December 2007 08:37  |
rosee
Messages: 1 Registered: December 2007 Location: France
|
Junior Member |
|
|
Hi,
I know that the WHERE CURRENT OF clause is used to update or to delete datas which are in a declared cursor, at the current record level.
But we know that the treatment in a cursor is done line after line; so why do we need to precise that the updating or deleting will happen in the current record .
for example if i have this updating
Cursor c IS select rowid,ste,client , name from TCLIENT FOR UPDATE;
BEGIN
FOR r IN c
LOOP
UPDATE TCLIENT set name='A' where ste = r.ste AND client = r.client;
UPDATE TCLIENT set name='A' where rowid=r.rowid;
UPDATE TCLIENT set name='A' where current of c;
END LOOP;
END;
these UPDATE requests give the same result but what is the difference between the third one(with where the current of clause)and the two others ones?
Secondly, i know that from the moment we use the for update clause, the cursor 's datas are locked.
Is the difference comes at this level( locking level).
Could you show me examples where we see the difference between queries we use the where current of clause and queries we don't.
Thanks a lot for your help.
Regards
Nathalie
[Updated on: Tue, 04 December 2007 08:38] Report message to a moderator
|
|
|
Re: where current of use [message #285448 is a reply to message #285421] |
Tue, 04 December 2007 11:57   |
 |
cmerry
Messages: 109 Registered: November 2005 Location: Idaho
|
Senior Member |
|
|
rosee wrote on Tue, 04 December 2007 06:37 | 1 - UPDATE TCLIENT set name='A' where ste = r.ste AND client = r.client;
2 - UPDATE TCLIENT set name='A' where rowid=r.rowid;
3 - UPDATE TCLIENT set name='A' where current of c;
|
Functionally statements 2 and 3 are the same when the cursor is opened FOR UPDATE (3 must be used in conjuction with FOR UPDATE).
1 differs in that the row being updated must be found for each iteration (either a full tablescan or index access), whereas 2 and 3 go directly to the row using ROWID.
The difference in 2 and 3 is basically stylistic. Using WHERE CURRENT OF, the cursor does not have to explicitly query the ROWID.
As for locking, Oracle performs a row level lock in this case.
The best advise I can think of deals with large transactions. It useful to lock the rows of the cursor in most situations; however, if I am performing a large transaction within the CURSOR FOR LOOP, and I need to COMMIT periodically, the FOR UPDATE CANNOT be used, and in that case, I would suggest using method 1 without the FOR UPDATE.
|
|
|
|
|
|
|
|