层次查询

        复杂查询除了多表关联查询，子查询以及表集合查询外，还有层次查询。层次查询主要用途是显示树形结构的数据。

      Oracle提供对于ANSI SQL的扩展来简化对树形层次结构的遍历，Oracle提供如下的3个结构来有效的处理层次化查询问题

              1）  start with ...... connect by

              2）  prior

               3） level

        关于 start with ...... connect by

        树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如EMP表中的EMPNO和MGR。EMPNO表示该雇员的编号，MGR表示领导该雇员的人的编号，即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个表示父节点的MGR（除根节点外），通过每个节点的父节点，就可以确定整个树结构。
　　在SELECT命令中使用CONNECT BY 和START WITH 子句可以查询表中的树型结构关系。其命令格式如下：
　　     SELECT   .....
　　     CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 裂名2}
　　     [START WITH] ;
　　其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。

注意：无论正树还是倒树, 关键就在于connect by的条件. 
      正树: 必须是 ‘父’= prior ‘子’
      倒树: 必须是 ‘子’= prior ‘父’

    定义查找起始节点
　　在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
　　例如查找JONES直接或间接领导的所有雇员信息。
　    　     SQL＞SELECT EMPNO，ENAME，MGR?
　    　                   FROM EMP
　        　       CONNECT BY PRIOR EMPNO=MGR
　                     　START WITH ENAME=‘JONES’；
　    　
　            　EMPNO ENAME MGR
　    　        --------------------------
　           　7566 JONES 7839?
　    　       7788 SCOTT 7566
　           　7876 ADAMS 7788
　           　7902 FORD 7566
　          　7369 SMITH 7902
　          　5 rows selected.
　　START WITH 不但可以指定一个根节点，还可以指定多个根节点。

      关于PRIOR
　　 运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。
　　PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：
　　           CONNECT BY PRIOR EMPNO=MGR
　　PIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：
　　            CONNECT BY EMPNO=PRIOR MGR
　　在这种方式中也应指定一个开始的节点。例如从SMITH节点开始自底向上查找EMP的树结构。
　           SQL>SELECT EMPNO，ENAME，MGR
　 FROM EMP
　 CONNECT BY EMPNO=PRIOR MGR
　 START WITH ENAME=’SMITH’；
　
　 EMPNO ENAME MGR
　 -------------------------
　 7369 SMITH 7902
　 7902 FORD 7566
　 7566 JONES 7839
　 7839 KING
　 4 rows selected.
　　在这种自底向上的查找过程中，只有树中的一枝被显示，这是因为，在树结构中每一个节点只允许有一个父节点，其查找过程是从开始节点起，找到其父节点，再由其父节点向上，找父节点的父节点。这样一直找到根节点为止，结果就是树中一枝的数据。
　　上面SQL的另外一种写法：
　 　SELECT EMPNO,ENAME,MGR
　    　FROM EMP
　       CONNECT BY PRIOR MGR=EMPNO
　 　START WITH ENAME=' SMITH ';

     使用LEVEL
　　在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。图1.2就表示了树结构的层次。
　　层号
　　KING    1
　　JONES BLAKE CLARK 2
　　MILLER  3
　　SCOTT FORD ALLEN WARD MARTIN TURNER JAMES
　　ADAMS SMITH  4
　　在查询中，可以使用伪列LEVEL显示每行数据的有关层次。LEVEL将返回树型结构中当前节点的层次，我们可以使用LEVEL来控制对树型结构进行遍历的深度。
　　例5显示EMP表中的各行数据及层号。
　      　SQL>SELECT LEVEL，EMPNO，MGR
　             FROM EMP
　             CONNECT BY PRIOR EMPNO=MGR
　             START WITH ENAME=‘KING’；
　             LEVEL EMPNO ENAME MGR
　             1 7839 KING?
　             2 7566 JONES 7839
　             3 7788 SCOTT 7566
　             4 7876 ADAMS 7788
　             3 7902 FORD 7566
　             4 7369 SMITH 7902
　             2 7698 BLAKE 7839
　             3 7499 ALLEN 7698
　             3 7521 WARD 7698
　             3 7654 MARTIN 7698
　             3 7844 TURNER 7698
　             3 7900 JAMES 7698
　             2 7782 CLARK 7839
　             3 7934 MILLER 7782
　             14 rows selected.
　　伪列LEVEL为数值型，可以在SELECT 命令中用语各种计算机。
　　例6 使用LEVEL改变查询结果的显示形式。
          SQL>COLUMN EMPLOYEE FORMAT A20
          SQL> SELECT EMPNO,RPAD(‘ ‘,LEVEL*3) ||ENAME EMPLOYEE,MGR
                FROM EMP
                CONNECT BY PRIORY EMPNO=EMR
                START WITH ENAME=’KING’;
                EMPNO EMPLOYEE MGR
                7840 KING?
                7566 JONES 7839
                7788 SCOTT 7566
                7876 ADAMS 7788
                7902 FORD 7566
                7369 SMITH 7902
                7698 BLAKE 7839
                7499 ALLEN 7698
                7521 WARD 7698
                7654 MARTIN 7698
　              78   TURNER 7698
　           　 7900 JAMES 7698
　           　 7782 CLARK 7839
　            　7934 MILLER 7782
　            　14 rows selected.
　 　在SELECT使用了函数RPAD，该函数表示以LEVEL*3个空格进行填充，由于不同行处于不同的节点位置，具有不同的LEVEL值，因此填充的空格数将根据各自的层号确定，空格再与雇员名字拼接，结果显示出这种层次关系，也就是说其雇员名字右侧填充的空格数与它的层数有关。

       有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示"1"，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示"0"。
        在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），Oracle 就会报出一个错误提示：" ORA-01436: CONNECT BY loop in user data"。如果不删掉对父亲的引用就无法执行查询操作。而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE，如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示"1"，否则就显示"0"。