Function that Iterates down a structure

Now that I am a beginner function generator I am trying to create a function that iterates down a structure.

Struct table looks like this
id par chd level

 1    0     4       0
 5    1    13       2
 6    1    15       2
 7    1     7       2
 8    4     1       1

The result should look like this:
id par chd level

 1    0     4       0
 8    4     1       1
24    4     5       1
 5    1    13       2
 6    1    15       2

My sql query would be:
select id, par, chd, level
from struct
where par='0'

That would produce 1 row, the first one.

Then I need to feed the result 'chd' which is 4 back to a function to get the rest of the rows until there were no more results.

This is what I have so far:

create or replace function get_struct( fld1 number ) return varchar2 as
 id number;
 par number;
 chd number;
 level number;
begin
 for curs in
   ( select id, par, chd, level from struct where par=fld1)    loop
    if ( keep going until no mores results ..... )    end loop
  return id, par, chd, level;
end get_struct

Any help is appreciated.

Mike Received on Fri Mar 11 2005 - 16:57:27 CST