Re: Function that Iterates down a structure

"mike" <hillmw_at_charter.net> a écrit dans le message de news:1110581847.347679.166050_at_f14g2000cwb.googlegroups.com...
| 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
|

Have a look at "connect by/start with" clause, something like:

select level, s.id, s.par, s.chd, s.level from struct s
connect by prior s.chd = s.par
start with s.par = 0
/

Btw, "level" is a reserved words (see the result of the query). Check this with "select keyword from v$reserved_words;"

Regards
Michel Cadot Received on Sat Mar 12 2005 - 01:42:57 CST