Oracle層級詢語句connect by 用法詳解

標籤：rom   HERE   ott   from   死迴圈   選擇   name   log   with   

如果表中包含層級資料，那麼你就可以使用層級查詢從句選擇行層級順序。

1.層級查詢從句文法

層級查詢從句文法：

{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...
}

 

START WITH：指定層級的跟節點行。

CONNECT BY：指定層級的父行於子行的關係。

• NOCYCLE參數指示Oracle資料庫查詢返回行，即使CONNECT BY在資料中存在迴圈。通常與CONNECT_BY_ISCYCLE偽列一起使用查看行是否包含迴圈。
• 在層級查詢中，運算式的條件必須使用PRIOR運算子加以限定來查詢父行，例如：

         ... PRIOR expr = expr
         or
         ... expr = PRIOR expr

      如果CONNECT BY的條件是複合條件，只有一個條件PRIOR運算子是必須的，然而可以有多個PRIOR條件。例如：

      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 ...

      PRIOR是一個一元運算子和一元+-算術運算子具有相同的優先順序。PRIOR根據層級查詢中的運算式立刻計算出當前行的父行。

      PRIOR必須與比較列值的相等運算子一起使用（PRIOD關鍵字可以相等運算子的任意一邊）。

CONNECT BY條件和PRIOD表達兩者之間形成一個不相關的子查詢結構。因此CURRVAL和NEXTVAL是無效的PRIOR運算式，所以PRIOR表達不能用於查詢序列。

通過使用CONNECT_BY_ROOT運算子來限定在查詢列表中的列，可以進一步細化層級查詢。這個運算子擴充了層級查詢CONNECT BY [PRIOR]條件的功能，不僅立即返回父行，而且還返回階層中的所有行根節點行。

 

2.層級查詢偽列

層級查詢偽列只有在層級查詢中是有效，層級查詢偽列：

2.1.CONNECT_BY_ISCYCLE偽列

如果當前行有一個子行，且子行又是當前行的祖先行，CONNECT_BY_ISCYCLE返回1，否則返回0.

只有在CONNECT BY從句中指定了NOCYCLE參數，才能指定CONNECT_BY_ISCYCLE。由於CONNECT BY存在迴圈資料，NOCYCLE能使Oracle返回查詢結果，否則將查詢失敗。

2.2.CONNECT_BY_ISLEAF偽列

如果當前行是CONNECT BY條件定義樹的葉子節點，CONNECT_BY_ISLEAF偽列返回1，否則返回0。該資訊也表明了一個給定的行是否可以進一步擴張，表現出更多的層次。

2.3.LEVEL偽列

層級查詢返回的每一行，跟節點行LEVEL偽列返回1，跟節點的子節點行LEVEL為例返回2等等。跟節點行是倒置樹的最高行。子節點行是任意非跟節點行。父節點行是任意有子節點的行。葉子節點行是任意沒有子節點行。

 

 

3.EXAMPLES3.1.CONNECT BY Example

查詢所有員工的上級。

 SQL> select e.empno, e.ename, e.mgr from emp e connect by prior e.empno = e.mgr;
 
EMPNO ENAME        MGR
----- ---------- -----
 7788 SCOTT       7566
 7876 ADAMS       7788
 7902 FORD        7566
 7369 SMITH       7902
 7499 ALLEN       7698
 7900 JAMES       7698
 7844 TURNER      7698
 7654 MARTIN      7698
 7521 WARD        7698
 7934 MILLER      7782
 7876 ADAMS       7788
 7566 JONES       7839
 7788 SCOTT       7566
 7876 ADAMS       7788
 7902 FORD        7566
 7369 SMITH       7902

 ........

 3.2.LEVEL Example

使用level虛列顯示父行於子行的層級

SQL> select e.empno, e.ename, e.mgr,level from emp e connect by prior e.empno = e.mgr;
 
EMPNO ENAME        MGR      LEVEL
----- ---------- ----- ----------
 7788 SCOTT       7566          1
 7876 ADAMS       7788          2
 7902 FORD        7566          1
 7369 SMITH       7902          2
 7499 ALLEN       7698          1
 7900 JAMES       7698          1
 7844 TURNER      7698          1
 7654 MARTIN      7698          1
 7521 WARD        7698          1
 7934 MILLER      7782          1
 7876 ADAMS       7788          1
 7566 JONES       7839          1
 7788 SCOTT       7566          2
 7876 ADAMS       7788          3
 7902 FORD        7566          2
 7369 SMITH       7902          3

............

 

3.3.START WITH Examples

從員工KING開始，查詢出所有員工的上級

SQL> select e.empno, e.ename, e.mgr, level
  2    from emp e
  3  connect by prior e.empno = e.mgr
  4   start with e.ename = ‘KING‘;
 
EMPNO ENAME        MGR      LEVEL
----- ---------- ----- ----------
 7839 KING                      1
 7566 JONES       7839          2
 7788 SCOTT       7566          3
 7876 ADAMS       7788          4
 7902 FORD        7566          3
 7369 SMITH       7902          4
 7698 BLAKE       7839          2
 7499 ALLEN       7698          3
 7521 WARD        7698          3
 7654 MARTIN      7698          3
 7844 TURNER      7698          3
 7900 JAMES       7698          3
 7782 CLARK       7839          2
 7934 MILLER      7782          3

 

3.4.NOCYCLE Examples

建立一個connect by 迴圈資料，將員工SCOTT指定為員工KING的上級，這樣會就出現一個死迴圈。

建立迴圈資料：

SQL> update emp e set e.mgr = ‘7788‘ where e.ename = ‘KING‘;
 
查詢以KING開始的員工的上級：

SQL> select e.empno, e.ename, e.mgr, level
  2    from emp e
  3  connect by prior e.empno = e.mgr
  4   start with e.ename = ‘KING‘;
 
select e.empno, e.ename, e.mgr, level
  from emp e
connect by prior e.empno = e.mgr
 start with e.ename = ‘KING‘
 
ORA-01436: 使用者資料中的 CONNECT BY 迴圈

 

使用NOCYCLE參數，查詢以KING開始的員工上級：

SQL> select e.empno, e.ename, e.mgr, level, connect_by_iscycle "CYCLE"
  2    from emp e
  3  connect by nocycle prior e.empno = e.mgr
  4   start with e.ename = ‘KING‘;
 
EMPNO ENAME        MGR      LEVEL      CYCLE
----- ---------- ----- ---------- ----------
 7839 KING        7788          1          0
 7566 JONES       7839          2          0
 7788 SCOTT       7566          3          1
 7876 ADAMS       7788          4          0
 7902 FORD        7566          3          0
 7369 SMITH       7902          4          0
 7698 BLAKE       7839          2          0
 7499 ALLEN       7698          3          0
 7521 WARD        7698          3          0
 7654 MARTIN      7698          3          0
 7844 TURNER      7698          3          0
 7900 JAMES       7698          3          0
 7782 CLARK       7839          2          0
 7934 MILLER      7782          3          0

Oracle層級詢語句connect by 用法詳解