[mssql] 계층적 구조 표현하기.

sql server 온라인 도움말 에 있는것.

 

 

계층 확장

데이터베이스에 계층 정보가 포함될 경우도 있습니다. 예를 들어, 다음 데이터는 전세계 지역에 대한 계층을 표현한 것입니다. 그러나 데이터에 함축되어 있는 구조를 명확하게 알 수 없습니다.

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이 재귀를 지원하지만 진행 중인 처리를 위해 모든 항목을 추적하는 스택으로 임시 테이블을 사용하는 것이 보다 효율적입니다. 특정한 항목의 처리가 끝나게 되면 스택에서 제거되고 새로운 항목이 발견되면 스택에 추가됩니다.

CREATE PROCEDURE expand (@current char(20)) asSET NOCOUNT ONDECLARE @level int, @line char(20)CREATE TABLE #stack (item char(20), level int)INSERT INTO #stack VALUES (@current, 1)SELECT @level = 1WHILE @level > 0BEGIN   IF EXISTS (SELECT * FROM #stack WHERE level = @level)      BEGIN         SELECT @current = item         FROM #stack         WHERE level = @level         SELECT @line = space(@level - 1) + @current         PRINT @line         DELETE FROM #stack         WHERE level = @level            AND item = @current         INSERT #stack            SELECT child, @level + 1            FROM hierarchy            WHERE parent = @current         IF @@ROWCOUNT > 0            SELECT @level = @level + 1      END   ELSE      SELECT @level = @level - 1END -- WHILE

입력 매개 변수 (@current)는 시작할 계층의 위치를 지정합니다. 이 매개 변수는 주 루프의 현재 항목 역시 추적합니다.

계층에서 현재 수준을 계속 추적하는 @level 과 들여쓰여진 줄을 만드는 데 이용되는 작업 공간인 @line의 두 가지 지역 변수가 사용됩니다.

SET NOCOUNT ON문은 각 SELECT의 ROWCOUNT 메시지로 인해 출력이 혼동되는 것을 방지합니다.

계층의 시작점에서 항목 식별자로 임시 테이블인 #stack이 만들어지고 초기화되며 @level이 이에 맞게 설정됩니다. #stack의 level 열은 데이터베이스의 복수의 수준에 같은 항목이 나타나게 합니다. 비록 이러한 상황이 예제의 지리 데이터에 응용되진 않지만 다른 예제에 응용할 수 있습니다.

이 예제에서 @level이 0보다 크면 프로시저는 다음 단계를 따릅니다.

1. 현재 수준(@level)에서의 스택에 항목이 남아 있으면 프로시저는 하나를 선택하고 @current로 호출합니다.
   
   
2. 항목을 @level 공백만큼 들여 쓴 후 인쇄합니다.
   
   
3. 더 이상 처리되지 않는 항목을 스택에서 삭제한 후 다음 수준(@level + 1)의 스택에 모든 하위 항목을 추가합니다. 이 곳이 계층 테이블(#stack)이 유일하게 사용되는 곳입니다..

   상용 프로그래밍 언어를 사용할 경우 각 하위 항목을 찾아서 일일이 스택에 추가해야 합니다. Transact-SQL을 사용하면 별도의 중첩 루프를 쓰지 않고도 하나의 문으로 모든 하위 항목을 찾고 추가할 수 있습니다.

4. 하위 항목이 존재할 경우(IF @@ROWCOUNT > 0), 처리를 위해 수준을 한 단계 낮추고(@level = @level + 1) 그 외에는 현재 수준에서 처리를 진행합니다.
   
   
5. 현재 수준에서 처리 대기 중인 스택의 항목이 없으면 이전 수준(@level = @level - 1)에서 대기 중인 처리가 있는지 알아보기 위해 한 수준 뒤로 갑니다. 이전 수준이 없으면 확장은 완료됩니다.

©1988-2000 Microsoft Corporation. All Rights Reserved.