1 SELECT LEVEL,E.* FROM EMP E CONNECT BY PRIOR E.MGR = E.EMPNO  START WITH E.EMPNO = 7876
2 ORDER BY LEVEL DESC

 1 WITH A AS
 2  (SELECT MAX(LEVEL) + 1 LVL
 3     FROM EMP E
 4   CONNECT BY PRIOR E.MGR = E.EMPNO
 5    START WITH E.EMPNO = 7876
 6    ORDER BY LEVEL DESC)
 7 SELECT A.LVL 最高等級(jí)加1,
 8        LEVEL 當(dāng)前等級(jí),
 9        A.LVL - LEVEL 優(yōu)化后等級(jí),
10        E.* 　FROM A,
11        EMP E CONNECT BY PRIOR E.MGR = E.EMPNO START WITH E.EMPNO = 7876 ORDER BY LEVEL DESC

1 SELECT LEVEL 等級(jí), E.*
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.EMPNO = 7839

1 select lpad(' ',level*2,' ')||ename ename, empno, mgr
2     from emp
3     START WITH MGR IS NULL
4     CONNECT BY PRIOR EMPNO = MGR

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

 1 CREATE OR REPLACE VIEW TREE_VIEW AS
 2 SELECT
 3  '1' AS rootnodeid,
 4  'xxxx有限責(zé)任公司' AS treename,
 5  '-1'  AS parent_id
 6 FROM dual
 7 UNION
 8 SELECT
 9   to_char(d.deptno),
10   d.dname || '_' ||d.loc,
11   '1' AS parent_id
12  FROM dept d;

1 SELECT T.*, LEVEL
2   FROM TREE_VIEW T
3  START WITH T.PARENT_ID = '-1'
4 CONNECT BY PRIOR T.ROOTNODEID = T.PARENT_ID

1 SELECT COUNT(LEVEL)
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.MGR IS NULL;

1 SELECT COUNT(LEVEL)
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.MGR IS NULL
5  GROUP BY LEVEL;

1 SELECT LEVEL AS 等級(jí), CONNECT_BY_ISLEAF AS 是否是葉子節(jié)點(diǎn), E.*
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.MGR IS NULL

1 SELECT LEVEL AS 等級(jí),
2        CONNECT_BY_ISLEAF AS 是否是葉子節(jié)點(diǎn),
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>')
4   FROM EMP E
5 CONNECT BY PRIOR E.EMPNO = E.MGR
6  START WITH E.MGR IS NULL;

1 SELECT LEVEL AS 等級(jí),
2        CONNECT_BY_ISLEAF AS 是否是葉子節(jié)點(diǎn),
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
4        E.*
5   FROM EMP E
6 WHERE e.empno != 7566
7 CONNECT BY PRIOR E.EMPNO = E.MGR
8  START WITH E.MGR IS NULL;

1 SELECT LEVEL AS 等級(jí),
2        CONNECT_BY_ISLEAF AS 是否是葉子節(jié)點(diǎn),
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
4        E.*
5   FROM EMP E
6 CONNECT BY PRIOR E.EMPNO = E.MGR
7        AND E.EMPNO != 7698
8  START WITH E.MGR IS NULL;

1 SELECT LEVEL AS 等級(jí),
2        CONNECT_BY_ISLEAF AS 是否是葉子節(jié)點(diǎn),
3        CONNECT_BY_ROOT ENAME,
4        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
5        E.*
6   FROM EMP E
7 CONNECT BY PRIOR E.EMPNO = E.MGR
8  START WITH E.MGR IS NULL;

1 SELECT LEVEL AS 等級(jí),
2        CONNECT_BY_ISLEAF AS 是否是葉子節(jié)點(diǎn),
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
4        E.*
5   FROM EMP E
6 CONNECT BY PRIOR E.EMPNO = E.MGR 
7  START WITH E.MGR IS NULL
8  ORDER SIBLINGS BY  E.ENAME;