Home » SQL & PL/SQL » SQL & PL/SQL » Cursors, locks, and Update (10g)
Cursors, locks, and Update [message #417899] Wed, 12 August 2009 01:14 Go to next message
nokheat
Messages: 2
Registered: August 2009
Junior Member
hi, I would like to find what is the straight forward and best way to do this?

DECLARE

Cursor c1 IS
SELECT a set from Table_A
FOR UPDATE NOWAIT;

BEGIN
block update

UPDATE Table_B
SET blah_blah= x
WHERE
record_num IN c1

UPDATE Table_B
SET update_date= sysdate
WHERE
record_num IN c1

COMMIT and Unlock
END
Re: Cursors, locks, and Update [message #417901 is a reply to message #417899] Wed, 12 August 2009 01:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>hi, I would like to find what is the straight forward and best way to do this?
What is "this"?
What value of which metric constitutes "best"?
Re: Cursors, locks, and Update [message #417906 is a reply to message #417901] Wed, 12 August 2009 01:39 Go to previous messageGo to next message
nokheat
Messages: 2
Registered: August 2009
Junior Member
best in terms of server loading in run-time, and repetitive select is to be avoided.

and i would also like to keep the updates statements as update to the whole block of records.

i am asking because i need to use a cursor to hold the results from the select, but i must not loop thought it ... another question is on the variable holding the results... would a cursor be appropriate?
Re: Cursors, locks, and Update [message #417908 is a reply to message #417899] Wed, 12 August 2009 01:40 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it in a single UPDATE.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Previous Topic: passing arrays
Next Topic: Constraint restricting hierarchical use of table
Goto Forum:
  


Current Time: Thu Dec 08 10:19:23 CST 2016

Total time taken to generate the page: 0.06708 seconds