CONNECT BY (DML 八)

select date '2017-05-18' + (rownum - 1) dt
from dual
connect by rownum <= (date '2017-05-20' - date '2017-05-18' + 1)

※connect by 可以將連續的日期顯示出來



先看一下EMP的表

員工編號7369的上司員工編號是7902
員工編號7902的上司員工編號是7566
員工編號7566的上司員工編號是7839
員工編號7839的沒有上司，所以可能是老闆

※CONNECT BY可以方便的把這樣的關係做出來

※基本語法

SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic
FROM emp
CONNECT BY PRIOR empno = mgr
-- AND sal > 1500
START WITH mgr is null
ORDER BY LEVEL;

※START WITH 和 CONNECT BY 順序可調換

※不能有WHERE，要加條件直接在CONNECT BY 後面寫，如上面註解的部分

※CONNECT BY 是一定要寫的，START WITH、ORDER BY 可以視情況加進去

※START WITH 表示從哪裡開始，而LEVEL是搭配 CONNECT BY使用的，從圖中可以知道它顯示的是第幾層的關係

※結果：

※CONNECT_BY_ISLEAF、SYS_CONNECT_BY_PATH

SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic,
    decode(CONNECT_BY_ISLEAF, 0, 'root', 'leaf') leaf,
    SYS_CONNECT_BY_PATH(ename,'-->') all_path
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH mgr is null;

※CONNECT_BY_ISLEAF：看下圖可以知道是不是有下屬，沒有就是leaf
SYS_CONNECT_BY_PATH：從START WITH設定的欄位開始往下

※結果：

※PRIOR

SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic,
    decode(CONNECT_BY_ISLEAF, 0, 'root', 'leaf') leaf,
    SYS_CONNECT_BY_PATH(ename,'-->') all_path
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH empno = 7902;

※PRIOR可以在等號的左邊或右邊，或都不寫
CONNECT BY PRIOR empno = mgr：第一個結果，把7902的下屬都顯示出來(管多少人)
CONNECT BY empno = PRIOR mgr：第二個結果，把7902的上司都顯示出來(被多少人管)
CONNECT BY empno = mgr 或 CONNECT BY PRIOR empno = PRIOR mgr(兩邊都寫)：
只會顯示 7902

結果：

※CONNECT_BY_ROOT

SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic,
    CONNECT_BY_ROOT ename conn_name
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH empno = 7566;

※結果：

※開始是7566，7566叫JONES，所以以下都是JONES，PRIOR不管寫在哪都一樣，也就是START WITH設定誰，誰就是root

※ORDER siblings BY

SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic,
    decode(CONNECT_BY_ISLEAF, 0, 'root', 'leaf') leaf,
    SYS_CONNECT_BY_PATH(ename,'-->') all_path
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH mgr is null
-- ORDER BY ename
ORDER siblings BY ename;

※ORDER BY結果可以看出，把整個PIC從小到大排好了，但大部分的人不會這樣用

※ORDER siblings BY結果可以看出是以層級為單位做排序的

※CONNECT_BY_ISCYCLE、NOCYCLE

假設自己的上司是123，123的上司是456，而456的上司又是123，這就是循環，使用這個語法可以判斷有沒有循環

UPDATE emp SET mgr = 7902 WHERE empno = 7839;
    
SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic,
    decode(CONNECT_BY_ISLEAF, 0, 'root', 'leaf') leaf,
    decode(CONNECT_BY_ISCYCLE, 0, 'x', 1, 'o') 有循環嗎
FROM emp
CONNECT BY NOCYCLE PRIOR empno = mgr
START WITH empno = 7839
ORDER siblings BY ename;

※因為目前沒有循環，所以執行第一個SQL，不要 commit 就可以執行了，測試完記得 rollback

※結果：

※7902有上司有下屬，但循環只會顯示7902

※使用 CONNECT_BY_ISCYCLE 一定要使用 NOCYCLE，不然編譯過不了(但使用 NOCYCLE 可以不用 CONNECT_BY_ISCYCLE)

※如果有循環但不打 NOCYCLE 會出現
ORA-01436: CONNECT BY 子句造成使用者資料迴路
01436. 00000 -  "CONNECT BY loop in user data"