层次查询

层次查询(connect by查询)
它的语法是：
SELECT *
FROM 
WHERE
START WITH 
CONNECT BY 
ORDER BY col1, col2 ...
其中，start with 表示的是根刻录行，connect by 指定了父记录行和子记录行之间的关系，在层次查询中，条件表达式必须使用prior操作符来指定父记录行，如：
... PRIOR expr = expr
or
... expr = PRIOR expr 
如果connect by 条件是一个组合条件，那么只有一个条件需要prior操作符，如：CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id不过，connect by 不能包含子查询。
prior是一个二元操作符，最常见的是用于列值相等的比较，它让Oracle使用对应列的父亲行的值。使用非相等比较，极有可能倒致查询陷入无穷循环，以出错终止。
与join的组合， 
如果where子句有join操作，在执行connect by之前会先执行join  
如果where子句没有join操作，会在执行connect by之后再执行这些条件  
Oracle会执行下述步骤来完成connect by操作：
1. 执行条件过滤，取决于是否有join操作
2. 选择层次中满足start with条件的的根行，即层次树中第一层的节点行
3. 选择每个根行的子行，每个子行必须满足connect by条件中对应于根行的条件
4. 选择子行的后代，每次都使用子行与对应的父行进行connect by 匹配
5. 如果where中不含有join操作，那么Oracle将在执行完connect by操作之后移掉不符合条件的那么记录行。
当一行既是父行，也是子行时，就会产生死循环。例，找出雇员及经理之间的关系的结果： 
SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        101 Kochhar                          100
        108 Greenberg                        101
        109 Faviet                           108
        110 Chen                             108
        111 Sciarra                          108
        112 Urman                            108
        113 Popp                             108
        200 Whalen                           101
 
举子例子说明，
对emp表，如果以条件“where mgr is NULL”开始，先会产生没有经理的那些行，以这些行作为根，开始搜索，再执行“CONNECT BY PRIOR EMPNO = MGR”，下面是这个查询的具体过程： 
Using EMP, the start with SET is:

scott@ORA8I.WORLD> select ename, empno, mgr from emp
  2  where mgr is null;

ENAME           EMPNO        MGR
---------- ---------- ----------
KING             7839

Now, if we do the "connect by manually" we would find:

scott@ORA8I.WORLD> select ename, empno, mgr
  2  from emp where mgr = 7839;

ENAME           EMPNO        MGR
---------- ---------- ----------
JONES            7566       7839
BLAKE            7698       7839
CLARK            7782       7839

scott@ORA8I.WORLD> 

KINGS empno is the prior empno.  If we build the entire hierarch -- we have:

scott@ORA8I.WORLD> select lpad(' ',level*2,' ')||ename ename, empno, mgr
  2  from emp
  3  START WITH MGR IS NULL
  4  CONNECT BY PRIOR EMPNO = MGR
  5  /

ENAME                EMPNO        MGR
--------------- ---------- ----------
  KING                7839
    JONES             7566       7839
      SCOTT           7788       7566
        ADAMS         7876       7788
      FORD            7902       7566
        SMITH         7369       7902
    BLAKE             7698       7839
      ALLEN           7499       7698
      WARD            7521       7698
      MARTIN          7654       7698
      TURNER          7844       7698
      JAMES           7900       7698
    CLARK             7782       7839
      MILLER          7934       7782

14 rows selected.