这种级联的问题在oracle中非常容易解决,有现成的查询语法SQL> select * from ttype; PARENTID TYPEID
---------- ----------
1 2
2 4
4 7
7 10SQL> create or replace function f_treeorder (p_typeid integer)
2 return varchar2 is
3 cursor c_typeid is
4 select parentid
5 from ttype
6 connect by prior parentid = typeid
7 start with typeid = p_typeid;
8
9 w_str varchar2 (100) := p_typeid;
10 begin
11 for i in c_typeid loop
12 w_str := i.parentid || ',' || w_str;
13 end loop;
14
15 return w_str;
16 end;
17 /
Function created.SQL> SQL>
SQL> create or replace function f_treelength (p_typeid integer)
2 return integer is
3 w_deepth integer;
4 begin
5 select max (level)
6 into w_deepth
7 from ttype
8 connect by prior parentid = typeid
9 start with typeid = p_typeid;
10
11 return w_deepth;
12 exception
13 when no_data_found then
14 return 0;
15 end;
16 /Function created.SQL> select f_treeorder(7) from dual;F_TREEORDER(7)
--------------------------------------------------------------------------------
1,2,4,7SQL> select f_treelength(7) from dual;F_TREELENGTH(7)
---------------
3
---------- ----------
1 2
2 4
4 7
7 10SQL> create or replace function f_treeorder (p_typeid integer)
2 return varchar2 is
3 cursor c_typeid is
4 select parentid
5 from ttype
6 connect by prior parentid = typeid
7 start with typeid = p_typeid;
8
9 w_str varchar2 (100) := p_typeid;
10 begin
11 for i in c_typeid loop
12 w_str := i.parentid || ',' || w_str;
13 end loop;
14
15 return w_str;
16 end;
17 /
Function created.SQL> SQL>
SQL> create or replace function f_treelength (p_typeid integer)
2 return integer is
3 w_deepth integer;
4 begin
5 select max (level)
6 into w_deepth
7 from ttype
8 connect by prior parentid = typeid
9 start with typeid = p_typeid;
10
11 return w_deepth;
12 exception
13 when no_data_found then
14 return 0;
15 end;
16 /Function created.SQL> select f_treeorder(7) from dual;F_TREEORDER(7)
--------------------------------------------------------------------------------
1,2,4,7SQL> select f_treelength(7) from dual;F_TREELENGTH(7)
---------------
3
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货