存取與變更關聯式資料 展開階層架構 資料庫經常會儲存階層架構資訊 (Hierarchical Data)。例如,請看下方的資料表,它包含的資料以階層架構的方式來表示世界上的地區。 CREATE TABLE Hierarchy (Parent VARCHAR(20) NOT NULL, Child VARCHAR(20), CONSTRAINT UIX_ParentChild UNIQUE NONCLUSTERED (Parent,Child)) CREATE CLUSTERED INDEX CIX_Parent ON Hierarchy(Parent) GO INSERT Hierarchy VALUES('World','Europe') INSERT Hierarchy VALUES('World','North America') INSERT Hierarchy VALUES('Europe','France') INSERT Hierarchy VALUES('France','Paris') INSERT Hierarchy VALUES('North America','United States') INSERT Hierarchy VALUES('North America','Canada') INSERT Hierarchy VALUES('United States','New York') INSERT Hierarchy VALUES('United States','Washington') INSERT Hierarchy VALUES('New York','New York City') INSERT Hierarchy VALUES('Washington','Redmond') GO 此表示法並沒有清楚地顯示資料所隱含的結構。 Parent Child ---------------------------------- ---------------------------------- World Europe World North America Europe France France Paris North America United States North America Canada United States New York United States Washington New York New York City Washington Redmond 底下的範例將較容易解釋: World North America Canada United States Washington Redmond New York New York City Europe France Paris 下列的 Transact-SQL 程序會將編碼過的階層架構展開成任意的深度。雖然 Transact-SQL 支援遞迴 (Recursive),但若使用暫存資料表作為堆疊 (Stack) 來追蹤所有已經開始處理但尚未完成的項目將會比較有效率。當特定項目處理完畢之後,它將從堆疊中移除。而找到的新項目則加入堆疊之中。 CREATE PROCEDURE expand (@current char(20)) AS SET NOCOUNT ON DECLARE @lvl int, @line char(20) CREATE TABLE #stack (item char(20), lvl int) INSERT INTO #stack VALUES (@current, 1) SELECT @lvl = 1 WHILE @lvl > 0 BEGIN IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl) BEGIN SELECT @current = item FROM #stack WHERE lvl = @lvl SELECT @line = space(@lvl - 1) + @current PRINT @line DELETE FROM #stack WHERE lvl = @lvl AND item = @current INSERT #stack SELECT Child, @lvl + 1 FROM Hierarchy WHERE Parent = @current IF @@ROWCOUNT > 0 SELECT @lvl = @lvl + 1 END ELSE SELECT @lvl = @lvl - 1 END -- WHILE 輸入參數 (@current) 指出了階層架構開始的位置。它也可在主要迴圈 (Main Loop) 中追蹤目前的項目。 所使用的區域變數為 @lvl,它可在階層架構中追蹤目前層級,而 @line 則是用來建構縮排 (Indent) 行的工作區域。 SET NOCOUNT ON 陳述式使用 ROWCOUNT 訊息避免每個 SELECT 陳述式的輸出變得凌亂。 暫存資料表 #stack 將由階層架構中啟動點 (Starting point) 的項目識別項建立及啟動,而 @lvl 則設成符合資料表中的層級。#stack 中的 lvl 資料行允許相同的項目出現在資料庫的多個層級內。雖然此狀況並不能套用至範例中的地理資料,但可套用於其他範例內。 在此範例中,當 @lvl 大於 0 時,程序將遵循這些步驟: 若堆疊中的目前層級 (@lvl) 內有任何項目,該程序將選擇一個,並將它叫做 @current。 將該項目縮排 @lvl 個空白,然後列印該項目。 從堆疊中刪除該項目,讓它不再被處理,然後將它的子項目加到下個層級 (@lvl + 1) 的堆疊內。這是唯一會使用階層架構資料表 (#stack) 的地方。 在傳統的程式語言中,您必須找出每個子項目,然後個別地將它加入堆疊內。使用 Transact-SQL ,您可使用單一陳述式找出所有的子項目,並將它們加入,而不需用到另一個巢狀迴圈 (Nested Loop)。 若還有子項目 (IF @@ROWCOUNT > 0),請再降低一個層級 (@lvl = @lvl + 1) 來處理它們;否則繼續在目前層級中進行處理。 若堆疊的目前層級中沒有任何項目正等待處理,請回到上個層級來檢視上個層級中 (@lvl = @lvl - 1) 是否有任何等待的處理。如果已經在最上層,表示展開程序已經完成。 用不同的參數來執行 expand 程序,會傳回描述指定參數所屬階層中層級的結果集。 EXEC expand 'World' --This is the result set. World North America United States Washington Redmond New York New York City Canada Europe France Paris EXEC expand 'United States' --This is the result set. United States Washington Redmond New York New York City ©1988-2004 Microsoft Corporation. All Rights Reserved.