| multiple cursors in one stored procedure [message #145113] |
Mon, 31 October 2005 23:13  |
lucas4394
Messages: 24
Registered: October 2005
|
Junior Member |
|
|
I have a stored procedure with 2 cursors to deal with DB to DB
data transactions.
...
CURSOR c1 IS SELECT * FROM T1;
CURSOR c2 IS SELECT * FROM T2;
BEGIN
...
FOR each_data IN c1
LOOP
/* some business logic */
END LOOP;
CLOSE c1;
/* start another cursor */
FOR each_row IN c2
LOOP
/* compare each row in T1 */
/* remove non-matched row from T2 */
END LOOP;
CLOSE c2;
...
END;
As showed above, after the first cursor completed, I got "invalid
cursor" in second cursor. The second concern about removing data from T2. Since c2 is built up from T2, if the data is
deleted where the current cursor pointed to T2. Doesn't effect
anything? Is this the correct way to handle cursors?
Thanks,
Lucas
|
|
|
|
| Re: multiple cursors in one stored procedure [message #145122 is a reply to message #145113] |
Mon, 31 October 2005 23:59  |
chj733
Messages: 11
Registered: October 2005
Location: CHINA
|
Junior Member |
|
|
You can create a array to store the data coming from t1,and then do what you want,because you have close the cursor of t1 when you want to use it
don't worry about the fallow rows when you open the cursor,you'll not see the rows,because of the consistency read
|
|
|
|
| Re: multiple cursors in one stored procedure [message #145126 is a reply to message #145113] |
Tue, 01 November 2005 00:24 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Because you use an implicit cursor (for row in cursor instead of open - fetch - close) you don't have to close the cursor. In fact, you cannot close it.
By the time the 'close c1' is reached, cursor c1 is already closed, resulting in the error 'Invalid cursor'.
You should use 1 cursor to handle this. As chj733 mentioned, the results from c1 are not available in your c2-loop, unless you store them in an array.
hth
|
|
|
|
| Re: multiple cursors in one stored procedure [message #145170 is a reply to message #145126] |
Tue, 01 November 2005 08:18 |
lucas4394
Messages: 24
Registered: October 2005
|
Junior Member |
|
|
You should use 1 cursor to handle this.
As chj733 mentioned, the results from c1
are not available in your c2-loop, unless
you store them in an array.
I don't think I can use one cursor to hanle them because the
cursor2 has to compare to Table 1, and remove non-matched
from Table 2 which cursor2 is built from.
I am familiar in using array in PL/SQL, could you give me some
examples/hints please? And do you suggest that using two
stored procedures to handle these two different cases, and
put both of them into one stored procedure?
Thanks,
Lucas
[Updated on: Tue, 01 November 2005 08:21] Report message to a moderator |
|
|
|
| Re: multiple cursors in one stored procedure [message #145172 is a reply to message #145170] |
Tue, 01 November 2005 08:53 |
raji.s
Messages: 52
Registered: February 2005
|
Member |
|
|
First of all, whenever you are using a cursor FOR LOOP,
you must strictly keep this in mind that Oracle implicitly
opens,fetches in a loop, and finally closes the cursor.
you should comment the line in your code which closes cursor c1,
that's the reason it is giving you an error as INVALID CURSOR
because oracle implicitly has already closed that cursor.
You can create a index by table /pl sql table type and store the data of t1 into it and then can easily use a for loop
in cursor c2 to find the matched/unmatched rows and delete
them accordingly.
If you still need clarification, type the code and specify what you want exactly.
Regards,
Rajiv.
|
|
|
|
| Re: multiple cursors in one stored procedure [message #145246 is a reply to message #145170] |
Wed, 02 November 2005 00:04 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| lucas4394 wrote on Tue, 01 November 2005 15:18 |
I don't think I can use one cursor to hanle them because the
cursor2 has to compare to Table 1, and remove non-matched
from Table 2 which cursor2 is built from.
|
SQL is al about comparing one table to another. Maybe 'NOT EXISTS' will do for you?
hth
|
|
|
|
| Re: multiple cursors in one stored procedure [message #151407 is a reply to message #145113] |
Wed, 14 December 2005 12:53 |
donlcs
Messages: 62
Registered: October 2005
Location: VA, USA
|
Member |
|
|
First, I hope the problem has been resolved. If my understanding of the problem is correct. Then, I would think at least two possible solutions exist.
*) The first one would seem more elegant, as Frank suggested use EXIST function, along the line of
Delete
From t2
Where EXISTS
t1.col1 = t2.col1
and t1.col2 = t2.col2
and t1.col3 = t2.col3
...
/* exactly match? the two tables are truly identical */
)
*) nested loops, in other words, the second loop should be inside the first one, I don't think that's going to be efficient.
|
|
|
|
| Re: multiple cursors in one stored procedure [message #151470 is a reply to message #151407] |
Wed, 14 December 2005 23:59 |
ashwin_tampa
Messages: 40
Registered: October 2005
|
Member |
|
|
I have done something like that
in one OF my 3rd table insert.
THIS WORKS PERFECTLY WELL,
might be helpfull:
DECLARE
CURSOR c1 IS SELECT * FROM merlin ORDER BY stkcd;
CURSOR c2 IS SELECT * FROM omega ORDER BY stkcd;
v_loc T.LOC%TYPE;
v_stkcd T.STKCD%TYPE;
v_oldcode T.OLDCODE%TYPE;
v_qty T.QTY%TYPE;
v_wac T.WAC%TYPE;
v_val T.VAL%TYPE;
v_pair_num T.PAIR_NUM%TYPE;
BEGIN
OPEN c1;
OPEN c2;
LOOP
FETCH c1 INTO v_loc,v_stkcd,v_oldcode,v_qty,v_wac,v_val;
FETCH c2 INTO v_loc,v_stkcd,v_oldcode,v_qty,v_wac,v_val;
IF (c1%FOUND) AND (c2%FOUND) THEN
v_pair_num := v_pair_num +1;
INSERT INTO T VALUES(v_loc,v_stkcd,v_oldcode,v_qty,v_wac,v_val,v_pair_num);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE c1;
CLOSE c2;
END;
/
|
|
|
|
| Re: multiple cursors in one stored procedure [message #151576 is a reply to message #145113] |
Thu, 15 December 2005 13:38 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm really at a loss of understanding on this one. As far as the last poster goes, what in the world are you doing? Fetching a random row out of a table. Then fetching a random row out of another table, but overwriting the first row? Then if both fetches were successful, insert the random row from the second table into a third table with a count of the number of fetches that happened so far? What is the point/goal of doing the above, and what is the goal you are trying to accomplish?
As far as the OP is concerned, you haven't very clearly explained your purpose in english; can you? But as was hinted by Frank, this totally seems like something that should at the least use implicicit cursors, but most probably be in a single query. Are you just trying to examine the rows in t2 and delete those that are not in t1?
In both cases you guys are doing this row at a time style which is the slowest way possible.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
