oracle sys_connect_by_root,ORACLE 的CONNECT BY、START WITH,CONNECT_BY_ROOT、CONNECT_BY_ISLEAF、SYS_CONNE...

最新推荐文章于 2023-08-10 20:13:43 发布

weixin_39948247

最新推荐文章于 2023-08-10 20:13:43 发布

阅读量302

点赞数

文章标签： oracle sys_connect_by_root

如果表中存在層次數據，則可以使用層次化查詢子句查詢出表中行記錄之間的層次關系

基本語法：

START WITH CONNECT BY [NOCYCLE ]

注： [ NOCYCLE ]參數暫時不解釋

例：

SQL> select empno, ename, job, mgr, hiredate, sal, comm, deptno, level as lv fromemp2 start with empno = 7839 connect by (prior empno) =mgr;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO LV----- ---------- ------------------------------ ----- ----------- --------- --------- ------ ----------

7839 KING PRESIDENT 1981-11-17 5000.25 10 1

7566 JONES MANAGER 7839 1981-04-02 2975.25 20 2

7788 SCOTT ANALYST 7566 1982-12-09 3000.25 20 3

7876 ADAMS CLERK 7788 1983-01-12 1100.25 20 4

7902 FORD ANALYST 7566 1981-12-03 3000.25 20 3

7369 SMITH CLERK 7902 1980-12-17 800.25 20 4

7698 BLAKE MANAGER 7839 1981-05-01 2850.25 30 2

120 gxl SALESMAN 7698 2018-02-08 5500.25 300.00 10 3

7499 ALLEN SALESMAN 7698 1981-02-20 1600.25 300.00 30 3

7521 WARD SALESMAN 7698 1981-02-22 1250.25 500.00 30 3

7654 MARTIN SALESMAN 7698 1981-09-28 1250.25 1400.00 30 3

7844 TURNER SALESMAN 7698 1981-09-08 1500.25 0.00 30 3

7900 JAMES CLERK 7698 1981-12-03 950.25 30 3

7782 CLARK MANAGER 7839 1981-06-09 2450.25 10 2

7934 MILLER CLERK 7782 1982-01-23 1300.25 10 3

15 rows selected

表中存在層次數據

數據之間的層次關系即父子關系，通過表中列與列間的關系來描述，如EMP表中的EMPNO和MGR。EMPNO表示雇員編號，MGR表示領導

該雇員的人的編號,在表的每一行中都有一個表示父節點的MGR(除根節點外)，通過每個節點的父節點，就可以確定整個樹結構。

CONNECT BY [ NOCYCLE ] CONDITION2 層次子句作用

CONDITION2 [PRIOR expr = expr] : 指定層次結構中父節點與子節點之之間的關系。

CONDITION2 中的一元運算符 PRIORY 必須放置在連接關系的兩列中某一個的前面。在連接關系中，除了可以使用列名外，還允許使用列表達式。

1.START WITH

start with 子句為可選項，用來標識哪行作為查找樹型結構的第一行(即根節點，可指定多個根節點)。若該子句被省略，則表示所有滿足查詢條件的行作為根節點。

2．關於PRIOR

PRIOR 置於運算符前后的位置，決定着查詢時的檢索順序。

2.1 從根節點自頂向下

SQL> select empno, ename, mgr, level as lv from emp start with mgr is null

2 connect by (prior empno) = mgr order by level;

EMPNO ENAME MGR LV----- ---------- ----- ----------

7839 KING 1

7566 JONES 7839 2

7782 CLARK 7839 2

7698 BLAKE 7839 2

7902 FORD 7566 3

7521 WARD 7698 3

7788 SCOTT 7566 3

7900 JAMES 7698 3

120 gxl 7698 3

7499 ALLEN 7698 3

7934 MILLER 7782 3

7654 MARTIN 7698 3

7844 TURNER 7698 3

7876 ADAMS 7788 4

7369 SMITH 7902 4

15 rows selected

----分析

層次查詢執行邏輯:

a. 確定上一行(相對於步驟b中的當前行)，若start with 子句存在，則以該語句確定的行為上一行，若不存在則將所有的數據行視為上一行。

b. 從上一行出發，掃描除該行之外所有數據行。

c. 匹配條件(prior empno) = mgr，注意一元運算符 prior，意思是之前的，指上一行

當前行定義：步驟b中掃描得到的所有行中的某一行

匹配條件含義:當前行字段 mgr 的值等於上一行字段 empno中的值,若滿足則取出該行，並將level + 1，

匹配完所有行記錄后，將滿足條件的行作為上一行，執行步驟 b，c。直到所有行匹配結束.

2.2 從根節點自底向上

SQL> select empno, ename, mgr, level as lv from emp start with empno = 7876

2 connect by (prior mgr ) = empno order by level;

EMPNO ENAME MGR LV----- ---------- ----- ----------

7876 ADAMS 7788 1

7788 SCOTT 7566 2

7566 JONES 7839 3

7839 KING 4

--分析

層次查詢執行邏輯:

a .確定上一行(相對於步驟b中的當前行)，若start with子句存在，則以該語句確定的行為上一行，若不存在則將所有的數據行視為上一行。

b .從上一行出發，掃描除該行之外所有數據行。

c .匹配條件 (prior mgr ) = empno

注意一元運算符 prior，意思是之前的，指上一行

當前行定義：步驟b中掃描得到的所有行中的某一行

匹配條件含義:當前行字段empno 的值等於上一行字段 mgr 中的值,若滿足則取出該行，並將

level + 1，

匹配完所有行記錄后，將滿足條件的行作為上一行，執行步驟 b，c。直到所有行匹配結束.

一、基本語法

SELECT * FROM TABLE START WITH CONNECT BY [PRIOR] id= parentid；

層次查詢(遞歸查詢)用來查找存在父子關系的數據，也就是樹形結構的數據；其返還的數據也能夠明確的區分出每一層的數據。

"start with " -- 代表在這棵樹中你要開始遍歷的的節點，是用來限制第一層的數據，或者叫根節點數據；以這部分數據

為基礎來查找第二層數據，然后以第二層數據查找第三層數據以此類推。

"connect by prior id = parentid" -- 標示節點之間的父子關系，是用來指明Oracle在查找數據時以怎樣的一種關系去查找；比如說查

找第二層的數據時用第一層數據的id去跟表里面記錄的parentid字段進行匹配，如果這個條件成立

那么查找出來的數據就是第二層數據，同理查找第三層第四層…等等都是按這樣去匹配。

"level"-- 關鍵字，代表樹形結構中的層級編號；第一層是數字1，第二層數字2，依次遞增。

prior還有一種用法：

select * from table [start with condition1]connectby id= [prior]parentid 或者：select * from table [start with condition1]connectby [prior] parentid = id

二、分層查詢的例子

1.從根節點查找葉子節點，即從根節點自頂向下

SQL> select level rn , empno,mgr,substr(sys_connect_by_path(ename,'-->'),4) fromemp2 start with ename = 'KING' connect by prior empno =mgr;

RN EMPNO MGR SUBSTR(SYS_CONNECT_BY_PATH(ENA---------- ----- ----- --------------------------------------------------------------------------------

1 7839KING2 7566 7839 KING-->JONES

3 7788 7566 KING-->JONES-->SCOTT

4 7876 7788 KING-->JONES-->SCOTT-->ADAMS

3 7902 7566 KING-->JONES-->FORD

4 7369 7902 KING-->JONES-->FORD-->SMITH

2 7698 7839 KING-->BLAKE

3 120 7698 KING-->BLAKE-->gxl

3 7499 7698 KING-->BLAKE-->ALLEN

3 7521 7698 KING-->BLAKE-->WARD

3 7654 7698 KING-->BLAKE-->MARTIN

3 7844 7698 KING-->BLAKE-->TURNER

3 7900 7698 KING-->BLAKE-->JAMES

2 7782 7839 KING-->CLARK

3 7934 7782 KING-->CLARK-->MILLER

15 rows selected

2.從葉子節點查找上層節點

SQL> select level rn , empno, mgr, substr(sys_connect_by_path(ename,'-->'),4) tb fromemp2 start with ename = 'ADAMS' connect by prior mgr =empno;

RN EMPNO MGR TB---------- ----- ----- --------------------------------------------------------------------------------

1 7876 7788ADAMS2 7788 7566 ADAMS-->SCOTT

3 7566 7839 ADAMS-->SCOTT-->JONES

4 7839 ADAMS-->SCOTT-->JONES-->KING

三、sys_connect_by_path 遞歸函數

SYS_CONNECT_BY_PATH( cln, fmt ) :這個函數是oracle9i才新提出來的！它一定要和connect by子句合用！

第一個參數cln是形成樹形式的字段，第二個參數fmt是父級和其子級分隔顯示用的分隔符,

CONNECT BY PRIOR 是標示父子關系的對應！

SQL> SELECT level,empno,mgr,SYS_CONNECT_BY_PATH(ename, '>') pt2 FROM emp START WITH ename = 'KING' CONNECT BY PRIOR empno =mgr;LEVELEMPNO MGR PT---------- ----- ----- --------------------------------------------------------------------------------

1 7839 >KING2 7566 7839 >KING>JONES3 7788 7566 >KING>JONES>SCOTT4 7876 7788 >KING>JONES>SCOTT>ADAMS3 7902 7566 >KING>JONES>FORD4 7369 7902 >KING>JONES>FORD>SMITH2 7698 7839 >KING>BLAKE3 120 7698 >KING>BLAKE>gxl3 7499 7698 >KING>BLAKE>ALLEN3 7521 7698 >KING>BLAKE>WARD3 7654 7698 >KING>BLAKE>MARTIN3 7844 7698 >KING>BLAKE>TURNER3 7900 7698 >KING>BLAKE>JAMES2 7782 7839 >KING>CLARK3 7934 7782 >KING>CLARK>MILLER15 rows selected