passing more than 2 parameters to an cursor [message #36600] |
Wed, 05 December 2001 23:34 |
pavan
Messages: 10 Registered: October 2001
|
Junior Member |
|
|
Hello Friends
If Any One Can help me out in passing more than 2 parameters to an cursor
the problem is as follows
i will open a cursor which will have 2 fields in each row i want to send those 2 fields to another cursor is it possible and if so please send in more information about it
Thank you
----------------------------------------------------------------------
|
|
|
Re: passing more than 2 parameters to an cursor [message #36602 is a reply to message #36600] |
Thu, 06 December 2001 00:22 |
tinel
Messages: 42 Registered: November 2001
|
Member |
|
|
Hi
try this:
DECLARE
CURSOR c1 IS SELECT col1, col2 FROM TEST1;
CURSOR c2 (param1 NUMBER, param2 NUMBER) IS SELECT col1, col2 FROM TEST2 WHERE col_x=param1 AND col_y=param2;
c_det1 c1%ROWTYPE;
c_det2 c2%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO c_det1;
dbms_output.put_line('c1');
EXIT WHEN c1%NOTFOUND;
OPEN c2(c_det1.col1, c_det1.col2);
LOOP
FETCH c2 INTO c_det2;
EXIT WHEN c2%NOTFOUND;
dbms_output.put_line('c2');
END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
END;
Bye
----------------------------------------------------------------------
|
|
|
Re: passing more than 2 parameters to an cursor [message #36605 is a reply to message #36600] |
Thu, 06 December 2001 01:13 |
Rob Baillie
Messages: 33 Registered: November 2001
|
Member |
|
|
Looks good, though there is a much simpler Cursor loop syntax
FOR c_det1 IN cur_1 LOOP
...
END LOOP;
does exactly the same as:
DECLARE
c_det1 c1%ROWTYPE;
BEGIN
LOOP
FETCH c1 INTO c_det1;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
CLOSE c1;
END
You don't then need all your EXITS, your dim for c_det1 / cdet_2, though all the remaining syntax stays the same.
And yes, you can still:
FOR c_det2 IN cur_2(c_det1.col1, c_det1.col2) LOOP
...
END LOOP;
----------------------------------------------------------------------
|
|
|