層級查詢並將層級拆分成多列

level, connect_by_isleaf, connect_by_iscycle偽列:

level 就是這個資料屬於 哪一個等級，比如PRESIDENT為1，MANAGER為2
connect_by_isleaf 就是樹的最末端的值，或者說這個樹枝下已經沒有樹葉了
connect_by_iscycle 導致出現死迴圈的那個樹枝  

透過START WITH . . . CONNECT BY . . .子句來實現SQL的層次查詢.
自從Oracle 9i開始，可以透過  SYS_CONNECT_BY_PATH 函式實現將父節點到當前行內容以 “path”或者層次元素列表的形式顯示出來。

自從Oracle 10g 中，還有其他更多關於層次查詢的新特性 。例如，有的時候使用者更關心的是每個層次分支中等級最低的內容。
那麼你就可以利用偽列函式 CONNECT_BY_ISLEAF來判斷當前行是不是葉子。 如果是葉子就會在偽列中顯示“1”，
如果不是葉子而是一個分支（例如當前內容是其他行的父親）就顯示“0”。

在Oracle 10g 之前的版本中，如果在你的樹中出現了環狀迴圈（如一個孩子節點引用一個父親節點）， Oracle 就會報出一個錯誤提示：“ ORA-01436: CONNECT BY loop in user data”。如果不刪掉對父親的引用就無法執行查詢操作。

而在 Oracle 10g 中，只要指定 “NOCYCLE”就可以進行任意的查詢操作。與 這個關鍵字相關的還有一個偽列—— CONNECT_BY_ISCYCLE，  如果在當前行中引用了某個父親節點的內容並在樹中出現了迴圈，那麼該行的偽列中就會顯示“1”，否則就顯示“0”。

1、層級查詢領導與員工的關係

create or replace view a as 
select level as rank,
        connect_by_isleaf as leaf_is_or_not,
        lpad(' ', level * 2 - 1) || sys_connect_by_path(ename, '-')  path1,
         lpad(' ', level * 2 - 1) || sys_connect_by_path(empno, '-')  path2,
         ltrim(ltrim(lpad(' ', level * 2 - 1) || sys_connect_by_path(empno, '-')),'-' ) path3
   from emp e
 connect by prior e.empno = e.mgr
  start with e.mgr is null

將path3 列的內容 拆分成多列進行儲存。

拆分多列需要建立一個函式

create or replace function f_new_rowit(in_text varchar2,--要擷取的字串
                                       fh      varchar2,--擷取識別符號
                                       n       number)--按第幾個符號擷取
                                        return varchar2 is
  Result varchar2(4000);
begin
  if n > 1 then
    SELECT substr(in_text,
                   
                  decode(instr(in_text, fh, n - 1, n - 1),
                         0,
                         0,
                         instr(in_text, fh, n - 1, n - 1) + 1),
                   
                  decode(sign(instr(in_text, fh, n, n) -
                              instr(in_text, fh, n - 1, n - 1)),
                         1,
                         (instr(in_text, fh, n, n) -
                         instr(in_text, fh, n - 1, n - 1)) - 1,
                         -1,
                         length(in_text),
                         0,
                         0)
                   
                  )
      into Result
      FROM dual;
  else
    select substr(in_text, 0, instr(in_text, fh, 1, 1) - 1)
      into Result
      from dual;
  end if;
  return(Result);
end f_new_rowit;

使用函式進行一列分成多列

select max(rank) from a;

這裡可以看出可以最多拆分4列

呼叫函式進行拆分

select rank,
       path1,
       path2,
       path3,
       f_new_rowit(path3, '-', 1) v1,
       f_new_rowit(path3, '-', 2) v2,
       f_new_rowit(path3, '-', 3) v3,
       f_new_rowit(path3, '-', 4) v4
  from a;

將path3 列的資料拆分成了v1 、v2、v3、v4 四列