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_ID	SUPER_ID	NAME	ORDER_ID
1	0	根配置	0
11	1	配置1	21
111	11	具体配置1	6
112	11	具体配置2	5
14	112	具体配置分支1	3
115	112	具体配置分支2	7
116	11	具体配置3	8
117	116	具体配置分支3	9
12	1	配置2	1
128	12	具体配置11	10
123	12	具体配置12	14

---

    下面偶来讲具体内容：
  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_ID	SUPER_ID	NAME	ORDER_ID
1	0	根配置	0
12	1	配置2	1
14	112	具体配置分支1	3
112	11	具体配置2	5
111	11	具体配置1	6
115	112	具体配置分支2	7
116	11	具体配置3	8
117	116	具体配置分支3	9
128	12	具体配置11	10
123	12	具体配置12	14
11	1	配置1	21

            要得到正确的结果集，需要引入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_ID	SUPER_ID	NAME	ORDER_ID
1	0	根配置	0
12	1	配置2	1
128	12	具体配置11	10
123	12	具体配置12	14
11	1	配置1	21
112	11	具体配置2	5
14	112	具体配置分支1	3
115	112	具体配置分支2	7
111	11	具体配置1	6
116	11	具体配置3	8
117	116	具体配置分支3	9

---

       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
1	0	根配置	0	根配置
12	1	配置2	1	根配置->配置2
128	12	具体配置11	10	根配置->配置2->具体配置11
123	12	具体配置12	14	根配置->配置2->具体配置12
11	1	配置1	21	根配置->配置1
112	11	具体配置2	5	根配置->配置1->具体配置2
14	112	具体配置分支1	3	根配置->配置1->具体配置2->具体配置分支1
115	112	具体配置分支2	7	根配置->配置1->具体配置2->具体配置分支2
111	11	具体配置1	6	根配置->配置1->具体配置1
116	11	具体配置3	8	根配置->配置1->具体配置3
117	116	具体配置分支3	9	根配置->配置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