Date: 23 May 2003 18:47:00 -0700
Message-ID: <130ba93a.0305231747.251572c7_at_posting.google.com>
"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.
Parameterized the 2nd cursor like this:
SQL> select * from tyu;
OBJECT_ID
OBJECT_NAME
46915
A
46920
ABCDE
31250
ADDRESS_O
OBJECT_ID
OBJECT_NAME
31268
ADDRESS_T
SQL> select * from tyu2;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
46915 A
46920 ABCDE
31250 ADDRESS_O
31268 ADDRESS_T
SQL> declare
2 cursor cur1 is select object_id from tyu;
3 cursor cur2(oid number) is select object_name from tyu2 where object_id=oid;
4 begin
5 for x in cur1 loop
6 for y in cur2(x.object_id) loop 7 dbms_output.put_line(y.object_name); 8 end loop;
9 end loop;
10 end;
11 /
A
ABCDE
ADDRESS_O
ADDRESS_T PL/SQL procedure successfully completed.
SQL>
- Jusung Yang