oracle子递归查询语句的几个有用的属性

1.如何正确排序:  siblings 
       2.展现路径:  SYS_CONNECT_BY_PATH
       3.条件的执行顺序

---

  先准备一下测试用的数据：

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--> SELECT FUNC_ID, SUPER_ID, NAME
    FROM MGR_PURV_FUNCTION
 START WITH FUNC_ID = 5
CONNECT BY PRIOR FUNC_ID = SUPER_ID

FUNC_IDSUPER_IDNAMEORDER_ID10根配置0111配置12111111具体配置1611211具体配置2514112具体配置分支13115112具体配置分支2711611具体配置38117116具体配置分支39121配置2112812具体配置111012312具体配置1214

---

    下面偶来讲具体内容：
  1.正确排序:  siblings 
             我们通常使用order by进行排序：            

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--> SELECT FUNC_ID, SUPER_ID, NAME,ORDER_ID
    FROM tmp_test2
 START WITH FUNC_ID = 1
CONNECT BY PRIOR FUNC_ID = SUPER_ID
order by order_id;

            但是这得到的结果是不正确的，因为order by在oracle的sql执行引擎中是最后被执行，其结果如下：

FUNC_IDSUPER_IDNAMEORDER_ID10根配置0121配置2114112具体配置分支1311211具体配置2511111具体配置16115112具体配置分支2711611具体配置38117116具体配置分支3912812具体配置111012312具体配置1214111配置121

            要得到正确的结果集，需要引入siblings，其在oracle的sql执行引擎的递归过程中发挥作用，因此结果正确，使用sql和查询结果如下：

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--> SELECT FUNC_ID, SUPER_ID, NAME,ORDER_ID
2    FROM tmp_test2
3 START WITH FUNC_ID = 1
4CONNECT BY PRIOR FUNC_ID = SUPER_ID
5order siblings by order_id;

FUNC_IDSUPER_IDNAMEORDER_ID10根配置0121配置2112812具体配置111012312具体配置1214111配置12111211具体配置2514112具体配置分支13115112具体配置分支2711111具体配置1611611具体配置38117116具体配置分支39

---

       2.展现路径:  SYS_CONNECT_BY_PATH
             有时候我们需要通过sql来直接展示节点的层次结构，一般的做法是通过伪列level和lpad来构造分割符来构造树的视觉效果，结果类似以下列表：

根配置  配置2    具体配置11    具体配置12  配置1    具体配置2      具体配置分支1      具体配置分支2    具体配置1    具体配置3      具体配置分支3

            其实oracle提供了原生的方法支持此类需求，而且效果更好，那就是函数sys_connect_by_path,使用sql和结果如下：

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->SELECT FUNC_ID, SUPER_ID, NAME, ORDER_ID, SUBSTR(SYS_CONNECT_BY_PATH(NAME, '->'), 3) REMARK
    FROM TMP_TEST2
 START WITH FUNC_ID = 1
CONNECT BY PRIOR FUNC_ID = SUPER_ID
 ORDER SIBLINGS BY ORDER_ID;

 

FUNC_ID

SUPER_ID

NAME

ORDER_ID

remark

10根配置0根配置121配置21根配置->配置212812具体配置1110根配置->配置2->具体配置1112312具体配置1214根配置->配置2->具体配置12111配置121根配置->配置111211具体配置25根配置->配置1->具体配置214112具体配置分支13根配置->配置1->具体配置2->具体配置分支1115112具体配置分支27根配置->配置1->具体配置2->具体配置分支211111具体配置16根配置->配置1->具体配置111611具体配置38根配置->配置1->具体配置3117116具体配置分支39根配置->配置1->具体配置3->具体配置分支3

 

 

 

            这点其实在“递归查询遍历详解”已经提及了，但是有兄弟经常会搞错
            首先是要注意子句的语法书写顺序：select -> from -> where -> start with -> connect by -> order by
            where写在connect by后面就不行，报错。

 

 

            其次要注意子句的执行顺序：from -> start with -> connect by -> where -> select -> order by
            执行顺序where在connect by之后，因此如果需要过滤出数据在进行递归查询，一定要将放到一个子查询结果中才行

           4、level

例子 create table tmp_test
(id number,
name varchar2(20),
pid number);

插入一些数据 ,如
1 A 0
2 B 1
3 C 2
4 D 3
5 E 3
6 F 4
7 G 5
8 H 1

select a.*,level from tmp_test a
start with id=1
connect by prior id=pid ;
执行结果
ID NAME PID LEVEL
1 A 0 1
2 B 1 2
3 C 2 3
4 D 3 4
6 F 4 5
5 E 3 4
7 G 5 5
8 H 1 2