溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹了oracle中connect by/level/start with怎么用，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。
level, connect_by_isleaf, connect_by_iscycle偽列:
level 就是這個數(shù)據(jù)屬于 哪一個等級，比如PRESIDENT為1，MANAGER為2
connect_by_isleaf 就是樹的最末端的值，或者說這個樹枝下已經(jīng)沒有樹葉了
connect_by_iscycle 導致出現(xiàn)死循環(huán)的那個樹枝
通過START WITH . . . CONNECT BY . . .子句來實現(xiàn)SQL的層次查詢.
自從Oracle 9i開始，可以通過 SYS_CONNECT_BY_PATH 函數(shù)實現(xiàn)將父節(jié)點到當前行內(nèi)容以“path”或者層次元素列表的形式顯示出來。
自從Oracle 10g 中，還有其他更多關(guān)于層次查詢的新特性 。例如，有的時候用戶更關(guān)心的是每個層次分支中等級最低的內(nèi)容。
那么你就可以利用偽列函數(shù)CONNECT_BY_ISLEAF來判斷當前行是不是葉子。如果是葉子就會在偽列中顯示“1”，
如果不是葉子而是一個分支（例如當前內(nèi)容是其他行的父親）就顯示“0”。
在Oracle 10g 之前的版本中，如果在你的樹中出現(xiàn)了環(huán)狀循環(huán)（如一個孩子節(jié)點引用一個父親節(jié)點），Oracle 就會報出一個錯誤提示：“ ORA-01436: CONNECT BY loop in user data”。如果不刪掉對父親的引用就無法執(zhí)行查詢操作。
而在 Oracle 10g 中，只要指定“NOCYCLE”就可以進行任意的查詢操作。與這個關(guān)鍵字相關(guān)的還有一個偽列——CONNECT_BY_ISCYCLE， 如果在當前行中引用了某個父親節(jié)點的內(nèi)容并在樹中出現(xiàn)了循環(huán)，那么該行的偽列中就會顯示“1”，否則就顯示“0”。
syntax 1 connect by [nocycle] start with
syntax 2 start with connect by [nocycle]
實驗之：scott用戶下emp表
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
查找員工編號為7876的領(lǐng)導:
select level,e.* from emp e connect by prior e.mgr = e.empno start with e.empno = 7876 order by level desc;
LEVEL EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
4 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
3 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
2 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
1 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
"start with" -- this identifies all level=1 nodes in the tree
"connect by" -- describes how to walk from the parent nodes above to their children and their childrens children.
Easiest to use an example on emp. if we start with "where mgr is null", we generate the set of employees that have no mgr (they are the top of the tree).
if we connect by prior empno = /* current */ mgr that will take all of the prior records (the start with at first) and find all records such that the mgr column equals their empno (find all the records of people managed by the people we started with).
使用with語句優(yōu)化查詢結(jié)果:優(yōu)化等級
with a as
(select max(level) + 1 lvl
from emp e
connect by prior e.mgr = e.empno
start with e.empno = 7876
order by level desc)
select a.lvl 最高等級加1,
level 當前等級,
a.lvl - level 優(yōu)化后等級,
e.* from a,
emp e connect by prior e.mgr = e.empno start with e.empno = 7876 order by level desc;
最高等級加1 當前等級 優(yōu)化后等級 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------- ---------- ---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
5 4 1 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
5 3 2 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
5 2 3 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
5 1 4 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
查找員工編號為7839的所有下屬(7839為king):
select level 等級, e.*
from emp e
connect by prior e.empno = e.mgr
start with e.empno = 7839;
等級 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
1 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
2 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
3 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
4 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
3 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
4 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
2 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
3 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
3 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
3 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
3 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
3 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
2 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
3 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
--構(gòu)造整個的層次結(jié)構(gòu)
select lpad(' ',level*2,' ')||ename ename,empno,mgr
from emp
start with mgr is null
connect by prior empno = mgr;
ENAME EMPNO MGR
------------------------------ ---------- ----------
KING 7839
JONES 7566 7839
SCOTT 7788 7566
ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 7782
14 rows selected.
so, king is the start with set then jones blake and clark fall under him. each of them becomes the prior record in turn and their trees are expanded.
使用connect by 結(jié)合 level構(gòu)造虛擬行:
select level from dual connect by level < 5;
LEVEL
----------
1
2
3
4
使用rownum實現(xiàn)類似的功能:
select rownum from dual connect by level < 5;
ROWNUM
----------
1
2
3
4
select level from dual connect by rownum <5;
LEVEL
----------
1
2
3
4
---------------------待續(xù)-----------------------
使用union all構(gòu)造兩層節(jié)點的樹:
視圖如下所示:
create or replace view tree_view as
select
'1' as rootnodeid,
'xxxx有限責任公司' as treename,
'-1' as parent_id
from dual
union
select
to_char(d.deptno),
d.dname || '_' ||d.loc,
'1' as parent_id
from dept d;
View created.
desc tree_view
Name Null? Type
-------------------------------------------------------------- ---------
ROOTNODEID VARCHAR2(40)
TREENAME VARCHAR2(28)
PARENT_ID VARCHAR2(2)
select * from tree_view;
ROOTNODEID TREENAME PA
---------------------------------------- ---------------------------- --
1 xxxx有限責任公司 -1
10 ACCOUNTING_NEW YORK 1
20 RESEARCH_DALLAS 1
30 SALES_CHICAGO 1
40 OPERATIONS_BOSTON 1
查詢語句:
select level,t.*
from tree_view t
start with t.parent_id = '-1'
connect by prior t.rootnodeid = t.parent_id;
LEVEL ROOTNODEID TREENAME PA
---------- ---------------------------------------- ---------------------------- --
1 1 xxxx有限責任公司 -1
2 10 ACCOUNTING_NEW YORK 1
2 20 RESEARCH_DALLAS 1
2 30 SALES_CHICAGO 1
2 40 OPERATIONS_BOSTON 1
--以下為更新內(nèi)容:
1、先查看總共有幾個等級：
select count(level)
from emp e
connect by prior e.empno = e.mgr
start with e.mgr is null;
COUNT(LEVEL)
------------
14
2、查看每個等級的人數(shù)。主要是通過level進行g(shù)roup by
select count(level)
from emp e
connect by prior e.empno = e.mgr
start with e.mgr is null
group by level;
COUNT(LEVEL)
------------
1
3
2
8
3、oracle 10g提供了一個簡單的connect_by_isleaf=1, 0 表示非葉子節(jié)點
select level as 等級, connect_by_isleaf as 是否是葉子節(jié)點, e.*
from emp e
connect by prior e.empno = e.mgr
start with e.mgr is null;
等級 是否是葉子節(jié)點 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ---------- ------------------------------ --------- ---------- ------------------- ----------
1 0 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
2 0 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
3 0 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
4 1 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
3 0 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
4 1 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
2 0 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
3 1 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
3 1 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
3 1 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
3 1 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
3 1 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
2 0 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
3 1 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
4、sys_connect_by_path
oracle 9i提供了sys_connect_by_path(column,char),其中column 是字符型或能自動轉(zhuǎn)換成字符型的列名。它的主要目的就是將父節(jié)點到當前節(jié)點的”path”按照指定的模式展現(xiàn)出現(xiàn)。char可以是單字符也可以是多字符，但不能使用列值中包含的字符，而且這個參數(shù)必須是常量，且不允許使用綁定變量，這個函數(shù)只能使用在層次查詢中。
select level as rank,
connect_by_isleaf as leaf_is_or_not,
ENAME
from emp e
connect by prior e.empno = e.mgr
start with e.mgr is null;
RANK LEAF_IS_OR_NOT ENAME
---------- -------------- ------------------------------
1 0 KING
2 0 JONES
3 0 SCOTT
4 1 ADAMS
3 1 FORD
2 0 BLAKE
3 1 ALLEN
3 1 WARD
3 1 MARTIN
3 1 TURNER
3 1 JAMES
RANK LEAF_IS_OR_NOT ENAME
---------- -------------- ------------------------------
2 0 CLARK
3 1 MILLER
13 rows selected.
select level as rank,
connect_by_isleaf as leaf_is_or_not,
lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '->') path
from emp e
connect by prior e.empno = e.mgr
start with e.mgr is null;
RANK LEAF_IS_OR_NOT PATH
---------- -------------- ----------------------------------------
1 0 ->KING
2 0 ->KING->JONES
3 0 ->KING->JONES->SCOTT
4 1 ->KING->JONES->SCOTT->ADAMS
3 1 ->KING->JONES->FORD
2 0 ->KING->BLAKE
3 1 ->KING->BLAKE->ALLEN
3 1 ->KING->BLAKE->WARD
3 1 ->KING->BLAKE->MARTIN
3 1 ->KING->BLAKE->TURNER
3 1 ->KING->BLAKE->JAMES
RANK LEAF_IS_OR_NOT PATH
---------- -------------- ----------------------------------------
2 0 ->KING->CLARK
3 1 ->KING->CLARK->MILLER
13 rows selected.
sys_connect_by_path就是從start with開始的地方開始遍歷，并記下其遍歷到的節(jié)點，start with開始的地方被視為根節(jié)點，將遍歷到的路徑根據(jù)函數(shù)中的
分隔符，組成一個新的字符串，這個功能是強大的。
實例：
--先查詢表：
ENAME 13 rows selected.SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------- ---------- ------------ ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------- ---------- ------------ ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
--接著查詢運用start with conncet by prior;
select ename
from scott.emp
start with ename = 'KING'
connect by prior empno = mgr;
ENAME
----------
KING
JONES
SCOTT
ADAMS
FORD
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
ENAME
----------
CLARK
MILLER
13 rows selected.
--最后運用sys_connect_by_path：
select sys_connect_by_path(ename, '->') "path"
from scott.emp
start with ename = 'KING'
connect by prior empno = mgr;
path
----------------------------------------
->KING
->KING->JONES
->KING->JONES->SCOTT
->KING->JONES->SCOTT->ADAMS
->KING->JONES->FORD
->KING->BLAKE
->KING->BLAKE->ALLEN
->KING->BLAKE->WARD
->KING->BLAKE->MARTIN
->KING->BLAKE->TURNER
->KING->BLAKE->JAMES
path
----------------------------------------
->KING->CLARK
->KING->CLARK->MILLER
13 rows selected.
5、修剪樹枝和節(jié)點:
過濾掉編號是7566的數(shù)據(jù)(修剪節(jié)點)，他指的是把這個節(jié)點給裁掉，但是并沒有破壞樹結(jié)構(gòu)，它的子節(jié)點還是可以正常的顯示。
select level as rank,
connect_by_isleaf as leaf_is_or_not,
lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '=>') path,
e.*
from emp e
where e.empno != 7566
connect by prior e.empno = e.mgr
start with e.mgr is null;
RANK LEAF_IS_OR_NOT PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- -------------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
1 0 =>KING 7839 KING PRESIDENT 17-NOV-81 5000 10
3 0 =>KING=>JONES=>SCOTT 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
4 1 =>KING=>JONES=>SCOTT=>ADAMS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
3 1 =>KING=>JONES=>FORD 7902 FORD ANALYST 7566 03-DEC-81 3000 20
2 0 =>KING=>BLAKE 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
3 1 =>KING=>BLAKE=>ALLEN 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
3 1 =>KING=>BLAKE=>WARD 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
3 1 =>KING=>BLAKE=>MARTIN 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
3 1 =>KING=>BLAKE=>TURNER 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
3 1 =>KING=>BLAKE=>JAMES 7900 JAMES CLERK 7698 03-DEC-81 950 30
2 0 =>KING=>CLARK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
RANK LEAF_IS_OR_NOT PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- -------------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
3 1 =>KING=>CLARK=>MILLER 7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
--裁掉編號是7698的節(jié)點和它的子節(jié)點:
select level as rank,
connect_by_isleaf as leaf_is_or_not,
lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '=>') path,
e.*
from emp e
connect by prior e.empno = e.mgr
and e.empno != 7698
start with e.mgr is null;
RANK LEAF_IS_OR_NOT PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- -------------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
1 0 =>KING 7839 KING PRESIDENT 17-NOV-81 5000 10
2 0 =>KING=>JONES 7566 JONES MANAGER 7839 02-APR-81 2975 20
3 0 =>KING=>JONES=>SCOTT 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
4 1 =>KING=>JONES=>SCOTT=>ADAMS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
3 1 =>KING=>JONES=>FORD 7902 FORD ANALYST 7566 03-DEC-81 3000 20
2 0 =>KING=>CLARK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
3 1 =>KING=>CLARK=>MILLER 7934 MILLER CLERK 7782 23-JAN-82 1300 10
7 rows selected.
6、connect_by_root的使用，oracle10g新增connect_by_root, 通過這個操作，可以獲取樹形查詢根記錄的字段。
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ------ ------ -------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ------ ------ -------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
--使用connect_by_root，查詢
select level as rank,
connect_by_isleaf as leaf_is_or_not,
connect_by_root ename,
lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '=>') path,
e.*
from emp e
connect by prior e.empno = e.mgr
start with e.mgr is null;
RANK LEAF_IS_OR_NOT CONNECT_BY PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- -------------- ---------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
1 0 KING =>KING 7839 KING PRESIDENT 17-NOV-81 5000 10
2 0 KING =>KING=>JONES 7566 JONES MANAGER 7839 02-APR-81 2975 20
3 0 KING =>KING=>JONES=>SCOTT 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
4 1 KING =>KING=>JONES=>SCOTT=>ADAMS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
3 1 KING =>KING=>JONES=>FORD 7902 FORD ANALYST 7566 03-DEC-81 3000 20
2 0 KING =>KING=>BLAKE 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
3 1 KING =>KING=>BLAKE=>ALLEN 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
3 1 KING =>KING=>BLAKE=>WARD 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
3 1 KING =>KING=>BLAKE=>MARTIN 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
3 1 KING =>KING=>BLAKE=>TURNER 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
3 1 KING =>KING=>BLAKE=>JAMES 7900 JAMES CLERK 7698 03-DEC-81 950 30
RANK LEAF_IS_OR_NOT CONNECT_BY PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- -------------- ---------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
2 0 KING =>KING=>CLARK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
3 1 KING =>KING=>CLARK=>MILLER 7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
上述sql等同于如下：
select level as rank,
connect_by_isleaf as leaf_is_or_not,
connect_by_root(ename) root_ename,
lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '=>') path,
e.*
from emp e
connect by prior e.empno = e.mgr
start with e.mgr is null;
RANK LEAF_IS_OR_NOT ROOT_ENAME PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- -------------- ---------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
1 0 KING =>KING 7839 KING PRESIDENT 17-NOV-81 5000 10
2 0 KING =>KING=>JONES 7566 JONES MANAGER 7839 02-APR-81 2975 20
3 0 KING =>KING=>JONES=>SCOTT 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
4 1 KING =>KING=>JONES=>SCOTT=>ADAMS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
3 1 KING =>KING=>JONES=>FORD 7902 FORD ANALYST 7566 03-DEC-81 3000 20
2 0 KING =>KING=>BLAKE 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
3 1 KING =>KING=>BLAKE=>ALLEN 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
3 1 KING =>KING=>BLAKE=>WARD 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
3 1 KING =>KING=>BLAKE=>MARTIN 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
3 1 KING =>KING=>BLAKE=>TURNER 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
3 1 KING =>KING=>BLAKE=>JAMES 7900 JAMES CLERK 7698 03-DEC-81 950 30
RANK LEAF_IS_OR_NOT ROOT_ENAME PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- -------------- ---------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
2 0 KING =>KING=>CLARK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
3 1 KING =>KING=>CLARK=>MILLER 7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
對于層次查詢?nèi)绻胦rder by排序，比如order by last_name則是先做完層次獲得level,然后按last_name 排序，這樣破壞了層次，比如特別關(guān)注某行的深度，按level 排序，也是會破壞層次的。在oracle10g中，增加了siblings 關(guān)鍵字的排序。
語法：order siblings by
它會保護層次，并且在每個等級中按expre排序。
select level as rank,
connect_by_isleaf as leaf_is_or_not,
lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '=>') path,
e.*
from emp e
connect by prior e.empno = e.mgr
start with e.mgr is null
order siblings by e.ename;
RANK LEAF_IS_OR_NOT PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- -------------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
1 0 =>KING 7839 KING PRESIDENT 17-NOV-81 5000 10
2 0 =>KING=>BLAKE 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
3 1 =>KING=>BLAKE=>ALLEN 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
3 1 =>KING=>BLAKE=>JAMES 7900 JAMES CLERK 7698 03-DEC-81 950 30
3 1 =>KING=>BLAKE=>MARTIN 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
3 1 =>KING=>BLAKE=>TURNER 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
3 1 =>KING=>BLAKE=>WARD 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
2 0 =>KING=>CLARK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
3 1 =>KING=>CLARK=>MILLER 7934 MILLER CLERK 7782 23-JAN-82 1300 10
2 0 =>KING=>JONES 7566 JONES MANAGER 7839 02-APR-81 2975 20
3 1 =>KING=>JONES=>FORD 7902 FORD ANALYST 7566 03-DEC-81 3000 20
RANK LEAF_IS_OR_NOT PATH EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---- -------------- ----------------------------------- ---------- ---------- --------- ---------- ------------ ------ ------ -------
3 0 =>KING=>JONES=>SCOTT 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
4 1 =>KING=>JONES=>SCOTT=>ADAMS 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
13 rows selected.
connect_by_iscycle(存在循環(huán)，將返回1，否則返回0)
the connect_by_iscycle pseudocolumn returns 1 if the current row has a child which is also its ancestor. otherwise it returns 0.
you can specify connect_by_iscycle only if you have specified the nocycle parameter of the connect by clause. nocycle enables oracle to return the results of a query that would otherwise fail because of a connect by loop in the data.
感謝你能夠認真閱讀完這篇文章，希望小編分享的“oracle中connect by/level/start with怎么用”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學習!
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
