Nested Cursor Loops in Oracle PL/SQL

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. Received on Sat May 24 2003 - 00:58:55 CEST