Oracle “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

create table DEP

(

   DEPID      number(10) not null ,

   DEPNAME    varchar2(256),

   UPPERDEPID number(10)

);

初始化一些数据

SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, '总经办' , null );

SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, '开发部' , 0);

SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, '测试部' , 0);

SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'Sever开发部' , 1);

SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'Client开发部' , 1);

SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'TA测试部' , 2);

SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, '项目测试部' , 2);

SQL> commit ;

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

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将字符串转成多列

SELECT REGEXP_SUBSTR(S, '[^,]+', 1, ROWNUM) N
  FROM (SELECT 'tt,aa,bb' AS S FROM DUAL)
CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE(S, '[^,]', NULL)) + 1

[例4]

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

1．树结构的描述

树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如EMP表中的EMPNO和MGR。EMPNO表示该雇员的编号，MGR表示领导该雇员的人的编号，即子节点的MGR值等于父节点的EMPNO值。在表的每一行中都有一个表示父节点的MGR（除根节点外），通过每个节点的父节点，就可以确定整个树结构。

在SELECT命令中使用CONNECT BY和蔼START WITH 子句可以查询表中的树型结构关系。其命令格式如下：

SELECT 。。。

CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR裂名2}

[START WITH]；

其中：CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。

START WITH：不但可以指定一个根节点，还可以指定多个根节点。

2．关于PRIOR

运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序。

PRIOR被置于CONNECT BY子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。如：

CONNECT BY PRIOR EMPNO=MGR

PIROR运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。例如：

CONNECT BY EMPNO=PRIOR MGR

在这种方式中也应指定一个开始的节点。

3．定义查找起始节点

在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。

4．使用LEVEL

在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2，依此类推。图1.2就表示了树结构的层次。

5．节点和分支的裁剪

在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。

6．排序显示

象在其它查询中一样，在树结构查询中也可以使用ORDER BY子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序。


Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。

CREATETABLE TBL_TEST

(

  ID   NUMBER,

  NAME VARCHAR2(100BYTE),

  PID   NUMBERDEFAULT0

);

 

--插入测试数据：

INSERTINTO TBL_TEST(ID,NAME,PID)VALUES('1','10','0');

INSERTINTO TBL_TEST(ID,NAME,PID)VALUES('2','11','1');

INSERTINTO TBL_TEST(ID,NAME,PID)VALUES('3','20','0');

INSERTINTO TBL_TEST(ID,NAME,PID)VALUES('4','12','1');

INSERTINTO TBL_TEST(ID,NAME,PID)VALUES('5','121','2');

 

select * from TBL_TEST;

 

--从Root往树末梢递归

SELECT t.*,level, connect_by_root NAME, SYS_CONNECT_BY_PATH(NAME,'/') from TBL_TEST t

start with id=1

connect by priorid= pid;

 

--从末梢往树ROOT递归

select t.*,level, connect_by_root NAME, SYS_CONNECT_BY_PATH(NAME,'/') from TBL_TEST t

start with id=5

connect by prior pid=ID;

例子

WITH ROW_NUMBER_TEST AS
 (SELECT 1 A, 'one' B
    FROM DUAL
  UNION ALL
  SELECT 1, 'one'
    FROM DUAL
  UNION ALL
  SELECT 3, 'three'
    FROM DUAL
  UNION ALL
  SELECT 4, 'four'
    FROM DUAL
  UNION ALL
  SELECT 5, 'five' FROM DUAL)
SELECT A,B,SYS_CONNECT_BY_PATH(B, '/') "PATH"
  FROM (SELECT A, B, ROW_NUMBER() OVER(PARTITION BY B ORDER BY B) RN
          FROM ROW_NUMBER_TEST) A
 START WITH A.RN = 1
CONNECT BY PRIOR A.B = A.B
       AND PRIOR A.RN = A.RN - 1