Re: Function that Iterates down a structure

mike wrote:
> 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
>

http://www.psoug.org
click on Morgan's Library
click on Pipelined Table Functions

and

also click on Bulk Binding

My advice to my students, and to you too, there is almost no good reason for writing a cursor loop in Oracle 9i or above.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)