Re: Nested Cursor Loops in Oracle PL/SQL

"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;

HTH Received on Sat May 24 2003 - 01:59:01 CEST