SYS_CONNECT_BY_PATH()与start with ... connect by priod 的用法~~

一、 start  with ... connect by priod 的使用方法介绍

SQL>  SELECT level, empno,ename,job,mgr   FROM scott.emp  START WITH ename = 'KING'  CONNECT BY PRIOR empno = mgr;      LEVEL EMPNO ENAME      JOB         MGR---------- ----- ---------- --------- -----         1  7839 KING       PRESIDENT          2  7566 JONES      MANAGER    7839         3  7902 FORD       ANALYST    7566         4  7369 SMITH      CLERK      7902         2  7698 BLAKE      MANAGER    7839         3  7499 ALLEN      SALESMAN   7698         3  7521 WARD       SALESMAN   7698         3  7654 MARTIN     SALESMAN   7698         3  7844 TURNER     SALESMAN   7698         3  7900 JAMES      CLERK      7698         2  7782 CLARK      MANAGER    7839         3  7934 MILLER     CLERK      7782         4  1111 YODA       JEDI       7934 13 rows selected 图一↑

 

SQL> select * from emp order by empno,mgr; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 1111 YODA       JEDI       7934 1981/11/17    5000.00            7369 SMITH      CLERK      7902 1980/12/17     800.00               20 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7839 KING       PRESIDENT       1981/11/17    5000.00               10 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30 7900 JAMES      CLERK      7698 1981/12/3      950.00               30 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10 13 rows selected

图二↑

从上图可以看到，start with ... connect  by priod 的使用方法，就是显示个树结构，及树下各枝。这个level 是自动排出来的。

例 从图二中可以找到 empno =7698 , 那么在emp 表中mgr=7698的记录分别有 empno=7499、7521、7654、7844、7900 这5条记录，在图一中的SQL条件 为 ：

CONNECT BY PRIOR empno = 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． 定义查找起始节点      在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。  

SQL> select deptno,sys_connect_by_path(ename,'>') dd from emp start with ename='KING' connect by  nocycle prior empno=mgr ; DEPTNO DD------ --------------------------------------------------------------------------------    10 >KING    20 >KING>JONES    20 >KING>JONES>FORD    20 >KING>JONES>FORD>SMITH    30 >KING>BLAKE    30 >KING>BLAKE>ALLEN    30 >KING>BLAKE>WARD    30 >KING>BLAKE>MARTIN    30 >KING>BLAKE>TURNER    30 >KING>BLAKE>JAMES    10 >KING>CLARK    10 >KING>MILLER       >KING>MILLER>YODA 13 rows selected SQL> select deptno,sys_connect_by_path(ename,'>') dd from emp start with ename='SMITH' connect by  nocycle  empno= prior mgr ; DEPTNO DD------ --------------------------------------------------------------------------------    20 >SMITH    20 >SMITH>FORD    20 >SMITH>FORD>JONES    10 >SMITH>FORD>JONES>KING

 4．使用LEVEL 在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。 5．节点和分支的裁剪 在对树结构进行查询时，可以去掉表中的某些行，也可以剪掉树中的一个分支，使用WHERE子句来限定树型结构中的单个节点，以去掉树中的单个节点，但它却不影响其后代节点（自顶向下检索时）或前辈节点（自底向顶检索时）。 6．排序显示 象在其它查询中一样，在树结构查询中也可以使用ORDER BY 子句，改变查询结果的显示顺序，而不必按照遍历树结构的顺序

 

 

二、SYS_CONNECT_BY_PATH() 的使用SYS_CONNECT_BY_PATH 和几个伪列

CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLESYS_CONNECT_BY_PATH 函数自从Since Oracle 9i 开始，就可以通过

SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下图三所示：它一定要和connect by子句合用！

第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！START WITH 代表你要开始遍历的的节点！CONNECT BY PRIOR 是标示父子关系的对

应！

 

SQL>  SELECT level,SYS_CONNECT_BY_PATH(empno, '>') "Path"   FROM scott.emp  START WITH ename = 'KING'  CONNECT BY PRIOR empno = mgr;      LEVEL Path---------- --------------------------------------------------------------------------------         1 >7839         2 >7839>7566         3 >7839>7566>7902         4 >7839>7566>7902>7369         2 >7839>7698         3 >7839>7698>7499         3 >7839>7698>7521         3 >7839>7698>7654         3 >7839>7698>7844         3 >7839>7698>7900         2 >7839>7782         3 >7839>7782>7934         4 >7839>7782>7934>1111 13 rows selected

图三↑

从图三中可以看到更成形的树结构。

在 Oracle 10g 中，还有其他更多关于层次查询的新特性 。

例如，有的时候用户更关心的是每个层次分支中等级最低的内容。

那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。

如 果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。下给出了一个关于这个函数使用的例子：

 

SQL>  SELECT level,connect_by_isleaf,SYS_CONNECT_BY_PATH(empno, '>') "Path"   FROM scott.emp  START WITH ename = 'KING'  CONNECT BY PRIOR empno = mgr;      LEVEL CONNECT_BY_ISLEAF Path---------- ----------------- --------------------------------------------------------------------------------         1                 0 >7839         2                 0 >7839>7566         3                 0 >7839>7566>7902         4                 1 >7839>7566>7902>7369         2                 0 >7839>7698         3                 1 >7839>7698>7499         3                 1 >7839>7698>7521         3                 1 >7839>7698>7654         3                 1 >7839>7698>7844         3                 1 >7839>7698>7900         2                 0 >7839>7782         3                 0 >7839>7782>7934         4                 1 >7839>7782>7934>1111 13 rows selected

 在Oracle 10g 中还有一个新操作——CONNECT_BY_ROOT。

它用在列名之前用于返回当前层的根节点。

如下面的例子，我可以显示出层次结构表中当前行数据所对应的最高等级节点的内容。（两个1级节点）

SQL>  SELECT level,connect_by_root empno,connect_by_isleaf,SYS_CONNECT_BY_PATH(empno, '>') "Path"   FROM scott.emp  START WITH mgr is null  CONNECT BY PRIOR empno = mgr;      LEVEL CONNECT_BY_ROOTEMPNO CONNECT_BY_ISLEAF Path---------- -------------------- ----------------- --------------------------------------------------------------------------------         1                 7839                 0 >7839         2                 7839                 0 >7839>7566         3                 7839                 0 >7839>7566>7902         4                 7839                 1 >7839>7566>7902>7369         2                 7839                 0 >7839>7698         3                 7839                 1 >7839>7698>7499         3                 7839                 1 >7839>7698>7521         3                 7839                 1 >7839>7698>7654         3                 7839                 1 >7839>7698>7844         3                 7839                 1 >7839>7698>7900         2                 7839                 1 >7839>7782         1                 7934                 0 >7934         2                 7934                 1 >7934>1111 13 rows selected SQL> select * from emp; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 1111 YODA       JEDI       7934 1981/11/17    5000.00            7369 SMITH      CLERK      7902 1980/12/17     800.00               20 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7839 KING       PRESIDENT       1981/11/17    5000.00               10 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30 7900 JAMES      CLERK      7698 1981/12/3      950.00               30 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20 7934 MILLER     CLERK           1982/1/23     1300.00               10 13 rows selected

在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），

Oracle 就会报出一个错误提示：“ ORA-01436: CONNECT BY loop in user data”。

如果不删掉对父亲的引用就无法执行查询操作。

而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。

与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE， 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，

那么该行的伪列中就会显示“1”，否则就显示“0”。如下例所示：

SQL>  SELECT level,connect_by_iscycle,connect_by_isleaf,SYS_CONNECT_BY_PATH(empno, '>') "Path"   FROM scott.emp  START WITH ename = 'KING'  CONNECT BY nocycle  PRIOR empno = mgr;      LEVEL CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF Path---------- ------------------ ----------------- --------------------------------------------------------------------------------         1                  0                 0 >7839         2                  0                 0 >7839>7566         3                  0                 0 >7839>7566>7902         4                  0                 1 >7839>7566>7902>7369         2                  0                 0 >7839>7698         3                  0                 1 >7839>7698>7499         3                  0                 1 >7839>7698>7521         3                  0                 1 >7839>7698>7654         3                  0                 1 >7839>7698>7844         3                  0                 1 >7839>7698>7900         2                  0                 1 >7839>7782         2                  1                 0 >7839>7934         3                  0                 1 >7839>7934>1111 13 rows selected SQL> select * from emp; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 1111 YODA       JEDI       7934 1981/11/17    5000.00            7369 SMITH      CLERK      7902 1980/12/17     800.00               20 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7839 KING       PRESIDENT  7934 1981/11/17    5000.00               10 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30 7900 JAMES      CLERK      7698 1981/12/3      950.00               30 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20 7934 MILLER     CLERK      7839 1982/1/23     1300.00               10 13 rows selected

 SYS_CONNECT_BY_PATH的其它用法：（转载）

racle中SYS_CONNECT_BY_PATH函数是非常重要的函数，下面就为您介绍一个使用SYS_CONNECT_BY_PATH函数的例子，实例如下：create table test (a varchar2(10),b varchar2(10));   INSERT INTO TEST (A, B) VALUES ('1', '我');  INSERT INTO TEST (A, B) VALUES ('1', '们');  INSERT INTO TEST (A, B) VALUES ('2', '一');  INSERT INTO TEST (A, B) VALUES ('2', '起');  COMMIT;   SELECT A, B FROM TEST   A          B  ---------- ----------  1          我  1          们  2          一  2          起   现在需要达到如下的效果，  A          B  ---------- ----------  1          我,们  2          一,起   只想用一句sql来返回结果。SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, ',')), ',') B  FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN            FROM TEST)  START WITH RN = 1 CONNECT BY RN - 1 = PRIOR RN         AND A = PRIOR A  GROUP BY A; 其中，SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。row_number函数的用途是非常广泛，这个函数的功能是为查询出来的每一行记录生成一个序号。生产序号的方法通过over()函数里面的语句来控制。