很久很久以前(轉眼已十年惹)學會用 SQL 2005 Common Table Expression 呈現資料表中的階層式資料(組織圖、BOM表),當時還學到 ORACLE 有個 CONNECT BY 語法效果類似。說來慚愧,對它只停在「知道」的層次,沒實地玩過,直到今天遇到必須使用 ORACLE 查詢展開組織圖的需求卻寫不出來,確認了「其實我不會」。

亡羊補牢,猶未晚矣,特筆記今天的實地演練心得備忘。

測試資料直接借用 CTE 文章裡的 BOM 範例:

使用以下指令產生測試資料:

CREATE TABLE BOMDemo (
       PartNo VARCHAR2(8),
       PartName NVARCHAR2(16),
       ParentPartNo VARCHAR2(8)
);
INSERT INTO BOMDemo VALUES('1','PC','ROOT');
INSERT INTO BOMDemo VALUES('2',N'主板模組','1');
INSERT INTO BOMDemo VALUES('3','CPU','2');
INSERT INTO BOMDemo VALUES('4','RAM','2');
INSERT INTO BOMDemo VALUES('5',N'主機板','2');
INSERT INTO BOMDemo VALUES('6',N'CPU散熱器','2');
INSERT INTO BOMDemo VALUES('7',N'滾珠風扇','6');
INSERT INTO BOMDemo VALUES('8',N'散熱鰭片','6');
INSERT INTO BOMDemo VALUES('9','StorageCage','1');
INSERT INTO BOMDemo VALUES('10',N'DVD燒錄器','8');
INSERT INTO BOMDemo VALUES('11','HD','8');
INSERT INTO BOMDemo VALUES('12','FDD','8');
INSERT INTO BOMDemo VALUES('13',N'機殼模組','1');
INSERT INTO BOMDemo VALUES('14',N'電源供應器','13');
INSERT INTO BOMDemo VALUES('15',N'機殼框架','13');
INSERT INTO BOMDemo VALUES('16',N'面板','13');
INSERT INTO BOMDemo VALUES('17',N'側板','13');

ORACLE 官方文章 Hierarchical Queries 有 CONNECT BY 使用說明,寫得頗為詳細,vr 還有範例可參考。

整理重點如下:

  • START WITH 條件被用來指定樹狀結構的起始點
  • CONNECT BY 條件用來定義從屬關係,由於關聯欄位分別來自父資料列與子資料列,父資料列欄位名稱需加上 PRIOR 運算子以為區別
  • LEVEL 可傳回該筆資料所在階層
  • 產生縮排可用 LPAD(' ', 4 * (LEVEL – 1))
  • 神奇的 SYS_CONNECT_BY_PATH(ColName, '/') 能自動組裝出 "Parent/Child/GrandChild",超方便
SELECT PartNo,
--內縮排版可用LPAD()實現
LPAD(' ', (Level - 1) * 4) || PartName AS PartName,
--SYS_CONNECT_BY_PATH()可快速串接各層欄位字串
SYS_CONNECT_BY_PATH(PartName,'/') AS PartPath,
Level
FROM BOMDemo
--以ParentPartNo='ROOT'這筆做為起始點開始長樹
START WITH ParentPartNo='ROOT'
--欄位名前方的一元運算符PRIOR用於指定父資料欄位
--故此處為"將PartNo等於本筆ParentPartNo的資料列視為父資料列"
CONNECT BY PRIOR PartNo = ParentPartNo

實測結果如下:

與 SQL CTE 遞迴呼叫相比,CONNECT BY 寫法簡潔不少,ORACLE 順手兵器 +1。


Comments

# by James Su

Oracle 10g 有 CTE Oracle 11g CTE 有支援遞迴

Post a comment