Oracle的SYS_CONNECT_BY_PATH函数

蔚落 2021-06-26 16:06 367阅读 0赞

在Oracle中，`SYS_CONNECT_BY_PATH`函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。  
`sys_connect_by_path(字段名, 2个字段之间的连接符号)`，注意这里的连接符号不要使用逗号，oracle会报错，如果一定要用`，`可以使用`replace`替换一下，方法如下`REPLACE(字段名,原字符,',')`。

还有，这个函数使用之前必须先建立一个树，否则无用。

使用`Oracle Database 10g`中的闪回表特性，可以毫不费力地恢复被意外删除的表。以下是一个不该发生却经常发生的情况：用户删除了一个非常重要的表— 当然是意外地删除 — 并需要尽快地恢复。

`Oracle9i Database` 推出了闪回查询选项的概念，以便检索过去某个时间点的数据，但它不能闪回 `DDL` 操作，如删除表的操作。唯一的恢复方法是在另一个数据库中使用表空间的时间点恢复，然后使用导出/导入或其他方法，在当前数据库中重新创建表。这一过程需要 `DBA` 进行大量工作并且耗费宝贵的时间，更不用说还要使用另一个数据库进行克隆。

--------------------

\#\#`ORACLE-SYS_CONNECT_BY_PATH`函数汇总  
**`SYS_CONNECT_BY_PATH(column_name,'分隔符')`函数**

第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符

伪列`CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE`

示例1：查询表中树结构并转化为文本展示（转载）

SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(ou_code, ';')), ';') AS RESULT
              FROM (SELECT category_id, ou_code, RN, LEAD(RN) OVER(ORDER BY RN) RN1
                      FROM (SELECT category_id,
                                   ou_code,
                                   ROW_NUMBER() OVER(ORDER BY category_id, ou_code DESC) RN
                              FROM (SELECT t.category_id, t.ou_code
                                      FROM bpm_dcs_category_ou t
                                     WHERE t.category_id=1000085)))
             START WITH RN1 IS NULL
                    AND category_id = 1000085
            CONNECT BY RN1 = PRIOR RN;

示例2：列转行（转载）

select max(substr(sys_connect_by_path(column_name,','),2))  
        from (select column_name,rownum rn from user_tab_columns where table_name ='DEPT')  
        start with rn=1 connect by rn=rownum ;  
         
        MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,','),2))  
        -------------------------------------------------------------------------------- 
        DEPTNO,DEPTNAME,MGRNO

`MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,';'),2))` 先连接所有节点，从第二个字符开始截取，取最完整条目

其作用于`LTRIM(MAX(SYS_CONNECT_BY_PATH(COLUMN_NAME,';')),';')`（先连接所有节点，取最完整条目，删除左侧字符‘`;`’）类似

补充：结构化查询

`START WITH ...CONNNECT BY PRIOR`基本语法是：

SELECT ...FROM
    WHERE （过滤返回记录，仅过滤被限定节点，其根节点和子节点均不受影响）
    START WITH (根节点，可以指定多个节点)
    CONNECT BY PRIOR= （连接条件，PRIOR置于等号前，则从根节点到叶节点开始检索；置于等号后，则从叶节点到根节点开始检索）

该查询访问路径如下：

从根节点开始，向下扫描子节点，该子节点已被访问则转向其最左侧未被访问的子节点，否则判断该节点是否为根节点，是则访问完毕，否则返回父节点重新执行判断。

即扫描整表判断所有节点树结构（遍历树结构）。

--------------------

## `SYS_CONNECT_BY_PATH`函数用法 ##

SELECT ename   
    FROM scott.emp    
    START WITH ename = 'KING'    
    CONNECT BY PRIOR empno = mgr;
    
    --得到结果为：
    
    KING   
    JONES   
    SCOTT   
    ADAMS   
    FORD   
    SMITH   
    BLAKE   
    ALLEN   
    WARD   
    MARTIN   
    TURNER   
    JAMES

而：

SELECT SYS_CONNECT_BY_PATH(ename, '>') "Path"    
    FROM scott.emp    
    START WITH ename = 'KING'    
    CONNECT BY PRIOR empno = mgr;  
    
    --得到结果为： 
    KING
    KING>JONES
    KING>JONES>SCOTT
    KING>JONES>SCOTT>ADAMS
    KING>JONES>FORD
    KING>JONES>FORD>SMITH
    KING>BLAKE
    KING>BLAKE>ALLEN
    KING>BLAKE>WARD
    KING>BLAKE>MARTIN
    KING>BLAKE>TURNER
    KING>BLAKE>JAMES
    KING>CLARK
    KING>CLARK>MILLER

其实`SYS_CONNECT_BY_PATH`这个函数是`oracle9i`才新提出来的！  
它一定要和`connect by`子句合用！  
第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！

`START WITH` 代表你要开始遍历的的节点！

`CONNECT BY PRIOR` 是标示父子关系的对应！

如下例子：

select max(   
    substr(   
    sys_connect_by_path(column_name,',')   
    ,2)   
    )   
    from (select column_name,rownum rn from user_tab_columns where table_name ='AA_TEST')   
    start with rn=1 connect by rn=rownum ;

是将列用,进行分割成为一行，然后将首个，去掉，只取取最大的那个数据。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

下面是别人的例子：

1、带层次关系

SQL> create table dept(deptno number,deptname varchar2(20),mgrno number);   
      
    Table created.   
      
    SQL> insert into dept values(1,'总公司',null);   
      
    1 row created.   
      
    SQL> insert into dept values(2,'浙江分公司',1);   
      
    1 row created.   
      
    SQL> insert into dept values(3,'杭州分公司',2);   
      
    1 row created.   
      
    SQL> commit;   
      
    Commit complete.   
      
    SQL> select max(substr(sys_connect_by_path(deptname,','),2)) from dept connect by prior deptno=mgrno;   
      
    MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))   
    -------------------------------------------------------------------------------- 
    总公司,浙江分公司,杭州分公司  
    SQL> create table dept(deptno number,deptname varchar2(20),mgrno number);
    
    Table created.
    
    SQL> insert into dept values(1,'总公司',null);
    
    1 row created.
    
    SQL> insert into dept values(2,'浙江分公司',1);
    
    1 row created.
    
    SQL> insert into dept values(3,'杭州分公司',2);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select max(substr(sys_connect_by_path(deptname,','),2)) from dept connect by prior deptno=mgrno;
    
    MAX(SUBSTR(SYS_CONNECT_BY_PATH(DEPTNAME,','),2))
    --------------------------------------------------------------------------------
    总公司,浙江分公司,杭州分公司

2、行列转换

如把一个表的所有列连成一行，用逗号分隔:

SQL> select max(substr(sys_connect_by_path(column_name,','),2))   
    from (select column_name,rownum rn from user_tab_columns where table_name ='DEPT')   
    start with rn=1 connect by rn=rownum ;   
      
    MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,','),2))   
    -------------------------------------------------------------------------------- 
    DEPTNO,DEPTNAME,MGRNO

--------------------

## Oracle中`SYS_CONNECT_BY_PATH`函数的妙用 ##

Oracle 中`SYS_CONNECT_BY_PATH`函数是非常重要的函数，下面就为您介绍一个使用`SYS_CONNECT_BY_PATH`函数的例子，实例如下：

数据准备：

create table test (a varchar2(10),b varchar2(10));  
     
    INSERT INTO TEST (A, B) VALUES ('1', 'A');  
    INSERT INTO TEST (A, B) VALUES ('1', 'B');  
    INSERT INTO TEST (A, B) VALUES ('2', 'X');  
    INSERT INTO TEST (A, B) VALUES ('2', 'Y');  
    
    SELECT A, B FROM TEST  ;

![这里写图片描述][20180914110924658]

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;

--------------------

将 'A,B,C,D,E,F,G’拆分成行显示 ；

/* 1.分析先找出有多少个‘,’逗号:length(str) - length(regexp_replace(str, ',', '')) + 1 ; 2.再根据regexp_substr()函数进行查找逗号的位置 ; regexp_substr(str, '[^,]+', 1, level, 'i') as str ，这里的level代表第几个逗号(‘,’) 3.function REGEXP_SUBSTR(__srcstr, __pattern, __position, __occurrence, __modifier) ; 参数的含义： __srcstr ：需要进行正则处理的字符串 __pattern ：进行匹配的正则表达式 __position ：起始位置，从第几个字符开始正则表达式匹配（默认为1） __occurrence ：标识第几个匹配组，默认为1 __modifier ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。） */
    
    with temp as
     (select 'A,B,C,D,E,F,G' str from dual)
    select regexp_substr(str, '[^,]+', 1, level, 'i') as str
      from temp
    connect by level <= length(str) - length(regexp_replace(str, ',', '')) + 1;

[20180914110924658]: /images/20210626/703c082494f74775b29aeb1604a59ca1.png