Oracle之设置数据层次

一. 设置层次函数

     

 

二. 示例

1. 观察分层的基本关系

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

　　　　

#操作是从大boss开始（empno == null），而后按照领导的结构分层。

 

1.1 层级结构：

     

 

 2. CONNECT_BY_ISLEAF伪列

     

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

　　　　

 

 3. CONNECT_BY_ROOT列

      

 

 3.1

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

　　　　

 

 3.2 最大的根节点就是 大boss，现在换一种方式，从一个指定的雇员信息开始列出

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

　　　　

 

 4. SYS_CONNECT_BY_PATH(列，char) 函数

   

 

 4.1

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

　　　　

 

4.2 去掉某一个节点

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

　　　　

 

 5. ORDER SIBLINGS BY 字段

   

 

 5.1 破坏程序结构的显示（直接使用ORDER BY 排序）

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;        #此行造成的

　　　　

 

 5.2 正常显示（ORDER SIBLINGS BY）

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;   

　　　　

 

 6. CONNECT_BY_ISCYCLE 伪列

    

 

 6.1 原本大boss没有领导，为了发现问题，让大boss有一个领导，将领导设置为  7692

update emp set mgr=7389 where empno=7692;

 

执行下面代码，结果：

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

 　　　　

由于存在了循环，所以显示不了。

 

6.2 不让其循环（添加 NOCYCLE）

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

 　　　　

 

 7.  CONNECT_BY_ISCYCLE（是否有 循环）

SELECT ename,LPAD('|-',LEVEL * 2,' ') || ename empname,LEVEL,
    DECODE(CONNECT_BY_ISLEAF,0,'根节点'，1,'   叶子结点') isleaf,
    DECODE(CONNECT_BY_ISCYCLE,0,'【✔】没有循环',1,'【✘】存在循环')
FROM emp
CONNECT BY NOCYCLE PRIOR empno=mgr
START WITH empno=7692
ORDER SIBLINGS BY ename;