Oracle12C--设置数据层次（十三）

• 基本概念
  • 层次查询是一种确定数据行之间关系结构的一种操作；例如，在学校中会分为，"教学管理层"，"教师层"，"学生层"这样3种层次结构；
  • 语法：设置层次函数

level... connect by [nocycle] prior 连接条件 [start with 开始条件]

level：可以根据数据所处的层次结构实现自动的层次编号，例如：1,2,3 connect by：指的是数据之间的连接，例如雇员数据依靠mgr找到其领导，就是一个连接条件，其中nocycle需要结合connect_byiscycle伪列确定出父子节点循环关系 start with：根节点数据的开始条件

• 观察分层的基本关系

SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,mgr,LEVEL FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ;

• 此sql根据雇员的领导关系，设置了一个基本的层次。利用level可以自动地根据所在层生成一个序号，而之所以在本程序之中使用了LPAD()函数，就是为了让层次结构看起来更加清晰；
• 利用树状模型图来理解分层的形式：
  • 

• connect_by_isleaf伪列
  • 在一个树状结构中，节点会分为两种，即根节点，叶子节点，用户可以利用connect_by_isleaf伪列判断某一个节点是根节点还是叶子节点。如果此列返回的是数字0，则表示根节点；如果返回为1，则表示为叶子节点
  • 示例：利用connect_by_isleaf判断某一个节点是根节点还是叶子节点

SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,mgr,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ;

• connect_by_root列语句
  • 该语句的主要作用是取得某一个字段在本次分层中的根节点数据名称，例如，如果按照领导层次划分，则所有数据的根节点都应该是king
  • 示例：该语句会根据根节点的起始位置开始列出节点数据。如果从king开始，则输出为king，如果从jones，则输出为jones

SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,mgr,LEVEL , CONNECT_BY_ROOT ename FROM emp CONNECT BY PRIOR empno=mgr START WITH empno=7566; 此处直接从7566数据开始列出

• sys_connect_by_path(列，char)函数
  • 可以利用此函数按照给出的节点关系，自动地将当前根节点中的所有相关路径进行显示
  • 示例1：利用此函数取得节点路径信息

SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || SYS_CONNECT_BY_PATH(ename,' => ') empname ,mgr,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ;

• 使用此sql，会输出所有的节点路径信息，如果不希望某个节点被显示，则需要在start with后面增加过滤条件即可，例如：empno!=7698，就表示7698这个节点的数据不被显示

• 示例2：去掉某一节点

SELECT empno,LPAD('|- ' , LEVEL * 2 , ' ') || SYS_CONNECT_BY_PATH(ename,' => ') empname ,mgr,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr AND empno!=7698 START WITH mgr IS NULL ;

• order siblings by 字段语句
  • 如果用户使用order by子句进行指定字段的排序，有可能会破坏数据的组成结构，例如下面的sql

SELECT ename,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ORDER BY ename ;

• 利用order siblings保持层次关系

SELECT ename,LPAD('|- ' , LEVEL * 2 , ' ') || ename empname ,LEVEL , DECODE (CONNECT_BY_ISLEAF , 0 , '根节点' , 1 , '叶子节点') isleaf FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ORDER siblings BY ename ;