一个非常难的查询问题(部门上下级的关系)
日期:2006-04-19 荐:
表 A:
id name
1 a
2 b
3 c
4 d
5 e
表 B(id1,id2都与A表的id关联,是联合主外键):
id1 id2
1 2
2 3
2 4
3 5
这是一个部门上下级的关系,前面的是上级,后面的下级,我想得到所有部门的列表,按照级别关系写成完整的字串,结果如下:
id full_name
1 a
2 a/b
3 a/b/c
4 a/b/d
5 a/b/c/d
请问怎么写?存储过程或函数都可以,十分感谢!
--------------------------------------------------------------
写过程处理:
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,把结果累加起来就可以了.
--------------------------------------------------------------
SQL〉 select * from aa;
ID FID
-- ---
1 0
2 1
3 1
4 2
5 3
6 4
6 5
7 rows selected
SQL〉
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
6
7 rows selected
然后以上查询语句,建一个函数把条件成立连在一起.
--------------------------------------------------------------
create function get_dept(p_id varchar2)
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!
--------------------------------------------------------------
使用数组,以下是一个例子:
SQL〉 create type t_var is varray(3) of number;
Type created
SQL〉 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
3
PL/SQL procedure successfully completed
-------------------------------------------
SQL〉 select lpad(’ ’,(level-1)*2,’ ’) | |id id,sys_connect_by_path(id,’/’) path
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/6
7 rows selected
标签: