| Cursor Usage [message #6806] |
Tue, 06 May 2003 19:45  |
Sankar B. Mandalika
Messages: 20
Registered: November 2002
|
Junior Member |
|
|
Hi,
Here is a stored prcedure that I have written. Basically there are two tables that are involved viz
tab1 and tab2. They both have a common field called ec_id. In tab2, there are many flag fields associated with each ec_id. My goal is for every ec_id that is identical in both the tables, I need to update all the flag fields in tab2 for that particular ec_id to yes.
If anybody can comment on what I wrote, I would greatly appreciate it.
Also, once this update is done, I need to delete those matching ec_id entries from tab1. How can I do it?
Many thanks,
Sankar.
CREATE OR REPLACE PROCEDURE CVProc AS
BEGIN
CURSOR cur_comp IS
SELECT fld1 FROM tab1
WHERE ec_id = tab2.ec_id
FOR UPDATE NOWAIT;
BEGIN
FOR i IN cur_comp LOOP
UPDATE tab2 SET
flag = 'yes',
WHERE ec_id = i.ec_id;
END LOOP;
END;
END;
/
|
|
|
|
| Re: Cursor Usage [message #6810 is a reply to message #6806] |
Wed, 07 May 2003 00:26  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You shouldn't use a cursor loop - this can all be done in just two SQL statements.
update t2
set flag1 = 'yes', flag2 = 'yes'
where exists (select null
from t1
where t1.ec_id = t2.ec_id);
delete
from t1
where exists (select null
from t2
where t2.ec_id = t1.ec_id);
You have not given us any idea as to the relative sizes of these tables (same size, t1 much bigger than t2, t2 bigger than t1, etc.). The two statements might change a bit depending on your answers, but it'll still be just two statements - no cursor loop at all.
|
|
|
|
| Re: Cursor Usage [message #6827 is a reply to message #6810] |
Wed, 07 May 2003 11:15 |
Sankar B. Mandalika
Messages: 20
Registered: November 2002
|
Junior Member |
|
|
Many thanks Todd, for your response and explanation. Basically, t1 is going to be a lot smaller than t2. t1 will basically have data for each day and after the above processing, I am making it smaller in size by deleting those entries. However, t2 will retain everything and will be considerably larger in size compared to t1. If you can confirm whether the suggestion given by you can be used w/o any changes, I would greatly appreciate it. Thanks again.
Sankar.
|
|
|
|
| Re: Cursor Usage [message #6829 is a reply to message #6827] |
Wed, 07 May 2003 12:18 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
With t1 being a lot smaller, I would change the first statement to:
update t2
set flag1 = 'yes', flag2 = 'yes'
where ec_id in (select ec_id
from t1);
The second statement is fine as is.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
