1、CONNECT_BY_ISLEAF
在 10g 中 Oracle 提供了新的伪列： CONNECT_BY_ISLEAF ，通过这个伪列，可以判断当前的记录是否是树的叶节点。
这里描述一下在 9i 中如何实现相应的功能。
首先构造一个例子：
SQL> CREATE TABLE T_TREE (ID NUMBER PRIMARY KEY, FATHER_ID NUMBER, NAME VARCHAR2(30));
说明：
ID：节点ID
FATHER_ID ：父节点ID
NAME ：名称
表已创建。
SQL> INSERT INTO T_TREE VALUES (1, 0, 'A');
已创建 1 行。
SQL> INSERT INTO T_TREE VALUES (2, 1, 'BC');
已创建 1 行。
SQL> INSERT INTO T_TREE VALUES (3, 1, 'DE');
已创建 1 行。
SQL> INSERT INTO T_TREE VALUES (4, 1, 'FG');
已创建 1 行。
SQL> INSERT INTO T_TREE VALUES (5, 2, 'HIJ');
已创建 1 行。
SQL> INSERT INTO T_TREE VALUES (6, 4, 'KLM');
已创建 1 行。
SQL> INSERT INTO T_TREE VALUES (7, 6, 'NOPQ');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL>
SELECT * FROM T_TREE;
ID FATHER_ID NAME
---------- ---------- ------------------------------
1 0 A
2 1 BC
3 1 DE
4 1 FG
5 2 HIJ
6 4 KLM
7 6 NOPQ
已选择 7 行。
下面看看 CONNECT_BY_ISLEAF 的功能：
SQL>
SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF
FROM T_TREE
START WITH FATHER_ID = 0
CONNECT BY PRIOR ID = FATHER_ID;
ID FATHER_ID NAME LEAF
---------- ---------- ------------------------------ ----------
1 0 A 0
2 1 BC 0
5 2 HIJ 1
3 1 DE 1
4 1 FG 0
6 4 KLM 0
7 6 NOPQ 1
已选择 7 行。
SQL>
SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF
FROM T_TREE
START WITH ID = 7
CONNECT BY PRIOR FATHER_ID = ID;
ID FATHER_ID NAME LEAF
---------- ---------- ------------------------------ ----------
7 6 NOPQ 0
6 4 KLM 0
4 1 FG 0
1 0 A 1
CONNECT_BY_ISLEAF 可以判断当前记录是否是树的叶节点。而这个功能在 9i 中没有简单的方法来实现，只能通过分析函数来进行判断：
SQL>
SELECT ID, FATHER_ID, NAME, CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
FROM
(
SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS
FROM T_TREE
START WITH FATHER_ID = 0
CONNECT BY PRIOR ID = FATHER_ID
);
ID FATHER_ID NAME LEAF
---------- ---------- ------------------------------ ----------
1 0 A 0
2 1 BC 0
5 2 HIJ 1
3 1 DE 1
4 1 FG 0
6 4 KLM 0
7 6 NOPQ 1
已选择 7 行。
SQL>
SELECT ID, FATHER_ID, NAME, CASE WHEN LEAD(LEVELS) OVER(ORDER BY RN) > LEVELS THEN 0 ELSE 1 END LEAF
FROM
(
SELECT ROWNUM RN, ID, FATHER_ID, NAME, LEVEL LEVELS
FROM T_TREE
START WITH ID = 7
CONNECT BY PRIOR FATHER_ID = ID
);
ID FATHER_ID NAME LEAF
---------- ---------- ------------------------------ ----------
7 6 NOPQ 0
6 4 KLM 0
4 1 FG 0
1 0 A 1
利用分析函数可以相对简单的在 9i 实现 CONNECT_BY_ISLEAF 伪列的功能。
转载：https://blog.csdn.net/wanghai__/article/details/5644451
2、LEVE、CONNECT_BY_ISLEAF、CONNECT_BY_ISCYCLE 伪列
level 就是这个数据属于 哪一个等级，比如PRESIDENT为1，MANAGER为2
connect_by_isleaf 就是树的最末端的值，或者说这个树枝下已经没有树叶了
connect_by_iscycle 导致出现死循环的那个树枝
查询scott 的emp 表
SQL>
select empno,level,rpad('*',level,'*')||ename name,job,mgr
from emp
start with empno=7839 connect by prior empno=mgr;
EMPNO LEVEL NAME JOB MGR
----- ---------- ------------ --------- -----
7839 1 *KING PRESIDENT --level 为1
7566 2 **JONES MANAGER 7839 --level 为2
7788 3 ***SCOTT ANALYST 7566
7876 4 ****ADAMS CLERK 7788
7902 3 ***FORD ANALYST 7566
7369 4 ****SMITH CLERK 7902
7698 2 **BLAKE MANAGER 7839
7499 3 ***ALLEN SALESMAN 7698
7521 3 ***WARD SALESMAN 7698
7654 3 ***MARTIN SALESMAN 7698
7844 3 ***TURNER SALESMAN 7698
7900 3 ***JAMES CLERK 7698
7782 2 **CLARK MANAGER 7839
7934 3 ***MILLER CLERK 7782
--认为的创造一个错误的数据
update emp set mgr=7902 where empno=7839;
commit;
--现在的数据变成了
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 10
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20
7839 KING PRESIDENT 7902 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-03 950.00 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 10
SQL> select empno,rpad('*',level,'*')||ename name,job,mgr,sal from emp start with empno=7839 connect by prior empno=mgr;
select empno,rpad('*',level,'*')||ename name,job,mgr,sal from emp start with empno=7839 connect by prior empno=mgr
ORA-01436: 用户数据中的 CONNECT BY 循环
SQL> select empno,
2 level,
3 CONNECT_BY_ISLEAF isleaf ,
4 connect_by_iscycle iscycle,
5 rpad('*', level, '*') || ename name,
6 job,
7 mgr
8 from emp
9 start with empno = 7839
10 connect by nocycle prior empno = mgr;
EMPNO LEVEL ISLEAF ISCYCLE NAME JOB MGR
----- ---------- ---------- ---------- ------------ --------- -----
7839 1 0 0 *KING PRESIDENT 7902
7566 2 0 0 **JONES MANAGER 7839
7788 3 0 0 ***SCOTT ANALYST 7566
7876 4 1 0 ****ADAMS CLERK 7788
7902 3 0 1 ***FORD ANALYST 7566 --这一行和第一行死循环了
7369 4 1 0 ****SMITH CLERK 7902
7698 2 0 0 **BLAKE MANAGER 7839
7499 3 1 0 ***ALLEN SALESMAN 7698
7521 3 1 0 ***WARD SALESMAN 7698 --这个是树枝的末端了
7654 3 1 0 ***MARTIN SALESMAN 7698
7844 3 1 0 ***TURNER SALESMAN 7698
7900 3 1 0 ***JAMES CLERK 7698
7782 2 0 0 **CLARK MANAGER 7839
7934 3 1 0 ***MILLER CLERK 7782
转载：http://blog.sina.com.cn/s/blog_4ea0bbed0100s7w9.html