START WITH 定義數(shù)據(jù)行查詢的初始起點；
成都創(chuàng)新互聯(lián)專注骨干網(wǎng)絡(luò)服務(wù)器租用十余年，服務(wù)更有保障！服務(wù)器租用，西部信息服務(wù)器托管 成都服務(wù)器租用，成都服務(wù)器托管，骨干網(wǎng)絡(luò)帶寬，享受低延遲，高速訪問。靈活、實現(xiàn)低成本的共享或公網(wǎng)數(shù)據(jù)中心高速帶寬的專屬高性能服務(wù)器。
CONNECT BY prior 定義表中的各個行是如何聯(lián)系的；
connect by 后面的"prior" 如果缺省，則只能查詢到符合條件的起始行，并不進(jìn)行遞歸查詢；
條件2：col_1 = col_2，col_1是父鍵（它標(biāo)識父），col_2是子鍵（它標(biāo)識子）。
條件3過濾遞歸前相應(yīng)節(jié)點及其子節(jié)點，如果上級節(jié)點不滿足則下級節(jié)點自動過濾掉；
條件4過濾遞歸后相應(yīng)的節(jié)點或子節(jié)點，如果上級節(jié)點不滿足則下級結(jié)點自動提升一級。
系統(tǒng)偽列：
CURRVAL AND NEXTVAL 使用序列號的保留字
ROWID 記錄的唯一標(biāo)識
ROWNUM 限制查詢結(jié)果集的數(shù)量
LEVEL 顯示層次樹中特定行的層次或級別
CONNECT_BY_ROOT 返回當(dāng)前層的根節(jié)點(當(dāng)前行數(shù)據(jù)所對應(yīng)的最高等級節(jié)點的內(nèi)容)
SYS_CONNECT_BY_PATH(column, char) 函數(shù)實現(xiàn)將從父節(jié)點到當(dāng)前行內(nèi)容以"path"或者層次元素列表的形式顯示出來
CONNECT_BY_ISCYCLE 須帶參數(shù)NOCYCLE，當(dāng)前行中引用了某個父親節(jié)點的內(nèi)容并在樹中出現(xiàn)了循環(huán)，如果循環(huán)顯示"1"，否則就顯示"0"。
CONNECT_BY_ISLEAF 判斷當(dāng)前行是不是葉子。如果是葉子顯示"1"，如果不是葉子而是一個分支（例如當(dāng)前內(nèi)容是其他行的父親）就顯示"0"
而在 Oracle 10g 中，只要指定"NOCYCLE"就可以進(jìn)行任意的查詢操作。與這個關(guān)鍵字相關(guān)的還有一個偽列——CONNECT_BY_ISCYCLE， 如果在當(dāng)前行中引用了某個父親節(jié)點的內(nèi)容并在樹中出現(xiàn)了循環(huán)，那么該行的偽列中就會顯示"1"，否則就顯示"0"。
【實例】
--創(chuàng)建測試表，增加測試數(shù)據(jù)
create table test(superid varchar2(20),id varchar2(20),mc varchar2(20));
insert into test values('0','1','A1');
insert into test values('0','2','A2');
insert into test values('1','11','A11');
insert into test values('1','12','A12');
insert into test values('2','21','A21');
insert into test values('2','22','A22');
insert into test values('11','111','A111');
insert into test values('11','112','A112');
insert into test values('12','121','A121');
insert into test values('12','122','A122');
insert into test values('21','211','A211');
insert into test values('21','212','A212');
insert into test values('22','221','A221');
insert into test values('22','222','A222');
commit;
--層次查詢示例
select level||'級' jc,lpad(' ',(level-1)*4)||id id,mc
from test
start with superid = '0' connect by prior id=superid;
select level||'級' jc,connect_by_isleaf mxf,lpad(' ',(level-1)*4)||id id,mc
from test
start with superid = '0' connect by prior id=superid;
--給出兩個以前在"數(shù)據(jù)庫字符串分組相加之四"中的例子來理解start with ... connect by ...
--功能：實現(xiàn)按照superid分組,把id用";"連接起來
--實現(xiàn)：以下兩個例子都是通過構(gòu)造2個偽列來實現(xiàn)connect by連接的。
about connect by
SELECT empno, ename, job, mgr, deptno, LEVEL, sys_connect_by_path(ename,'\'), connect_by_root(ename) FROM emp START WITH mgr IS NULL CONNECT BY mgr =? PRIOR empno
WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必須把結(jié)構(gòu)寫出來
SELECT empno, ename, job, mgr, deptno? ---- 先寫錨點查詢，用START WITH的條件
,1 AS the_level? ? ---- 遞歸起點，第一層
,'\'||ename? ? ? ? ---- 路徑的第一截
,ename AS top_manager ---- 原來的CONNECT_BY_ROOT
FROM scott.EMP
WHERE mgr IS NULL ---- 原來的START WITH條件
UNION ALL? ---- 下面是遞歸部分
SELECT e.empno, e.ename, e.job, e.mgr, e.deptno? ---- 要加入的新一層數(shù)據(jù)，來自要遍歷的emp表
,1 + t.the_level? ? ? ? ? ? ---- 遞歸層次，在原來的基礎(chǔ)上加1。這相當(dāng)于CONNECT BY查詢中的LEVEL偽列
,t.path||'\'||e.ename? ? ? ? ---- 把新的一截路徑拼上去
,t.top_manager? ? ? ? ? ? ? ---- 直接繼承原來的數(shù)據(jù)，因為每個路徑的根節(jié)點只有一個
FROM t, scott.emp e? ? ? ? ? ? ? ? ? ? ---- 典型寫法，把子查詢本身和要遍歷的表作一個連接
WHERE t.empno = e.mgr? ? ? ? ? ? ---- 原來的CONNECT BY條件
) ---- WITH定義結(jié)束
SELECT * FROM T
EMPNO ENAME? ? ? JOB? ? ? ? MGR DEPTNO? THE_LEVEL PATH? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TOP_MANAGER
----- ---------- --------- ----- ------ ---------- -------------------------------------------------------------------------------- -----------
7839 KING? ? ? PRESIDENT? ? ? ? ? 10? ? ? ? ? 1 \KING? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7566 JONES? ? ? MANAGER? ? 7839? ? 20? ? ? ? ? 2 \KING\JONES? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7698 BLAKE? ? ? MANAGER? ? 7839? ? 30? ? ? ? ? 2 \KING\BLAKE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7782 CLARK? ? ? MANAGER? ? 7839? ? 10? ? ? ? ? 2 \KING\CLARK? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7999 MIKE? ? ? ANALYST? ? 7566? ? 30? ? ? ? ? 3 \KING\JONES\MIKE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7499 ALLEN? ? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\ALLEN? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7521 WARD? ? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\WARD? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7654 MARTIN? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\MARTIN? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7788 SCOTT? ? ? ANALYST? ? 7566? ? 20? ? ? ? ? 3 \KING\JONES\SCOTT? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7844 TURNER? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\TURNER? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7900 JAMES? ? ? CLERK? ? ? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\JAMES? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7902 FORD? ? ? ANALYST? ? 7566? ? 20? ? ? ? ? 3 \KING\JONES\FORD? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7934 MILLER? ? CLERK? ? ? 7782? ? 10? ? ? ? ? 3 \KING\CLARK\MILLER? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7369 SMITH? ? ? CLERK? ? ? 7902? ? 20? ? ? ? ? 4 \KING\JONES\FORD\SMITH? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
7876 ADAMS? ? ? CLERK? ? ? 7788? ? 20? ? ? ? ? 4 \KING\JONES\SCOTT\ADAMS? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING
1、創(chuàng)建測試表，create table test_connect(id number, p_id number);
2、插入測試數(shù)據(jù)，
insert into test_connect values(1,1);
insert into test_connect values(2,1);
insert into test_connect values(3,2);
insert into test_connect values(4,3);
commit;
3、查詢數(shù)據(jù)表內(nèi)容，select * from?test_connect ，
4、執(zhí)行遞歸查詢語句，加入nocycle要素，不會出現(xiàn)【ORA-01436: 用戶數(shù)據(jù)中的 CONNECT BY 循環(huán)的錯誤】，執(zhí)行結(jié)果如下，
select *
from test_connect t
start with id = 4
connect by nocycle prior t.p_id = t.id
下面是用oracle數(shù)據(jù)庫解決不用start?with?來查詢子父數(shù)據(jù)查詢方法，里面主要用到了substr?和instr?函數(shù)（這兩個函數(shù)，其他數(shù)據(jù)庫也有相對應(yīng)的函數(shù)），游標(biāo)（其他數(shù)據(jù)庫也有游標(biāo)）。
-- 1 前提：創(chuàng)建表以及插入數(shù)據(jù)
CREATE TABLE TMP_TEST(MAIN_COLUMN VARCHAR2(10),PARENT_COLUMN VARCHAR2(10));
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('A',NULL);
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('B','A');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('C','A');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('D','A');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('E','B');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('F','C');
INSERT INTO TMP_TEST(MAIN_COLUMN,PARENT_COLUMN) VALUES('G','E');
-- 2 創(chuàng)建存儲過程
CREATE OR REPLACE PROCEDURE GET_TREE(IS_PARENT?? IN NUMBER /** 子父查詢 **/,
SEARCH_ID?? IN VARCHAR2 /** 查詢條件節(jié)點 **/,
TREE_RESOUT OUT VARCHAR2 /** 輸出結(jié)果集合 **/)
AS
V_TEMP VARCHAR2(4000);
V_SEARCH VARCHAR2(4000);
V_INDEX INTEGER;
BEGIN
V_TEMP :=SEARCH_ID||'-';
TREE_RESOUT := '';
WHILE length(V_TEMP) 0 LOOP
V_INDEX := instr(V_TEMP,'-');
V_SEARCH := substr(V_TEMP,0,V_INDEX-1);
V_TEMP := substr(V_TEMP,V_INDEX+1);
/*DBMS_OUTPUT.put_line('V_INDEX:'|| V_INDEX ||'V_TEMP:' ||V_TEMP||'V_SEARCH:'|| V_SEARCH);*/
/** 查詢子節(jié)點 **/
if(IS_PARENT = 1) THEN
FOR C1 IN (SELECT * FROM TMP_TEST T1 WHERE T1.PARENT_COLUMN = V_SEARCH) LOOP
TREE_RESOUT := TREE_RESOUT || C1.MAIN_COLUMN;
V_TEMP := V_TEMP || C1.MAIN_COLUMN || '-';
END LOOP;
ELSE
/** 查詢父節(jié)點 **/
FOR C1 IN (SELECT * FROM TMP_TEST T1 WHERE T1.MAIN_COLUMN = V_SEARCH) LOOP
TREE_RESOUT := TREE_RESOUT || C1.PARENT_COLUMN;
V_TEMP := V_TEMP || C1.PARENT_COLUMN || '-';
END LOOP;
END IF;
END LOOP;
/*DBMS_OUTPUT.put_line('TREE_RESOUT:'||TREE_RESOUT);*/
END;
-- 3 調(diào)用存儲過程
declare
TREE_RESULT VARCHAR2(4000);
SEARCH_ID VARCHAR2(4000);
begin
get_tree(1,'A',TREE_RESULT);
dbms_output.put_line('查詢子節(jié)點:' || TREE_RESULT);
get_tree(0,'G',TREE_RESULT);
dbms_output.put_line('查詢父節(jié)點:' || TREE_RESULT);
end;
select * from tableName
start with ?條件A ? -- 開始遞歸的根節(jié)點,可多個條件
connect ?by prior ?條件B ?--?prior ?決定查詢的索引順序
where 條件 C
select t.empno,t.mgr,t.deptno ,level
from emp t
connect by prior t.empno=t.mgr
order by level,t.mgr,t.deptno;
找到empno為7369的所有領(lǐng)導(dǎo)。
select t.*,t.rowid from emp t
start with t.empno = 7369 ? ? ? --從empno為7369的開始查找
connect by prior t.mgr = t.empno ;? ? --上一條數(shù)據(jù)（這里就是empno為7369）的mgr == 當(dāng)前遍歷這一條數(shù)據(jù)的empno（那么就會找到empno為7902的用戶）
找到empno為7566的所有下屬
select t.*,t.rowid from emp t
start with t.empno = 7566
connect by prior t.empno = t.mgr ; --注意：connect by? t.mgr =prior t.empno與左邊寫法含義一樣
start with ：設(shè)置起點，省略后默認(rèn)以全部行為起點。
connect by [condition] ：與一般的條件一樣作用于當(dāng)前列，但是在滿足條件后，會以全部列作為下一層級遞歸（沒有其他條件的話）。
prior ： 表示上一層級的標(biāo)識符。經(jīng)常用來對下一層級的數(shù)據(jù)進(jìn)行限制。不可以接偽列。
level ：偽列，表示當(dāng)前深度。
connect_by_root() ：顯示根節(jié)點列。經(jīng)常用來分組。
connect_by_isleaf ：1是葉子節(jié)點，0不是葉子節(jié)點。在制作樹狀表格時必用關(guān)鍵字。
sys_connect_by_path() ：將遞歸過程中的列進(jìn)行拼接。
nocycle , connect_by_iscycle : 在有循環(huán)結(jié)構(gòu)的查詢中使用。
siblings : 保留樹狀結(jié)構(gòu)，對兄弟節(jié)點進(jìn)行排序
;request_id=162538763316780265474850biz_id=0utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_v2~rank_v29-22-52652111.first_rank_v2_pc_rank_v29_1utm_term=ORACLE%E9%80%92%E5%BD%92%E5%87%BD%E6%95%B0spm=1018.2226.3001.4187
;request_id=162538763316780269872688biz_id=0utm_medium=distribute.pc_search_result.none-task-blog-2~all~baidu_landing_v2~default-5-108683534.first_rank_v2_pc_rank_v29_1utm_term=ORACLE%E9%80%92%E5%BD%92%E5%87%BD%E6%95%B0spm=1018.2226.3001.4187
;request_id=162538763316780265474850biz_id=0utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_v2~rank_v29-10-105773226.first_rank_v2_pc_rank_v29_1utm_term=ORACLE%E9%80%92%E5%BD%92%E5%87%BD%E6%95%B0spm=1018.2226.3001.4187
重慶分公司
重慶分公司
