Oracle Connect By用法

oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是：

select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>
;

<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。
<Conditional-2>：查询结果重起始根结点的限定条件。
<Conditional-3>：连接条件

数据组织结构如下图：

|---a------a1
|     |------a2
|
|---b------b1
      |------b2

数据库表结构如下：

create table t2(
root_id number,
id number,
name varchar(5),
description varchar(10)
);

insert into t2(root_id,id,name,description) values(0,1,'a','aaa');
insert into t2(root_id,id,name,description) values(1,2,'a1','aaa1');
insert into t2(root_id,id,name,description) values(1,3,'a2','aaa2');
insert into t2(root_id,id,name,description) values(0,4,'b','bbb');
insert into t2(root_id,id,name,description) values(4,5,'b1','bbb1');
insert into t2(root_id,id,name,description) values(4,6,'b2','bbb2');

SQL> select * from t2;

   ROOT_ID         ID NAME DESCRIPTIO
---------- ---------- ----- ----------
         0          1 a     aaa
         1          2 a1    aaa1
         1          3 a2    aaa2
         0          4 b     bbb
         4          5 b1    bbb1
         4          6 b2    bbb2
        
获取完整树：
select * from t2 start with root_id = 0 connect by prior id = root_id;

|---a------a1
|     |------a2
|
|---b------b1
      |------b2

   ROOT_ID         ID NAME DESCRIPTIO
---------- ---------- ----- ----------
         0          1 a     aaa
         1          2 a1    aaa1
         1          3 a2    aaa2
         0          4 b     bbb
         4          5 b1    bbb1
         4          6 b2    bbb2

获取特定子树：
select * from t2 start with id = 1 connect by prior id = root_id;

|---a------a1
|     |------a2

   ROOT_ID         ID NAME DESCRIPTIO
---------- ---------- ----- ----------
         0          1 a     aaa
         1          2 a1    aaa1
         1          3 a2    aaa2
        
select * from t2 start with id = 4 connect by prior id = root_id;

|---b------b1
      |------b2

   ROOT_ID         ID NAME DESCRIPTIO
---------- ---------- ----- ----------
         0          4 b     bbb
         4          5 b1    bbb1
         4          6 b2    bbb2
        
如果connect by prior中的prior被省略，则查询将不进行深层递归。
如：

select * from t2 start with root_id = 0 connect by id = root_id;

|---a
|   
|
|---b

   ROOT_ID         ID NAME DESCRIPTIO
---------- ---------- ----- ----------
         0          1 a     aaa
         0          4 b     bbb

如：        
select * from t2 start with id = 1 connect by id = root_id;

|---a

   ROOT_ID         ID NAME DESCRIPTIO
---------- ---------- ----- ----------
         0          1 a     aaa

 

 

level,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