SELECT id1
FROM depts
START WITH id1 = 1
CONNECT BY PRIOR id2 = id1;
----------
1
2
3
4
5
--------把上面的写成一个cursor------
当取到 n 时,(例如n=3),则: 调用自定义函数 f_getDeptName(n).
SELECT A.full_name
FROM B,A
where A.id = B.id1
START WITH id2 = n
CONNECT BY PRIOR id1 = id2;
--上面的写成cursor,把结果累加起来就可以了.
FROM depts
START WITH id1 = 1
CONNECT BY PRIOR id2 = id1;
----------
1
2
3
4
5
--------把上面的写成一个cursor------
当取到 n 时,(例如n=3),则: 调用自定义函数 f_getDeptName(n).
SELECT A.full_name
FROM B,A
where A.id = B.id1
START WITH id2 = n
CONNECT BY PRIOR id1 = id2;
--上面的写成cursor,把结果累加起来就可以了.
-- ---
1 0
2 1
3 1
4 2
5 3
6 4
6 57 rows selectedSQL>
SQL> select lpad(id,level*2+length(id),' ') id
2 from aa
3 connect by prior id = fid
4 start with fid = 0;ID
--------------------------------------------------------------------------------
1
2
4
6
3
5
67 rows selected然后以上查询语句,建一个函数把条件成立连在一起.
ID FID
-- ---
1 0
2 1
3 1
4 2
5 3
6 4
6 5可能比较复杂,而且不连续,跳跃型,并且可能关系是多层的!
return varchar2
as
v_ret varchar2(20);
cursor t_sor(v_id varchar2) is
select distinct id
from aa
connect by prior id = fid
start with fid =v_id;
begin
for v_sor in t_sor(p_id) loop
v_ret:=v_ret||'/'||v_sor.id;
end loop;
return(v_ret);
end;
/SQL> select id,get_dept(id) from aa;ID GET_DEPT(ID)
-- -------------------------------------------------------------------------------
1 /2/3/4/5/6
2 /4/6
3 /5/6
4 /6
5 /6
6
6 哈哈,搞一个怪函数出来,让大家分析再下笔.肚子饿要回家去!bye!
我去试一试
你的不能满足条件:
1、没有得到name
2、我想得到每个部门的上级部门,不是下级部门
SQL> create type t_var is varray(3) of number;Type createdSQL> set serveroutput on
SQL> declare
2 v_var t_var:=t_var();
3 procedure get(p_var in out t_var)
4 as
5 begin
6 for i in 1..3 loop
7 p_var.extend;
8 p_var(i):=i;
9 dbms_output.put_line(p_var(i));
10 end loop;
11 end;
12 begin
13 get(v_var);
14 end;
15 /
1
2
3PL/SQL procedure successfully completed
2 from aa
3 connect by prior id = fid
4 start with id=1;ID PATH
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 /1
2 /1/2
4 /1/2/4
6 /1/2/4/6
3 /1/3
5 /1/3/5
6 /1/3/5/67 rows selected