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.

If you had attempted to search first, you would have come across this post.

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=92eeeff0.0305212210.4bd4a85b%40posting.google.com

Regards
/Rauf Sarwar Received on Sat May 24 2003 - 03:13:42 CEST