Oracle层次关系查询

原文链接： http://www.examda.com/oracle/zonghe/20100805/092741666.html

[导读]Oracle是一种关系型数据库，在表中不可能以层次的关系存放数据,但是oracle提供了层次(树形)查询语句，使用树的遍历来获得层次关系的数据。　　sql语法

　　select column,expr….

　　From table

　　Where conditions

　　Start with conditions

　　Connect by prior conditions

　　关键字和伪列介绍

　　Select

　　部分可以是字段或者表达式,或者伪列，如level，connect_by_isleaf等.

　　From

　　From后面可以是table,view但是只能是一个table,view中不能有多个表连接.

　　Where

　　条件限制了查询返回的行，但是其只影响节点自身，该节点的下层child不受影响

　　,属于节点的截断.

　　start with

　　确定遍历查询的开始点,可以是子查询,也可以不指定，不指定表示每个节点都作为起始节点来遍历一遍

　　connect by prior

　　确定遍历的方向,即是找子孙节点还是找祖先节点,遍历方向分为:自上向下，自下向上.

　　如果prior字段放在父字段前面，则表示要做自下向上的遍历;

　　如果prior字段放在子字段段前面，则表示要做自上向下的遍历;

　　和关键字prior放在=号左边右边没关系。

　　父字段:指明记录上一节点的字段如emp表中的mgr字段；

　　子字段:用来和父字段连接的字段，如emp标中的empno字段

　　level

　　查询的起始节点level为1，子孙依次增加，代表每个节点在家族树中的层次关系

　　connect_by_isleaf

　　是否叶子节点，如果查询时自顶向下，则叶子节点为1，如果自下向上，则根节点为1

　　connect _by_root column

　　查找子节点，叶子节点对应的根节点，10g新增的，这个很有用

　　prior

　　表示父节点，可以用在selelct部分，也可以用在connect by部分

　　nocycle

　　如果出现循环，在connect by中制定nocycle选项，查询将跳过循环部分的节点，避免10g之前的抱错。

　　connect_iscycle

　　如果出现循环，则为1，可以找出哪一条记录出现了循环。需要在connect by中加上nocycle选项

　　sys_connect_by_path

　　按path的顺序把字段连起来，做行列转换的时候需要。

　　Siblings

　　用于对树形遍历结果的排序，普通的排序会打乱树形遍历结果的层次关系，加上此关键字，可以不改变树形遍历结果的层次关系，只是在每一层内部按关键字排序，相当于做组内排序了，用法为order siblings by col

　　节点修剪和分支修剪

　　where子句的限制将会做节点的修剪，但是其后代不会受到影响，相对应,connect by中加上条件,将会把满足条件的节点以及后代修剪，属于分支修剪。

　　格式化输出

　　利用lpad函数和level，可以格式化输出记录间父子层次关系，方便阅读。

　　应用例子

　　利用lpad函数和level伪列,格式化输出

　　SQL> select empno,ename,lpad(empno,length(empno)+(level-1)*4,'-') from emp start with mgr is null connect by prior empno=mgr;

　　EMPNO ENAME     LPAD(EMPNO,LENGTH(EMPNO)+(LEVE

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

　　7839 KING      7839

　　7566 JONES     ----7566

　　7788 SCOTT     --------7788

　　7876 ADAMS     ------------7876

　　7902 FORD      --------7902

　　7369 SMITH     ------------7369

　　7698 BLAKE     ----7698

　　7499 ALLEN     --------7499

　　7521 WARD      --------7521

　　7654 MARTIN    --------7654

　　7844 TURNER    --------7844

　　7900 JAMES     --------7900

　　7782 CLARK     ----7782

　　7934 MILLER    --------7934

　　自上向下，自下向上遍历查询

　　自下向上遍历

　　SQL> select empno,ename,level from emp start with empno=7788 connect by prior mgr=empno;

　　EMPNO ENAME          LEVEL

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

　　7788 SCOTT              1

　　7566 JONES              2

　　7839 KING               3

　　自上向下遍历

　　SQL> select empno,ename,level from emp start with empno=7788 connect by prior empno=mgr;

　　EMPNO ENAME          LEVEL

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

　　7788 SCOTT              1

　　7876 ADAMS              2