求一oracle函数的写法

larkim82 2010-01-20 08:12:35
现有一张表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;
...全文
274 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
lzcbj2008 2010-01-22
  • 打赏
  • 举报
回复
2楼写得好
czp3158 2010-01-22
  • 打赏
  • 举报
回复
哦,不对,这条
select * from menu_tree
start with id = 3000
connect by prior id = pid;
执行的结果不对!
czp3158 2010-01-22
  • 打赏
  • 举报
回复
哦,是可以的,sorry!把*改成pid就可以了
czp3158 2010-01-22
  • 打赏
  • 举报
回复
哈,这不是查全表嘛,这里只要查id=3000的所有下级id,你也太幽默了吧?
xiaosheng2008 2010-01-22
  • 打赏
  • 举报
回复
select * from menu_tree
start with id = 3000
connect by prior id = pid;
czp3158 2010-01-22
  • 打赏
  • 举报
回复
说sql能解决的写出来洛,我用sql解决不了
whmng 2010-01-22
  • 打赏
  • 举报
回复
不错
kingkingzhu 2010-01-21
  • 打赏
  • 举报
回复
2楼正解
qhf503 2010-01-21
  • 打赏
  • 举报
回复
SQL就能够解决
tangren 2010-01-21
  • 打赏
  • 举报
回复

--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;
小灰狼W 2010-01-21
  • 打赏
  • 举报
回复
那你要找出为什么会发生循环错误
是否有几个id互为上下级,或者有些记录(比如最高级)的id和pid相同
如果是后者
connect by后的条件里加上
and prior id<>prior pid
sxq129601 2010-01-21
  • 打赏
  • 举报
回复
SQL就能够解决
larkim82 2010-01-21
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 zhangwonderful 的回复:]
参考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;
[/Quote]

按照zhangwonderful提供的办法会报“用户数据中的CONNECT BY 循环”错误,在网上搜了一下,将函数“connect by nocycle prior ”就可以了,但加了以后在oracle 10g下运行没问题,放到oracle 9i下编译报错,错误的地方就在加的“nocycle”上,是不是oracle 9i不支持“nocycle”呢,如果不支持那要怎么修改哦,谢谢了,急着用!
小灰狼W 2010-01-20
  • 打赏
  • 举报
回复
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以上版本支持
另,使用的变量名称不要与表中字段名相同
zhangwonderful 2010-01-20
  • 打赏
  • 举报
回复
参考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;

17,377

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧