connect by使用

         Oracle “CONNECT BY”是层次查询子句，一般用于树状或者层次结果集的查询。其语法是：

1 2	[ START WITH condition ] CONNECT BY [ NOCYCLE ] condition

     The start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts).

      说明：  

1. START WITH：告诉系统以哪个节点作为根结点开始查找并构造结果集，该节点即为返回记录中的最高节点。 
2. 当分层查询中存在上下层互为父子节点的情况时，会返回ORA-01436错误。此时，需要在connect by后面加上NOCYCLE关键字。同时，可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。 connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用。

   接下来，用一些示例来说明“CONNECT BY”的用法。

      例1、 创建一个部门表，这个表有三个字段，分别对应部门ID，部门名称，以及上级部门ID

--create table
create table dep(
depid number(10) not null,
depname varchar2(256),
upperdepid number(10)
);

初始化一些数据

insert into dep (depid , depname , upperdepid) values(0,'总经办',null);
insert into dep (depid , depname , upperdepid) values(1,'开发部',0);
insert into dep (depid , depname , upperdepid) values(2,'测试部',0);
insert into dep (depid , depname , upperdepid) values(3,'Server开发部',1);
insert into dep (depid , depname , upperdepid) values(4,'Client开发部',1);
insert into dep (depid , depname , upperdepid) values(5,'TA测试部',2);
insert into dep (depid , depname , upperdepid) values(6,'项目测试部',2);
commit;

现在根据"connect by"实现树状查询

select rpad(' ',2*(level-1),'-')||depname depname,
CONNECT_BY_ROOT depname root,
CONNECT_BY_ISLEAF isleaf,
level,
sys_connect_by_path(depname,'/') PATH
from dep
start with upperdepid is null
connect by prior depid=upperdepid;

查询结果

    说明：1、connect_by_root返回当前节点的最顶端节点；2、connect_by_isleaf判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点；3、level伪劣表示节点深度；4、sys_connect_by_path函数显示详细路径，并用"/"分割。

       例2、通过connect by生成序列

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;