求一oracle函数的写法

Question

现有一张表menu_tree，数据结构如下：
id      pid      name
3000    0000     单位一
3100    3000     单位二
3110    3100     单位三
3111    3110     单位四
3200    3000     单位五
3210    3200     单位六
3211    3210     单位七其中id表示当前单位编码，pid表示的是当前单位的上级单位。
我现在想写个oracle函数，能够查询一个单位下的所有单位（包括子单位的下级单位，依次循环），举个例子，我想知道3000下的所有单位，那么pid为3000的有3100和3200两条记录，而3100下又有3110，3110下又有3111，3200下有3210，3210下又有3211，我最后想要的结果应该是3100,3110,3111,3200,3210,3211,我想写个函数传一个参数，就可以查出下面该节点下的所有单位，最后返回的结果为满足条件单位的id.
create or replace function getSubId(id in varchar2) return varchar2 is
  Result varchar2(500);   
  /*
中间部分不知道怎么写了
*/
return Result;
end getSubId;

Answer 1

参考sql:
create or replace function getSubId(id in varchar2) return varchar2 is
   Result varchar2(500); 
   v_id menu_tree.id%type; 
   cursor cur_menu is
   select id from menu_tree 
   start with id='0000'
   connect by prior pid = id 
begin   
    Result:=' ';
    open cur_menu;
    loop
        fetch cur_menu into  v_id;
        exit when cur_menu%notfound;
        if Result<>' ' then
            Result:=Result|| ',' || v_id ;
        else
            Result:=v_id;
        end if; 
     end loop;
     close cur_menu;
return Result;
end getSubId;

Answer 2

create or replace function getSubId(p_id in varchar2) return varchar2 is 
  Result varchar2(500);  
begin
select wm_concat(distinct name) into result
from menu_tree
start with pid=p_id
connect by prior id=pid;
return result;
end;
wm_concat需要10g以上版本支持
另，使用的变量名称不要与表中字段名相同

Answer 3

按照zhangwonderful提供的办法会报“用户数据中的CONNECT BY 循环”错误，在网上搜了一下，将函数“connect by nocycle prior ”就可以了，但加了以后在oracle 10g下运行没问题，放到oracle 9i下编译报错，错误的地方就在加的“nocycle”上，是不是oracle 9i不支持“nocycle”呢，如果不支持那要怎么修改哦，谢谢了，急着用！

Answer 4

那你要找出为什么会发生循环错误
是否有几个id互为上下级，或者有些记录（比如最高级）的id和pid相同
如果是后者
connect by后的条件里加上
and prior id<>prior pid

Answer 5

--zhangwonderful可能入口参数与字段名重名了
--这个应该可以的
CREATE OR REPLACE FUNCTION getSubId(i_id IN VARCHAR2) RETURN VARCHAR2 IS
  RESULT VARCHAR2(500);
BEGIN
  RESULT := '';
  FOR c IN (SELECT t.id, t.pid, t.name FROM menu_tree t
            CONNECT BY PRIOR id = pid START WITH pid = i_id) LOOP
    RESULT := RESULT || c.id || ',';
  END LOOP;
  RETURN RTRIM(RESULT, ',');
EXCEPTION
  WHEN OTHERS THEN
    RETURN '';
END getSubId;

Answer 6

说sql能解决的写出来洛，我用sql解决不了

Answer 7

select * from menu_tree
start with id = 3000
connect by prior id = pid;

Answer 8

哈，这不是查全表嘛，这里只要查id=3000的所有下级id,你也太幽默了吧？

Answer 9

哦，是可以的，sorry！把*改成pid就可以了

Answer 10

哦，不对，这条
select * from menu_tree 
start with id = 3000 
connect by prior id = pid;
执行的结果不对！