Oracle CONNECT BY的用法

Oracle CONNECT BY的用法

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 plain copy

print?

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.  

hr@MYTEST2> select * from dep; DEPID DEPNAME UPPERDEPID NUMOFEMP---------- --------------- ---------- ---------- 0 Dev Center 2 1 DevA 0 3 2 DevA Team1 1 10 3 DevA Team2 1 8 4 DevB 0 2 5 DevB Team1 4 12 6 Test Center 1 7 Test Team1 6 5 8 Test Team2 6 59 rows selected.

现在根据“CONNECT BY”来实现树状查询：

[sql] view plain copy

print?

1. column depname format a15;  
2. column rootdep format a15;  
3. column path format a30;  
4.   
5. select rpad(' ', 2*(level-1), '-') || depname "DEPNAME",  
6. connect_by_root depname "ROOTDEP",  
7. connect_by_isleaf "ISLEAF",  
8. level,  
9. sys_connect_by_path(depname, '/') "PATH"  
10. from dep  
11. start with upperdepid is null  
12. connect by prior depid = upperdepid  
13. /  

column depname format a15;column rootdep format a15;column path format a30;select rpad(' ', 2*(level-1), '-') || depname "DEPNAME",connect_by_root depname "ROOTDEP",connect_by_isleaf "ISLEAF",level,sys_connect_by_path(depname, '/') "PATH"from depstart with upperdepid is nullconnect by prior depid = upperdepid/

结果如下：

[sql] view plain copy

print?

1. DEPNAME         ROOTDEP             ISLEAF      LEVEL PATH  
2. --------------- --------------- ---------- ---------- ---------------------------  
3. Dev Center      Dev Center               0          1 /Dev Center  
4.  -DevA          Dev Center               0          2 /Dev Center/DevA  
5.  ---DevA Team1  Dev Center               1          3 /Dev Center/DevA/DevA Team1  
6.  ---DevA Team2  Dev Center               1          3 /Dev Center/DevA/DevA Team2  
7.  -DevB          Dev Center               0          2 /Dev Center/DevB  
8.  ---DevB Team1  Dev Center               1          3 /Dev Center/DevB/DevB Team1  
9. Test Center     Test Center              0          1 /Test Center  
10.  -Test Team1    Test Center              1          2 /Test Center/Test Team1  
11.  -Test Team2    Test Center              1          2 /Test Center/Test Team2  

DEPNAME ROOTDEP ISLEAF LEVEL PATH--------------- --------------- ---------- ---------- ---------------------------Dev Center Dev Center 0 1 /Dev Center -DevA Dev Center 0 2 /Dev Center/DevA ---DevA Team1 Dev Center 1 3 /Dev Center/DevA/DevA Team1 ---DevA Team2 Dev Center 1 3 /Dev Center/DevA/DevA Team2 -DevB Dev Center 0 2 /Dev Center/DevB ---DevB Team1 Dev Center 1 3 /Dev Center/DevB/DevB Team1Test Center Test Center 0 1 /Test Center -Test Team1 Test Center 1 2 /Test Center/Test Team1 -Test Team2 Test Center 1 2 /Test Center/Test Team2

下面计算Dev Center和Test Center部门的总人数：

[sql] view plain copy

print?

1. select ROOTDEPID, sum(numofemp) "TOTALEMP"  
2. from (select connect_by_root depid "ROOTDEPID", numofemp from dep  
3. start with upperdepid is null  
4. connect by prior depid = upperdepid)  
5. group by ROOTDEPID  
6. /  

select ROOTDEPID, sum(numofemp) "TOTALEMP"from (select connect_by_root depid "ROOTDEPID", numofemp from depstart with upperdepid is nullconnect by prior depid = upperdepid)group by ROOTDEPID/

结果如下：

[plain] view plain copy

print?

1. ROOTDEPID   TOTALEMP  
2. --------- ----------  
3.         6         11  
4.         0         37  

ROOTDEPID TOTALEMP---------- ---------- 6 11 0 37

[例2]
通过CONNECT BY用于十六进度转换为十进制

[sql] view plain copy

print?

1. CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS  
2.     ----------------------------------------------------------------------------------------------------------------------  
3.     -- 对象名称: f_hex_to_dec  
4.     -- 对象描述: 十六进制转换十进制  
5.     -- 输入参数: p_str 十六进制字符串  
6.     -- 返回结果: 十进制字符串  
7.     -- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;  
8.     ----------------------------------------------------------------------------------------------------------------------  
9.     v_return  VARCHAR2(4000);  
10.   BEGIN  
11.     SELECT SUM(DATA) INTO v_return  
12.       FROM (SELECT (CASE upper(substr(p_str, rownum, 1))  
13.                      WHEN 'A' THEN '10'  
14.                      WHEN 'B' THEN '11'  
15.                      WHEN 'C' THEN '12'  
16.                      WHEN 'D' THEN '13'  
17.                      WHEN 'E' THEN '14'  
18.                      WHEN 'F' THEN '15'  
19.                      ELSE substr(p_str, rownum, 1)  
20.                    END) * power(16, length(p_str) - rownum) DATA  
21.               FROM dual  
22.             CONNECT BY rownum <= length(p_str));  
23.     RETURN v_return;  
24.   EXCEPTION  
25.     WHEN OTHERS THEN  
26.       RETURN NULL;  
27.   END;  

CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS ---------------------------------------------------------------------------------------------------------------------- -- 对象名称: f_hex_to_dec -- 对象描述: 十六进制转换十进制 -- 输入参数: p_str 十六进制字符串 -- 返回结果: 十进制字符串 -- 测试用例: SELECT f_hex_to_dec('78A') FROM dual; ---------------------------------------------------------------------------------------------------------------------- v_return VARCHAR2(4000); BEGIN SELECT SUM(DATA) INTO v_return FROM (SELECT (CASE upper(substr(p_str, rownum, 1)) WHEN 'A' THEN '10' WHEN 'B' THEN '11' WHEN 'C' THEN '12' WHEN 'D' THEN '13' WHEN 'E' THEN '14' WHEN 'F' THEN '15' ELSE substr(p_str, rownum, 1) END) * power(16, length(p_str) - rownum) DATA FROM dual CONNECT BY rownum <= length(p_str)); RETURN v_return; EXCEPTION WHEN OTHERS THEN RETURN NULL; END;

说明：
1. CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历
2. 通过CASE语句，来解析十六进制中的A-F对应的10进制值

测试结果如下：

[plain] view plain copy

print?

1. hr@MYTEST2> variable dec varchar2(32);  
2. hr@MYTEST2> exec :dec := f_hex_to_dec('1FF');  
3.   
4. PL/SQL procedure successfully completed.  
5.   
6. hr@MYTEST2> print dec  
7.   
8. DEC  
9. ----------  
10. 511  

hr@MYTEST2> variable dec varchar2(32);hr@MYTEST2> exec :dec := f_hex_to_dec('1FF');PL/SQL procedure successfully completed.hr@MYTEST2> print decDEC----------511

[例3]
通过CONNECT BY生成序列
对于connect by，现在大多数人已经很熟悉了，connect by中的条件就表示了父子之间的连接关系，比如 connect by id=prior pid。

但如果connect by中的条件没有表示记录之间的父子关系
那会出现什么情况？
常见的，connect by会在构造序列的时候使用
用select rownum from dual connect by rownum<xxx 代替早期版本的 select rownum from all_objects where rownum <xxx

我们注意到，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 plain copy

print?

1. hr@MYTEST2> column id format a2;  
2. hr@MYTEST2> select id,level from t connect by level<2;  
3.   
4. ID      LEVEL  
5. -- ----------  
6. A           1  
7. B           1  
8. C           1  
9.   
10. hr@MYTEST2> select id,level from t connect by level<3;  
11.   
12.   
13. ID      LEVEL  
14. -- ----------  
15. A           1  
16. A           2  
17. B           2  
18. C           2  
19. B           1  
20. A           2  
21. B           2  
22. C           2  
23. C           1  
24. A           2  
25. B           2  
26. C           2  
27.   
28. 12 rows selected.  

hr@MYTEST2> column id format a2;hr@MYTEST2> select id,level from t connect by level<2;ID LEVEL-- ----------A 1B 1C 1hr@MYTEST2> select id,level from t connect by level<3;ID LEVEL-- ----------A 1A 2B 2C 2B 1A 2B 2C 2C 1A 2B 2C 212 rows selected.

无需多说，我们很快可以找到其中的规律，假设表中有N条记录
则记F(N,l)为 select id,level from t connect by level<l 的结果集数目
那么，
F(N,1)=N
F(N,l) = F(N,l-1)*N+N

于是可以总结出
F(N,l)=∑power(N,p), p取值为[1,l)

要解释，也很容易。
当连接条件不能限制记录之间的关系时
每一条记录都可以作为自己或者其他记录的叶子
如下所示：
A          1
A          2
A          3
B          3
C          3
B          2
A          3
B          3
C          3
C          2
A          3
B          3
C          3

在这里，我们看到的是
Oracle采用了深度优先的算法