Oracle中可以通过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”。
The start with .. connect by clause can be used to select data that has a hierarchical relationship
(usually some sort of parent->child, boss->employee or thing->parts).
It is also being used when an sql execution plan is explained.
syntax:
select ... [start with initial-condition] connect by [nocycle] recurse-condition
level
With level it is possible to show the level in the hierarchical relation of all the data.
--oracle 9i
sys_connect_by_path
With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child.
--oracle 10g
connect_by_root
connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_is_leaf
connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_iscycle
connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
下面举例说明：
[例1]创建一个部门表，这个表有4个字段，分别对应部门ID，部门名称，上级部门ID ，本部门直属员工人数。
create table DEP
(
DEPID number(10) not null,
DEPNAME varchar2(32),
UPPERDEPID number(10),
NUMOFEMP number(10)
) ;
下面插入一些数据，结果如下：
[sql] view plaincopy
1 hr@MYTEST2> select * from dep;
2
3 DEPID DEPNAME UPPERDEPID NUMOFEMP
4 ---------- --------------- ---------- ----------
5 0 Dev Center 2
6 1 DevA 0 3
7 2 DevA Team1 1 10
8 3 DevA Team2 1 8
9 4 DevB 0 2
10 5 DevB Team1 4 12
11 6 Test Center 1
12 7 Test Team1 6 5
13 8 Test Team2 6 5
14
15 9 rows selected.
现在根据“CONNECT BY”来实现树状查询：
[sql] view plaincopy
16 column depname format a15;
17 column rootdep format a15;
18 column path format a30;
19
20 select rpad(' ', 2*(level-1), '-') || depname "DEPNAME",
21 connect_by_root depname "ROOTDEP",
22 connect_by_isleaf "ISLEAF",
23 level,
24 sys_connect_by_path(depname, '/') "PATH"
25 from dep
26 start with upperdepid is null
27 connect by prior depid = upperdepid
28 /
结果如下：
[sql] view plaincopy
29 DEPNAME ROOTDEP ISLEAF LEVEL PATH
30 --------------- --------------- ---------- ---------- ---------------------------
31 Dev Center Dev Center 0 1 /Dev Center
32 -DevA Dev Center 0 2 /Dev Center/DevA
33 ---DevA Team1 Dev Center 1 3 /Dev Center/DevA/DevA Team1
34 ---DevA Team2 Dev Center 1 3 /Dev Center/DevA/DevA Team2
35 -DevB Dev Center 0 2 /Dev Center/DevB
36 ---DevB Team1 Dev Center 1 3 /Dev Center/DevB/DevB Team1
37 Test Center Test Center 0 1 /Test Center
38 -Test Team1 Test Center 1 2 /Test Center/Test Team1
39 -Test Team2 Test Center 1 2 /Test Center/Test Team2
下面计算Dev Center和Test Center部门的总人数：
[sql] view plaincopy
40 select ROOTDEPID, sum(numofemp) "TOTALEMP"
41 from (select connect_by_root depid "ROOTDEPID", numofemp from dep
42 start with upperdepid is null
43 connect by prior depid = upperdepid)
44 group by ROOTDEPID
45 /
结果如下：
[plain] view plaincopy
46 ROOTDEPID TOTALEMP
47 --------- ----------
48 6 11
49 0 37
[例2]通过CONNECT BY用于十六进度转换为十进制
[sql] view plaincopy
50 CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
51 ----------------------------------------------------------------------------------------------------------------------
52 -- 对象名称: f_hex_to_dec
53 -- 对象描述: 十六进制转换十进制
54 -- 输入参数: p_str 十六进制字符串
55 -- 返回结果: 十进制字符串
56 -- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;
57 ----------------------------------------------------------------------------------------------------------------------
58 v_return VARCHAR2(4000);
59 BEGIN
60 SELECT SUM(DATA) INTO v_return
61 FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
62 WHEN 'A' THEN '10'
63 WHEN 'B' THEN '11'
64 WHEN 'C' THEN '12'
65 WHEN 'D' THEN '13'
66 WHEN 'E' THEN '14'
67 WHEN 'F' THEN '15'
68 ELSE substr(p_str, rownum, 1)
69 END) * power(16, length(p_str) - rownum) DATA
70 FROM dual
71 CONNECT BY rownum <= length(p_str));
72 RETURN v_return;
73 EXCEPTION
74 WHEN OTHERS THEN
75 RETURN NULL;
76 END;
说明：
1. CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历
2. 通过CASE语句，来解析十六进制中的A-F对应的10进制值
测试结果如下：
[plain] view plaincopy
77 hr@MYTEST2> variable dec varchar2(32);
78 hr@MYTEST2> exec :dec := f_hex_to_dec('1FF');
79
80 PL/SQL procedure successfully completed.
81
82 hr@MYTEST2> print dec
83
84 DEC
85 ----------
86 511
[例3]通过CONNECT BY生成序列
对于connect by，现在大多数人已经很熟悉了，connect by中的条件就表示了父子之间的连接关系，比如 connect by id=prior pid。
但如果connect by中的条件没有表示记录之间的父子关系
那会出现什么情况？
常见的，connect by会在构造序列的时候使用
用select rownum from dual connect by rownum
我们注意到，dual是一个只有一条记录的表，如果表有多条记录，将会怎样？
下面开始实验
CREATE TABLE T(ID VARCHAR2(1 BYTE));
INSERT INTO T ( ID ) VALUES ( 'A');
INSERT INTO T ( ID ) VALUES ( 'B');
INSERT INTO T ( ID ) VALUES ( 'C');
COMMIT;
然后执行以下查询：
[plain] view plaincopy
87 hr@MYTEST2> column id format a2;
88 hr@MYTEST2> select id,level from t connect by level<2;
89
90 ID LEVEL
91 -- ----------
92 A 1
93 B 1
94 C 1
95
96 hr@MYTEST2> select id,level from t connect by level<3;
97
98
99 ID LEVEL
100 -- ----------
101 A 1
102 A 2
103 B 2
104 C 2
105 B 1
106 A 2
107 B 2
108 C 2
109 C 1
110 A 2
111 B 2
112 C 2
113
114 12 rows selected.
无需多说，我们很快可以找到其中的规律，假设表中有N条记录
则记F(N,l)为 select id,level from t connect by level
那么，
F(N,1)=N
F(N,l) = F(N,l-1)*N+N
于是可以总结出
F(N,l)=∑power(N,p), p取值为[1,l)
要解释，也很容易。
当连接条件不能限制记录之间的关系时
每一条记录都可以作为自己或者其他记录的叶子
如下所示：
A 1A 2A 3
B 3
C 3
B 2A 3
B 3
C 3
C 2
A 3
B 3
C 3
在这里，我们看到的是
Oracle采用了深度优先的算法
转载自：
http://www.blogjava.net/wxqxs/archive/2008/08/15/222338.html
http://www.cnblogs.com/lettoo/archive/2010/08/03/1791239.html
http://www.itpub.net/thread-994465-1-1.html
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26495863/viewspace-1276681/ ，如需转载，请注明出处，否则将追究法律责任。
