Re: A question about performing UPDATE and multiple tables

ken_at_kendenny.com wrote:
>On Sep 7, 11:31 am, Ubiquitous <web..._at_polaris.net> wrote:

>> I currently have a stored procedure which reads a transaction table for new
>> records, writes them to a flat file (which is uploaded to a mainframe system
>> for processing) and then updates the records as being processed.
>>
>> We will be creating a new ORACLE instance which also creates transactions. For
>> network traffic and other issues, we will create an indentical transaction
>> table in the new instance that will be processed the same was as the original.
>>
>> My current stored procedure looks something like this (simplified a bit for
>> clarity's sake):
>>
>> /************************************************/
>> CURSOR GetTrans_cur IS
>> SELECT *
>> FROM TRANS
>> WHERE processing_dt IS NULL
>> FOR UPDATE OF processing_dt;
>>
>> [...]
>>
>> FOR v_gettrans IN GetTrans_cur LOOP
>> [build file record from cursor fields]
>> UTL_FILE.PUT_LINE(v_outfile, v_REC);
>>
>> UPDATE TRANS
>> SET processing_dt = SYSDATE
>> WHERE CURENT OF GetTrans_cur;
>> END LOOP;
>> /************************************************/
>>
>> The easiest way to process the new table would be to create a second cursor
>> and perform a second FOR LOOP for it, but is there a way to query and update
>> multiple tables with a single cursor?
>>
>> I thought using a UNION would be perfect for the task but it doesn't like
>> having an UPDATE OF clause. Am I barking up the wrong tree?
>
>CURSOR c1 IS SELECT t1.col1, t1.col2, t2.col3, t2.col4
> FROM t1, t2
> WHERE t1.id = t2.id
> FOR UPDATE OF t1.something, t2.something;
>

Thank you! I never considered using a normal join.

Can someone point me to where I can find the differences between a join and a UNION of two tables? I'm curious... Received on Fri Sep 07 2007 - 12:15:43 CDT