oracle sql 進階程式設計學習筆記（二十六)

• 一、RSF執行個體示範
• 二、RSF的限制條件
• 三、與connect by 不同點
• 四、複制connect by功能
• • 1、level僞列
  • 2、sys_connect_by_path函數
  • 3、connect_by_root
  • 4、connect_by_iscycle
  • 5、connect_by_isleaf

遞歸子查詢因子化　recursive subquery factoring RSF

在上篇文章中我們用connect by 示範了遞歸功能，本文将用RSF進行示範

一、RSF執行個體示範

實作connect by同樣功能

with emp  as
 (select e.last_name, e.first_name, e.employee_id, e.manager_id
    from employees e
  ) ,
  emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as lvl -- lvl隻是别名
    from emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl
    from emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) search depth first by last_name set order1
select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name,
       er.first_name,
       er.employee_id,
       er.manager_id
  from emp_recurse er;
           

結果:

同樣也能得到相同的結果，隻是RSF方法看着有點冗長，但它的工作原理了解更簡單，并且支援更複雜的查詢,遞歸的with語句 需要有兩個查詢塊：定位點成員和遞歸成員。這兩個子查詢塊必須通過集合運算union all 結合到一起。前面那個就是定位點，後面那個就是遞歸成員。遞歸子查詢必須引用定義子查詢–這樣做了，就是進行了遞歸。

二、RSF的限制條件

下面元素不能出現在RSF的遞歸成員中使用：

1、DISTINCT 關鍵字或GROUP BY 子句

2、MODEL 子句

3、聚合函數、但在select 清單中可以使用分析函數

4、query_name 的子查詢

5、引用query_name作為右表的聯結

三、與connect by 不同點

1、level僞列被lvl 取代

2、rsf查詢傳回列必須像這樣 emp_recurse(last_name,first_name,employee_id,manager_id,lvl) 聲明。

3、search depth first by last_name set order1 相當于order by siblings 一樣的層級輸出

而預設 是breadth first 。當然這并不是層級輸出

breadth 示範

with emp  as
     (select e.last_name, e.first_name, e.employee_id, e.manager_id
        from employees e
      
      ) ,
      emp_recurse(last_name,
    first_name,
    employee_id,
    manager_id,
    lvl) as
     (select emp.last_name,
             emp.first_name,
             emp.employee_id,
             emp.manager_id,
             1 as lvl
        from emp
       where emp.manager_id is null
      union all
      select emp.last_name,
             emp.first_name,
             emp.employee_id,
             emp.manager_id,
             empr.lvl + 1 as lvl
        from emp
        join emp_recurse empr
          on empr.employee_id = emp.manager_id) 
    --改為 breadth 輸出 不會按照層級排序一樣
          search breadth first by last_name set order1
    select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name  last_name,
           er.first_name,
           er.employee_id,
           er.manager_id
      from emp_recurse er;
      結果并不是我們要的：
           

search 子句中指定了按照last_name 進行搜尋，也可以指定first_name ,manager_id進行搜尋，以控制每個層級中各行 的順序。

四、複制connect by功能

connect by 函數，運算符，僞列如下

類型	名稱	用途
函數	sys_connect_by_path	傳回目前資料行的所有祖先
運算符	connect_by_root	傳回根資料行的值
運算符	prior	用來表明層級型查詢，在遞歸子查詢中不需要
僞列	connect_by_iscycle	在層級中檢測循環
參數	nocycle	connect by的參數，與connect_by_iscycle一起使用
僞列	connect_by_isleaf	辨別葉子資料行
僞列	level	用來表明層級中的深度

1、level僞列

lvl 實作了connect by中的level作用

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as lvl
    from employees emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search depth first by last_name set   last_name_order
select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name, er.lvl
  from emp_recurse er
  order by  last_name_order;
           

2、sys_connect_by_path函數

本函數用于傳回組成層級的直到目前行的值。

select LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  sys_connect_by_path(emp.last_name,':') path
  from employees emp
  connect by  prior emp.employee_id=emp.manager_id
  start with emp.manager_id is null
  order    siblings by emp.last_name;
           

可以看到 sys_connect_by_path函數建立了一個從根到目前節點之間層級。這個函數不能在RSF查詢中使用，同樣可以使用與産生level相同的辦法實作。

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl,
path) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as lvl,
         ':'||to_char(emp.last_name) path
    from employees emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl,
         empr.path||':'||emp.last_name
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search depth first by last_name set   last_name_order
select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name, er.path
  from emp_recurse er
  order by  last_name_order;
           

大家會發現通過下面的改動可以實作根元素之前沒有分隔符，并且分隔符還能修改為’,’，

—修改分隔符為’,'示範

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl,
path) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as lvl,
         emp.last_name  path
    from employees emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl,
         empr.path||','||emp.last_name
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search depth first by last_name set   last_name_order
select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name, er.path
  from emp_recurse er
  order by  last_name_order;
           

但是sys_connect_by_path函數中根節點的分隔符是無法去掉：

select LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
--把分隔符改為','
  sys_connect_by_path(emp.last_name,',') path
  from employees emp
  connect by  prior emp.employee_id=emp.manager_id
  start with emp.manager_id is null
  order    siblings by emp.last_name;
           

3、connect_by_root

connect_by_root 強化了connect by 的文法，使得它可以傳回目前行的根節點。

select level,LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  connect_by_root last_name root,
  sys_connect_by_path(emp.last_name,':') path
  from employees emp
  where connect_by_root last_name='Cambrault'
  connect by  prior emp.employee_id=emp.manager_id
  order    siblings by emp.last_name;
           

可以看到 sys_connect_by_path可以擷取到節點的根節點

同樣也可以在RSF中複制該功能。

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl,
path) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1               as lvl,
         emp.last_name   path
    from employees emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl,
         empr.path || ':' || emp.last_name
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) search depth first by last_name set last_name_order,
emps as
 (select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name,
--Cambrault 屬于第二層級，是以截取路徑中的第二個名字即可
         substr(er.path, instr(er.path, ':', 1,1)+1, instr(er.path, ':', 1,2)-instr(er.path, ':', 1,1)-1) root,
         substr(er.path, instr(er.path, ':', 1,1)+1) path
    from emp_recurse er
     )
select last_name, root, path from emps where emps.root = 'Cambrault';
           

顯而易見，connect_by_root功能更加靈活，對于擷取第三個層級，同樣不需要改代碼，而

RSF中還需修改截取部分的代碼。

–擷取遞歸層次是3 指定根節點’Hunold’的員工

select level,LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  connect_by_root last_name root,
  sys_connect_by_path(emp.last_name,':') path
  from employees emp
  where connect_by_root last_name='Hunold'
  connect by  prior emp.employee_id=emp.manager_id
  order    siblings by emp.last_name;
           

而對于RSF還需修改代碼

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl,
path) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1               as lvl,
         emp.last_name   path
    from employees emp
   where emp.manager_id is null
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl,
         empr.path || ':' || emp.last_name
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) search depth first by last_name set last_name_order,
emps as
 (select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name,
-- 遞歸層次是3，截取第三個冒号和第二個冒号之間
         substr(er.path, instr(er.path, ':', 1,2)+1, instr(er.path, ':', 1,3)-instr(er.path, ':', 1,2)-1) root,
          substr(er.path, instr(er.path, ':', 1,2)+1) path
    from emp_recurse er
     )
select last_name, root, path from emps where emps.root = 'Hunold';
           

4、connect_by_iscycle

connect_by_iscycle 僞列使得在層級中檢測循環變得很容易。
           

我們通過修改表中employees 表中King的上級為Hunold來示範

UPDATE employees E SET E.MANAGER_ID='103' WHERE E.EMPLOYEE_ID='100';
           

再來使用connect by 查詢

select LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  sys_connect_by_path(emp.last_name,',') path
  from employees emp
  connect by  prior emp.employee_id=emp.manager_id
  start with emp.manager_id ='100'
  order    siblings by emp.last_name;
           

提示ora-1436錯誤

接下來就是

nocycle和connect_by_iscycle大展拳腳的時候了

select LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  sys_connect_by_path(emp.last_name,',') path,
  connect_by_iscycle
  from employees emp
  connect by  nocycle prior emp.employee_id=emp.manager_id
  start with emp.manager_id ='100'
  order    siblings by emp.last_name;
           

connect_by_iscycle 值為1這行資料導緻了錯誤發生。是以解決問題的辦法就是把Kind 的manager_id 設定為空。同樣在RSF中也能實作相同的功能。

with emp_recurse(last_name,
first_name,
employee_id,
manager_id,
lvl) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as lvl
    from employees emp
   where emp.employee_id ='100'
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.lvl + 1 as lvl
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search depth first by last_name set   last_name_order
--set is_or_cycle 表示别名 to 隻能設定但字元值。
      cycle  employee_id set is_or_cycle to '1' default '0'  
select LPAD(' ', lvl * 2 - 1, ' ') || er.last_name last_name, employee_id,
manager_id,is_or_cycle
  from emp_recurse er
  order by  last_name_order;
           

5、connect_by_isleaf

connect_by_isleaf 可以很友善的在層級函數中識别葉子節點。

select LPAD(' ', level * 2 - 1, ' ') || emp.last_name last_name, 
  connect_by_isleaf
  from employees emp
  connect by    prior emp.employee_id=emp.manager_id
  start with emp.manager_id  is null
  order    siblings by emp.last_name;
           

結果中葉子節點被标注了出來，即為1的資料行。

RSF想要複制該功能會有一點挑戰性。用lead 函數實作

with emp_recurse (last_name,
first_name,
employee_id,
manager_id,
as_level) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as as_level
    from employees emp
   where emp.manager_id is null  
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.as_level + 1 as as_level
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search depth first by last_name set   last_name_order
select LPAD(' ', as_level * 2 - 1, ' ') || er.last_name last_name, as_level,
lead(as_level)over(order by last_name_order ) next_as_level,
case when as_level-lead(as_level)over(order by last_name_order )<0
  then 0
    else 1 end is_leaf
  from emp_recurse er
  order by  last_name_order;
           

但是需要主要 的是lead函數是非常依賴排序的

是以當把depth改為breadth時

with emp_recurse (last_name,
first_name,
employee_id,
manager_id,
as_level) as
 (select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         1 as as_level
    from employees emp
   where emp.manager_id is null  
  union all
  select emp.last_name,
         emp.first_name,
         emp.employee_id,
         emp.manager_id,
         empr.as_level + 1 as as_level
    from employees emp
    join emp_recurse empr
      on empr.employee_id = emp.manager_id) 
      search breadth first by last_name set   last_name_order
select lpad(' ', as_level * 2 - 1, ' ') || er.last_name last_name, as_level,
lead(as_level)over(order by last_name_order ) next_as_level,
case when as_level-lead(as_level)over(order by last_name_order )<0
  then 0
    else 1 end is_leaf
  from emp_recurse er
  order by  last_name_order;
           

很明顯得到的結果時錯誤的。