ORACLE筆記-使用 CONNECT BY 呈現階層化資料

很久很久以前(轉眼已十年惹)學會用 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。

歡迎推文分享:
Published 20 April 2018 10:06 PM 由 Jeffrey
Filed under:
Views: 3,172



意見

# James Su said on 21 April, 2018 10:54 AM

Oracle 10g 有 CTE

Oracle 11g CTE 有支援遞迴

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<April 2018>
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication