Home » SQL & PL/SQL » SQL & PL/SQL » passing more than 2 parameters to an cursor
passing more than 2 parameters to an cursor [message #36600] Wed, 05 December 2001 23:34 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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;


----------------------------------------------------------------------
Previous Topic: How can I get multiple records from store procedure?
Next Topic: loop
Goto Forum:
  


Current Time: Fri Mar 29 02:09:50 CDT 2024