Oracle的SYS_CONNECT_BY_PATH函式

在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  ;

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;

序號	值
1	A,B,C,D,E,F,G’
1	A
1	B
1	C
1	D
1	E
1	F