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');
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