ORACLE CONNECT BY 学习

语法格式：

 

select [level], column, expr... from table
　　[where condition]
　　start with condition
　　connect by [prior column1= column2 |column1 = prior column2];

 层次查询是通过start with和connect by子句标识的：

 

1.其中level关键字是可选的，表示等级，1表示root,2表示root的child,其他相同的规则。

2.From之后可以是table,view但是只能是一个table。

3.Where条件限制了查询返回的行，但是不影响层次关系，属于将节点截断，但是这个被截断的节点的下层child不受影响。

4.Start with是表示开始节点，对于一个真实的层次关系，必须要有这个子句，但是不是必须的。

5.connect by prior是指定父子关系，其中prior的位置不一定要在connect by之后，对于一个真实的层次关系，这也是必须的。

对于from是视图的，那么这个view不能包含join。

层次查询限制

1.层次查询from 之后如果是table，只能是一个table，不能有join。

2.from之后如果是view，则view不能是带join的。

3.使用order by子句，order子句是在等级层次做完之后开始的，所以对于层次查询来说没有什么意义，除非特别关注level，获得某行在层次中的深度，但是这两种都会破坏层次。见增强特性中的使用siblings排序。

4.在start with中表达式可以有子查询，但是connect by中不能有子查询。

层次查询的增强特性

1、SYS_CONNECT_BY_PATH

Oracle 9i提供了sys_connect_by_path(column,char),其中column是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。

下面的是oracle10g新增特性

2、 CONNECT_BY_ISLEAF

    在oracle9i的时候，查找指定root下的叶子节点，是很复杂的，oracle10g引入了一个新的函数，connect_by_isleaf,如果行的值为0表示不是叶子节点，1表示是叶子节点。

3、CONNECT_BY_ISCYCLE和NOCYCLE关键字

    如果从root节点开始找其子孙，找到一行，结果发生和祖先互为子孙的情况，则发生循环，oracle会报ORA-01436: CONNECT BY loop in user data，在9i中只能将发生死循环的不加入到树中或删除，在10g中可以用nocycle关键字加在connect by之后，避免循环的参加查询操作。并且通过connect_by_iscycle得到哪个节点发生循环。0表示未发生循环，1表示发生了循环。

4、CONNECT_BY_ROOT

    Oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。

5、使用SIBLINGS关键字排序

    对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level,然后按last_name排序，这样破坏了层次，比如特别关注某行的深度，按level排序，也是会破坏层次的。

     在oracle10g中，增加了siblings关键字的排序。

语法：order  siblings  by <expre>

它会保护层次，并且在每个等级中按expre排序。 

案例：

--测试表A B C D 4层数据
create table DATAS(
ID NUMBER,
DATES DATE,
IDNEXT NUMBER
)
--存在B、C情况
insert into datas values(1,to_date('2015-01-01','yyyy-mm-dd'),2);
insert into datas values(2,to_date('2015-01-11','yyyy-mm-dd'),3);
insert into datas values(3,to_date('2015-01-14','yyyy-mm-dd'),4);
insert into datas values(4,to_date('2015-01-19','yyyy-mm-dd'),null);
--不存在B、C情况
insert into datas values(11,to_date('2015-02-01','yyyy-mm-dd'),14);
insert into datas values(14,to_date('2015-02-11','yyyy-mm-dd'),null);

--查询语句
SELECT ID,DATES,
CONNECT_BY_ROOT ID "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL,
SYS_CONNECT_BY_PATH(ID, '-') "PATH"
FROM DATAS
START WITH IDNEXT IS NULL
CONNECT BY PRIOR ID = IDNEXT ORDER SIBLINGS BY ID;

         ID   DATES          ROOT    ISLEAF   LEVEL   PATH

    -------  -----------      ----------   ----------  ---------- -----------

         4  1/19/2015            4           0           1          -4

         3  1/14/2015            4           0           2          -4-3

         2  1/11/2015            4           0           3          -4-3-2

         1  1/1/2015              4           1           4          -4-3-2-1

        14  2/11/2015          14          0           1          -14

        11  2/1/2015            14          1           2          -14-11