存取與變更關聯式資料 

 
展開階層架構
資料庫經常會儲存階層架構資訊 (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.