Ubiquitous wrote:
> 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...
Not really. People learn multiple ways to accomplish the same goal and
then, through testing and experience, try several.
Go to:
http://www.psoug.org/reference/explain_plan.html
There you will find 10 test statements all written using different
syntax and all returning the exact same result set.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Sep 07 2007 - 16:41:00 CDT
