五餅二魚工作室

這幾天剛好有同事需要把原本接 Oracle 的程式，改成連接 SQL Server 來使用，雖然大部分 ANSI 語法都能很快地轉換過去，但遇到一些 Oracle 的特殊寫法，轉換上遇到一些問題，因此就協助做了一個範例，也讓自己順便熟悉一下 Oracle 和 SQL 語法的差異。

此次主要遇到問題的在 Oracle 下使用 START WITH .... CONNECT BY.... 語法上轉換遇到了一些問題，在 Oracle 下那樣的寫法，可以很快的將階層式的查詢給完成了，但在 SQL Server 下，雖然可以使用 CTE 來做遞迴的處理，但有些小地雷需要注意一下，因為相關範例資料要準備有點麻煩，因此我這裡借用黑大的文章「ORACLE筆記-使用 CONNECT BY 呈現階層化資料」，將他的範例拿出來使用，說明一下 SQL Server 下要注意的地方。

我先參考黑大的作法，先做一個測試用的範例資料

-- 範例資料表
CREATE TABLE BOMDemo (
       PartNo VARCHAR(8),
       PartName VARCHAR(16),
       ParentPartNo VARCHAR(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');

 

一般這種要展階層的處理，透過 CTE 來做是蠻方便的，以下面的範例來說，要注意的就是一開始產生第一階的資料 ( 一般在生產管理裡面，我們會習慣稱為第 0 階，但在這裡我就配合原本 Oracle 的範例，稱為第一階 )，因為會談入兩個 NULL 的值，因此這裡要確認資料的欄位型態，因此我搭配 CAST 來做個轉換

;
WITH BOM AS
(
	SELECT PartNo, PartName, CAST(NULL AS VARCHAR(8)) ParentPartNo, CAST(NULL AS VARCHAR(16)) ParentPartName , 1 AS [Level]
	FROM BOMDemo WHERE ParentPartNo='ROOT'
	UNION ALL
	SELECT BOMDemo.PartNo, BOMDemo.PartName, BOM.PartNo, BOM.PartName, BOM.[Level] + 1
	FROM BOM 
	JOIN BOMDemo ON BOMDemo.ParentPartNo = BOM.PartNo
)
SELECT * FROM BOM

從上面的狀況看起來，要把語法轉換過來是蠻容易的，但是如果我要跟黑大的範例是一樣的結果，那要要怎麼來改寫呢 ? 一般來說會很直覺的寫成這樣

;
WITH BOM AS
(
	SELECT PartNo, PartName,'/'+PartName PartPath, 1 AS [Level]
	FROM BOMDemo WHERE ParentPartNo='ROOT'
	UNION ALL
	SELECT BOMDemo.PartNo,REPLICATE(N' ',Level*4)+BOMDemo.PartName,BOM.PartPath+'/'+BOMDemo.PartName, [Level] + 1
	FROM BOM 
	JOIN BOMDemo ON BOMDemo.ParentPartNo = BOM.PartNo
)
SELECT * FROM BOM

很不幸的這樣會爆出錯誤

此次主要會有問題的原因，是因為我們在做階層處理的，雖然第一階的值不是 NULL，但因為後續會加上階層的欄位資料，因此可能會造成字串長度溢位的問題，因此這裡最好也是搭配 CAST 來做個處理，修改後的語法如下：

;
WITH BOM AS
(
	SELECT PartNo, CAST(PartName AS VARCHAR(MAX)) PartName,CAST('/'+PartName AS VARCHAR(MAX)) PartPath, 1 AS [Level]
	FROM BOMDemo WHERE ParentPartNo='ROOT'
	UNION ALL
	SELECT BOMDemo.PartNo,CAST(REPLICATE(N' ',Level*4)+BOMDemo.PartName AS VARCHAR(MAX)) ,BOM.PartPath+'/'+BOMDemo.PartName, [Level] + 1
	FROM BOM 
	JOIN BOMDemo ON BOMDemo.ParentPartNo = BOM.PartNo
)
SELECT * FROM BOM

這樣就可以得到相同的結果了

實作完上述的範例，看起來在 Oracle 下使用 START WITH ... CONNECT BY ... ，的確是比 SQL Server 下面使用 CTE 來的方便多了。