--建立测试表
drop table test;
create table test(id number(5),parent_id number(5));
insert into test values(1,null);
insert into test values(2,1);
insert into test values(3,1);
insert into test values(4,3);
insert into test values(5,4);select * from test
//*
创建函数返回级别层次
*/
create or replace function f_tree(p_id integer) return varchar2 is
cursor c_id is
select parent_id
from test
connect by prior parent_id = id
start with id = p_id;
result varchar2(100) := p_id;
begin
for i in c_id loop
result := i.parent_id || ',' || result;
end loop;
result := substr(result,2);
return result;
end;
/--调用如下
select id,f_tree(id) from test;
select f_tree(id) from test where id = 5;
drop table test;
create table test(id number(5),parent_id number(5));
insert into test values(1,null);
insert into test values(2,1);
insert into test values(3,1);
insert into test values(4,3);
insert into test values(5,4);select * from test
//*
创建函数返回级别层次
*/
create or replace function f_tree(p_id integer) return varchar2 is
cursor c_id is
select parent_id
from test
connect by prior parent_id = id
start with id = p_id;
result varchar2(100) := p_id;
begin
for i in c_id loop
result := i.parent_id || ',' || result;
end loop;
result := substr(result,2);
return result;
end;
/--调用如下
select id,f_tree(id) from test;
select f_tree(id) from test where id = 5;
/*
哦。楼主要得到记录集啊。
那你可以这样调用:
*/select * from test where
instr((select ',' || f_tree(id) || ',' from test where id = 5),',' || id || ',') > 0
我这里没有9i的环境,所以无法测试
可以实现这个目的。
start with id=? connect by prior parent_id = id
parent_id = id这个也可以颠倒过来,可以同时实现查找自身及父类型或者自身及子类型的功能,你试一下吧
FROM your_table
START WITH id = 5
CONNECT BY PRIOR parent_id = id
connect by prior mgr=empno表示empno的上级是mgrlevel是伪列,表示深度
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries4a.htm#2053937
但是要满足楼主的要求,
需要一点点修改
SELECT id
FROM your_table
START WITH id = 5
CONNECT BY PRIOR parent_id = id
楼上几位已经说清楚了
具体用法楼上的都说了,我不就多说了.