Re: Nested Cursor Loops in Oracle PL/SQL

From: John <John_at_none_zzzz.com>
Date: Sat, 24 May 2003 00:21:24 GMT
Message-ID: <8Wyza.66372$823.15141_at_news1.east.cox.net>

Thanks.

"Maximus" <qweqwe_at_qwqwewq.com> wrote in message news:9Byza.52953$ro6.1479796_at_news2.calgary.shaw.ca...
> "John" <John_at_none_zzzz.com> wrote in message
> news:PIxza.65033$823.21004_at_news1.east.cox.net...
> > Hi.
> >
> > I have two tables tbl1 and tbl2, which have a one to many relationship.
 Let
> > us say, "keyfld" is the primary key in tbl1 and foreign key in tbl2.
> >
> > I want to traverse the two tables using a for loop and cursors. The
 problem
> > is I can not figure how to define the second cursor to have the keyfld
> > column from the first cursor in its where clause.
> >
> > declare
> > cursor tbl1_c is (select keyfld from tbl1);
> > cursor tbl2_c is (select fld2 from tbl2 where keyfld=
 tbl1_c_rec.keyfld);
> >
> > begin
> > for tbl1_c_rec in tbl1_c /* Pick one row and for each one */
> > loop
> > for tbl2_c_rec in tbl2_c /* Look at all child rows for that
 row
 */
> > loop
> > null; /* do something */
> > end loop;
> > end loop;
> > end;
> >
> > Oracle gives an error. I tried creating a variable of the same type and
> > assigning the value to be passed from tbl1_c_rec to it and then use the
> > variable in the second cursor. It did not like that either.
> >
> > I am new to Oracle programming. Thanks.

>

> For your inner for loop, you need to create a cursor that accepts
> parameters:

>


> declare

>     cursor tbl1_c is (select keyfld from tbl1);


>     cursor tbl2_c( keyvalue number ) is ( select fld2 from tbl2 where


 keyfld

> = keyvalue ); << modified cursor to accept keyvalue parameter
> begin
> for tbl1_c_rec in tbl1_c
> loop
> for tbl2_c_rec in tbl2_c( tbl1_c_rec.keyfld ) << feed keyvalue
> from table one to inner cursor
> loop
> null;
> end loop;
> end loop;
> end;

>

> HTH

>
>
>

Received on Sat May 24 2003 - 02:21:24 CEST