connect by 用法

    Oracle “CONNECT BY”是层次查询子句，一般用于树状或者层次结果集的查询。其语法是：

1 2	[ START WITH  condition ] CONNECT  BY  [ NOCYCLE ] condition

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).

 

说明： 
1. START WITH：告诉系统以哪个节点作为根结点开始查找并构造结果集，该节点即为返回记录中的最高节点。 
2. 当分层查询中存在上下层互为父子节点的情况时，会返回ORA-01436错误。此时，需要在connect by后面加上NOCYCLE关键字。同时，可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。 connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用

 

    接下来，用一些示例来说明“CONNECT BY”的用法。

 

[例1]

创建一个部门表，这个表有三个字段，分别对应部门ID，部门名称，以及上级部门ID

1 2 3 4 5 6 7 8

-- Create table create  table  DEP (    DEPID      number(10) not  null ,    DEPNAME    varchar2(256),    UPPERDEPID number(10) ) ;

初始化一些数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL> INSERT  INTO  DEP(DEPID, DEPNAME, UPPERDEPID) VALUES  (0, '总经办' , null ); 1 row inserted   SQL> INSERT  INTO  DEP(DEPID, DEPNAME, UPPERDEPID) VALUES  (1, '开发部' , 0); 1 row inserted   SQL> INSERT  INTO  DEP(DEPID, DEPNAME, UPPERDEPID) VALUES  (2, '测试部' , 0); 1 row inserted   SQL> INSERT  INTO  DEP(DEPID, DEPNAME, UPPERDEPID) VALUES  (3, 'Sever开发部' , 1); 1 row inserted   SQL> INSERT  INTO  DEP(DEPID, DEPNAME, UPPERDEPID) VALUES  (4, 'Client开发部' , 1); 1 row inserted   SQL> INSERT  INTO  DEP(DEPID, DEPNAME, UPPERDEPID) VALUES  (5, 'TA测试部' , 2); 1 row inserted   SQL> INSERT  INTO  DEP(DEPID, DEPNAME, UPPERDEPID) VALUES  (6, '项目测试部' , 2); 1 row inserted   SQL> commit ; Commit  complete

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> SELECT  * FROM  DEP;          DEPID DEPNAME                                                                           UPPERDEPID ----------- -------------------------------------------------------------------------------- -----------            0 General Deparment                                                                           1 Development                                                                                0            2 QA                                                                                         0            3 Server Development                                                                         1            4 Client Development                                                                         1            5 TA                                                                                         2            6 Porject QA                                                                                 2   7 rows  selected

现在我要根据“CONNECT BY”来实现树状查询结果

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SQL> SELECT  RPAD( ' ' , 2*( LEVEL -1), '-'  ) || DEPNAME "DEPNAME" , CONNECT_BY_ROOT DEPNAME "ROOT" , CONNECT_BY_ISLEAF "ISLEAF" , LEVEL  , SYS_CONNECT_BY_PATH(DEPNAME, '/' ) "PATH" FROM  DEP START WITH  UPPERDEPID IS  NULL CONNECT  BY  PRIOR  DEPID = UPPERDEPID;   DEPNAME                        ROOT                    ISLEAF      LEVEL  PATH ------------------------------ ------------------- ---------- ---------- -------------------------------------------------------------------------------- General Deparment              General Deparment            0          1 /General Deparment   -Development                  General Deparment            0          2 /General Deparment/Development   ---Server Development         General Deparment            1          3 /General Deparment/Development/Server Development   ---Client Development         General Deparment            1          3 /General Deparment/Development/Client Development   -QA                           General Deparment            0          2 /General Deparment/QA   ---TA                         General Deparment            1          3 /General Deparment/QA/TA   ---Porject QA                 General Deparment            1          3 /General Deparment/QA/Porject QA                                                      7 rows  selected

说明： 
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点 
2. CONNECT_BY_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点 
3. LEVEL 伪列表示节点深度 
4. SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔

[例2]

通过CONNECT BY生成序列

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> SELECT  ROWNUM FROM  DUAL CONNECT  BY  ROWNUM <= 10;        ROWNUM ----------           1           2           3           4           5           6           7           8           9          10   10 rows  selected

[例3]

通过CONNECT BY用于十六进度转换为十进制

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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进制值