这篇文章主要介绍了oracle中connect by/level/start with怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。
level, connect_by_isleaf, connect_by_iscycle伪列:
level 就是这个数据属于 哪一个等级，比如PRESIDENT为1，MANAGER为2
connect_by_isleaf 就是树的最末端的值，或者说这个树枝下已经没有树叶了
connect_by_iscycle 导致出现死循环的那个树枝
通过START WITH . . . CONNECT BY . . .子句来实现SQL的层次查询.
自从Oracle 9i开始，可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。
自从Oracle 10g 中，还有其他更多关于层次查询的新特性 。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”，
如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。
在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），Oracle 就会报出一个错误提示：“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。
而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE， 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“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的领导:
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语句优化查询结果:优化等级
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 优化后等级,
e.* from a,
emp e connect by prior e.mgr = e.empno start with e.empno = 7876 order by level desc;
最高等级加1 当前等级 优化后等级 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
--构造整个的层次结构
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 结合 level构造虚拟行:
select level from dual connect by level < 5;
LEVEL
----------
1
2
3
4
使用rownum实现类似的功能:
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
---------------------待续-----------------------
使用union all构造两层节点的树:
视图如下所示:
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
--以下为更新内容:
1、先查看总共有几个等级：
select count(level)
from emp e
connect by prior e.empno = e.mgr
start with e.mgr is null;
COUNT(LEVEL)
------------
14
2、查看每个等级的人数。主要是通过level进行group 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 表示非叶子节点
select level as 等级, connect_by_isleaf as 是否是叶子节点, e.*
from emp e
connect by prior e.empno = e.mgr
start with e.mgr is null;
等级 是否是叶子节点 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 是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。char可以是单字符也可以是多字符，但不能使用列值中包含的字符，而且这个参数必须是常量，且不允许使用绑定变量，这个函数只能使用在层次查询中。
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开始的地方开始遍历，并记下其遍历到的节点，start with开始的地方被视为根节点，将遍历到的路径根据函数中的
分隔符，组成一个新的字符串，这个功能是强大的。
实例：
--先查询表：
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、修剪树枝和节点:
过滤掉编号是7566的数据(修剪节点)，他指的是把这个节点给裁掉，但是并没有破坏树结构，它的子节点还是可以正常的显示。
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的节点和它的子节点:
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.
对于层次查询如果用order by排序，比如order by last_name则是先做完层次获得level,然后按last_name 排序，这样破坏了层次，比如特别关注某行的深度，按level 排序，也是会破坏层次的。在oracle10g中，增加了siblings 关键字的排序。
语法：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(存在循环，将返回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怎么用”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
