在Oracle数据表的处理过程中，偶尔会遇到查询某个父节点下的所有子节点，对于这种情况，Oracle中有没有具体的函数来实现呢？答案是肯定的。
下面我们来看下具体的示例：

一、递归函数的语法
select *
from 表名
[where 条件1]
start with [条件2]
connect by [条件3];

参数说明：
其中connect by 与start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要。[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同。
[where 条件1] 是在根据“start with [条件2] connect by [条件3]”选择出来的记录进行过滤，是针对单条记录的过滤，不会考虑树的结构。
[条件2] 限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件。
[条件3] 指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉。

start with与connect by prior语句完成递归记录，形成一棵树形结构，通常可以在具有层次结构的表中使用。
start with 表示开始的记录。
connect by prior 指定与当前记录关联时的字段关系代码。
prior 存在于[条件2]中，可以不要，不要的时候只能查找到符合“start with [条件3]”的记录，不会再寻找这些记录的子节点。在使用时有两种写法：
connect by prior dep_id=pid ：表示采用自上而下的搜索方式（先找到父节点，然后找子节点），一般用户根据父节点查找子节点的情况；
connect by dep_id=prior pid ：表示采用自下而上的搜索方式（先找叶子节点，然后找父节点），一般用于根据子节点查找父节点的情况。

二、创建部门测试表
--创建部门表，这是一个具有层次结构的表，子记录通过parent_id与父记录的id进行关联
drop table tmp_dept;
create table tmp_dept(
dep_id       number(9) primary key,   --部门id
dep_name     varchar2(100),           --部门名称
pid          number(9)                --父级部门id，通过此字段与上级部门关联
);
-- 添加测试数据
insert into tmp_dept
select 101,'家喻销售有限公司',null from dual
union
select 1001,'研发部',101 from dual
union
select 1002,'财务部',101 from dual
union
select 1003,'销售部',101 from dual
union
select 1004,'产品部',101 from dual
union
select 1005,'市场部',101 from dual
union
select 10011,'研发一部',1001 from dual
union
select 10012,'研发二部',1001 from dual
;
commit;

--查看数据
select * from tmp_dept;
    DEP_ID DEP_NAME                                                                                PID
---------- -------------------------------------------------------------------------------- ----------
       101 家喻销售有限公司                                                                 
      1001 研发部                                                                                  101
      1002 财务部                                                                                  101
      1003 销售部                                                                                  101
      1004 产品部                                                                                  101
      1005 市场部                                                                                  101
     10011 研发一部                                                                               1001
     10012 研发二部                                                                               1001


三、查询示例
1、从根节点开始自上而下查询
从根节点开始查询递归的记录，start with dep_id=101表示从dep_id=101的记录开始查询，向叶子的方向递归，递归条件是dep_id=pid，当前记录的dep_id等于子记录的pid。
level 表示伪列表示节点深度。
select a.*,level
from tmp_dept a
start with dep_id=101
connect by prior a.dep_id=a.pid;
    DEP_ID DEP_NAME                                                                                PID      LEVEL
---------- -------------------------------------------------------------------------------- ---------- ----------
       101 家喻销售有限公司                                                                          1          1
      1001 研发部                                                                                  101          2
     10011 研发一部                                                                               1001          3
     10012 研发二部                                                                               1001          3
      1002 财务部                                                                                  101          2
      1003 销售部                                                                                  101          2
      1004 产品部                                                                                  101          2
      1005 市场部                                                                                  101          2

2、自下而上的方式查询
select dep_id,substr(sys_connect_by_path(dep_name,'-->'),4) as dep_name
from tmp_dept
start with pid =1001
connect by dep_id= prior pid ;
    DEP_ID DEP_NAME
---------- --------------------------------------------------------------------------------
     10011 研发一部
      1001 研发一部-->研发部
       101 研发一部-->研发部-->家喻销售有限公司
     10012 研发二部
      1001 研发二部-->研发部
       101 研发二部-->研发部-->家喻销售有限公司

3、从叶子节点开始查询
从叶子节点开始查询递归的记录，递归条件按照当前记录的pid等与父记录的dep_id。
select a.*,level
from tmp_dept a
start with dep_id=1001
connect by prior a.dep_id=a.pid;
    DEP_ID DEP_NAME                                                                                PID      LEVEL
---------- -------------------------------------------------------------------------------- ---------- ----------
      1001 研发部                                                                                  101          1
     10011 研发一部                                                                               1001          2
     10012 研发二部                                                                               1001          2
 
 
4、查询结果过滤查询
在下面的查询结果中可以看到，首先使用start with ... connect by prior查询出树形的结构，然后where条件才生效，对全部查询结果进行过滤。
select *
from tmp_dept
where dep_name like '%销售%'
start with dep_id=101
connect by prior dep_id = pid;
    DEP_ID DEP_NAME                                                                                PID
---------- -------------------------------------------------------------------------------- ----------
       101 家喻销售有限公司                                                                          1
      1003 销售部                                                                                  101


5、去掉prior关键字查询
prior的作用，prior关键字表示不进行递归查询，仅查询出满足dep_id=101的记录，下面是将第一个查询去掉prior关键字后结果。
select *
from tmp_dept
start with dep_id=101
connect by dep_id = pid;
    DEP_ID DEP_NAME                                                                                PID
---------- -------------------------------------------------------------------------------- ----------
       101 家喻销售有限公司                                                                          1

6、使用sys_connect_by_path函数连接显示子节点
sys_connect_by_path 函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。
select a.dep_id,substr(sys_connect_by_path(a.dep_name, '-->'),4) as dep_name,level
from tmp_dept a
connect by prior a.dep_id=a.pid
start with dep_id=101;
    DEP_ID DEP_NAME                                                                              LEVEL
---------- -------------------------------------------------------------------------------- ----------
       101 家喻销售有限公司                                                                          1
      1001 家喻销售有限公司-->研发部                                                                 2
     10011 家喻销售有限公司-->研发部-->研发一部                                                      3
     10012 家喻销售有限公司-->研发部-->研发二部                                                      3
      1002 家喻销售有限公司-->财务部                                                                 2
      1003 家喻销售有限公司-->销售部                                                                 2
      1004 家喻销售有限公司-->产品部                                                                 2
      1005 家喻销售有限公司-->市场部                                                                 2

7、使用connect_by_isleaf函数判断子节点
函数connect_by_isleaf来判断当前行是不是子节点。如果是子节点就会在列中显示1，如果不是叶子而是一个分支（例如当前内容是其他行的父节点）就显示0。
select substr(sys_connect_by_path(a.dep_name, '-->'),4) as dep_name,level,connect_by_isleaf
from tmp_dept a
connect by prior a.dep_id=a.pid
start with dep_id=101;
DEP_NAME                                                                              LEVEL CONNECT_BY_ISLEAF
-------------------------------------------------------------------------------- ---------- -----------------
家喻销售有限公司                                                                          1                 0
家喻销售有限公司-->研发部                                                                 2                 0
家喻销售有限公司-->研发部-->研发一部                                                      3                 1
家喻销售有限公司-->研发部-->研发二部                                                      3                 1
家喻销售有限公司-->财务部                                                                 2                 1
家喻销售有限公司-->销售部                                                                 2                 1
家喻销售有限公司-->产品部                                                                 2                 1
家喻销售有限公司-->市场部                                                                 2                 1

8、使用connect_by_root函数返回层根节点
函数 connect_by_root 用在列名之前用于返回当前层的根节点。
select connect_by_root a.dep_name,substr(sys_connect_by_path(a.dep_name, '-->'),4) as dep_name,level
from tmp_dept a
connect by prior a.dep_id=a.pid
start with dep_id=101;
CONNECT_BY_ROOTA.DEP_NAME      DEP_NAME                                                                              LEVEL
------------------------------ -------------------------------------------------------------------------------- ----------
家喻销售有限公司               家喻销售有限公司                                                                          1
家喻销售有限公司               家喻销售有限公司-->研发部                                                                 2
家喻销售有限公司               家喻销售有限公司-->研发部-->研发一部                                                      3
家喻销售有限公司               家喻销售有限公司-->研发部-->研发二部                                                      3
家喻销售有限公司               家喻销售有限公司-->财务部                                                                 2
家喻销售有限公司               家喻销售有限公司-->销售部                                                                 2
家喻销售有限公司               家喻销售有限公司-->产品部                                                                 2
家喻销售有限公司               家喻销售有限公司-->市场部                                                                 2


9、使用connect_by_iscycle函数返回循环节点
函数connect_by_iscycle，如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示1，否则就显示0。
select connect_by_iscycle,substr(sys_connect_by_path(a.dep_name, '-->'),4) as dep_name
from tmp_dept a
start with pid is null
connect by nocycle prior a.dep_id=a.pid;
CONNECT_BY_ISCYCLE DEP_NAME
------------------ --------------------------------------------------------------------------------
                 0 家喻销售有限公司
                 0 家喻销售有限公司-->研发部
                 0 家喻销售有限公司-->研发部-->研发一部
                 0 家喻销售有限公司-->研发部-->研发二部
                 0 家喻销售有限公司-->财务部
                 0 家喻销售有限公司-->销售部
                 0 家喻销售有限公司-->产品部
                 0 家喻销售有限公司-->市场部
四、总结
1、prior放在子节点端，则表示扫描树是以start with指定的节点作为根节点从上往下扫描，可能对应一个或多个分支。
start with可以省略，如果省略，表示对所有节点都当成根节点分别进行遍历。
2、prior放在父节点端，则表示扫描树是以start with指定的节点作为最低层子节点，从下往上扫描。顺序是子节点往父节点扫描，直到根节点为止，这种情况只能得到一个分支。
start with可以省略，如果省略，表示对所有节点都当成最低层子节点分别往根节点方向遍历。





本文参考资料：http://wenku.baidu.com/link?url=RQDXWaKZH5NPadhY488xExmeiuJQq5YpoUhPTsdcFKKIu-QkWOFdGifexAHqB7Il9Qb7UQjFfDBSTxeZJuaLUtXDIK8fYEgqDAyR2x47AZi