■PRIOR
阶层查询的CONNECY BY condition的条件式需要用到PRIOR来指定父节点，
作为运算符，PRIOR和加(+)减(-)运算的优先级相同。

■阶层查询
语法：START WITH condition CONNECT BY NOCYCLE condition

START WITH 指定阶层的根
CONNECT BY 指定阶层的父/子关系
NOCYCLE    存在CONNECT BY LOOP的纪录时，也返回查询结果。
condition  ... PRIOR expr = expr 或者 ... expr = PRIOR expr
例：
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...
CONNECT BY PRIOR employee_id = manager_id and 
           PRIOR account_mgr_id = customer_id ...

■CONNECT_BY_ROOT
查询指定根的阶层数据。

■CONNECT BY子句的例子
通过CONNECT BY子句定义职员和上司的关系。
SQL>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

■LEVEL的例子
通过LEVEL虚拟列表示节点的关系。
SQL>SELECT employee_id, last_name, manager_id, LEVEL
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                 MANAGER_ID      LEVEL
----------- ------------------------- ---------- ----------
        101 Kochhar                          100          1
        108 Greenberg                        101          2
        109 Faviet                           108          3
        110 Chen                             108          3
        111 Sciarra                          108          3
        112 Urman                            108          3
        113 Popp                             108          3

■START WITH子句的例子
通过START WITH指定根节点，ORDER SIBLINGS BY保持阶层的顺序。

SQL>SELECT last_name, employee_id, manager_id, LEVEL
      FROM employees
      START WITH employee_id = 100
      CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY last_name;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL
------------------------- ----------- ---------- ----------
King                              100                     1
Cambrault                         148        100          2
Bates                             172        148          3
Bloom                             169        148          3
Fox                               170        148          3
Kumar                             173        148          3
Ozer                              168        148          3
Smith                             171        148          3
De Haan                           102        100          2
Hunold                            103        102          3
Austin                            105        103          4
Ernst                             104        103          4
Lorentz                           107        103          4
Pataballa                         106        103          4
Errazuriz                         147        100          2
Ande                              166        147          3
Banda                             167        147          3

hr.employees里，Steven King是公司的最高责任者，没有上司，他有一个叫John Russell的下属是部门80的管理者。
更新employees表，把Russell设置成King的上司，这样就产生了CONNECT BY LOOP。

SQL>UPDATE employees SET manager_id = 145
   WHERE employee_id = 100;

SQL>SELECT last_name "Employee", 
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;
  2    3    4    5    6    7  ERROR:
ORA-01436: CONNECT BY loop in user data

CONNECT BY NOCYCLE强制返回查询结果。CONNECT_BY_ISCYCLE显示是否存在LOOP。

SQL>SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
   LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE level <= 3 AND department_id = 80
   START WITH last_name = 'King'
   CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;

Employee                   Cycle  LEVEL Path
------------------------- ------ ------ -------------------------
Russell                        1      2 /King/Russell
Tucker                         0      3 /King/Russell/Tucker
Bernstein                      0      3 /King/Russell/Bernstein
Hall                           0      3 /King/Russell/Hall
Olsen                          0      3 /King/Russell/Olsen
Cambrault                      0      3 /King/Russell/Cambrault
Tuvault                        0      3 /King/Russell/Tuvault
Partners                       0      2 /King/Partners
King                           0      3 /King/Partners/King
Sully                          0      3 /King/Partners/Sully
McEwen                         0      3 /King/Partners/McEwen

■CONNECT_BY_ROOT的例子
1，查询110部门的职员，上司，职员和上司之间级别差及路径。

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
   LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
   FROM employees
   WHERE LEVEL > 1 and department_id = 110
   CONNECT BY PRIOR employee_id = manager_id;

Employee        Manager         Pathlen Path
--------------- ------------ ---------- -----------------------------------
Higgins         Kochhar               1 /Kochhar/Higgins
Gietz           Kochhar               2 /Kochhar/Higgins/Gietz
Gietz           Higgins               1 /Higgins/Gietz
Higgins         King                  2 /King/Kochhar/Higgins
Gietz           King                  3 /King/Kochhar/Higgins/Gietz


2，使用GROUP BY语句，查询110部门的职员以及该职员下属职员的工资和。

SELECT name, SUM(salary) "Total_Salary" FROM (
   SELECT CONNECT_BY_ROOT last_name as name, Salary
      FROM employees
      WHERE department_id = 110
      CONNECT BY PRIOR employee_id = manager_id)
      GROUP BY name;

NAME                      Total_Salary
------------------------- ------------
Gietz                             8300
Higgins                          20300
King                             20300
Kochhar                          20300