2012/09/26 - [DBMS] - 순진한 트리(문서) - 재귀쿼리 방법론 에서 언급된 재귀적호출 중 MS-SQL을 이용한 MSDN자료 입니다.
원문 : http://msdn.microsoft.com/ko-kr/library/ms186243(v=sql.105).aspx
CTE(공통 테이블 식)를 사용하면 자기 자신을 참조하는 재귀적 CTE를 만들 수 있으므로 상당히 유용합니다. 재귀적 CTE는 최초 CTE가 반복적으로 실행되어 전체 결과 집합을 얻을 때까지 데이터의 하위 집합을 반환하는 CTE입니다.
재귀적 CTE를 참조하는 쿼리를 재귀 쿼리라고 합니다. 재귀 쿼리의 일반적인 용도는 계층적 데이터를 반환하는 것입니다. 예를 들어 직원을 조직도에 표시하는 경우 또는 부모 제품에 하나 이상의 구성 요소가 있고 이러한 구성 요소가 하위 구성 요소를 가지거나 다른 부모의 구성 요소일 수도 있는 제품 구성 정보(BOM) 시나리오에 데이터를 표시하는 경우가 있습니다.
재귀적 CTE는 SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 내에서 재귀 쿼리를 실행하는 데 필요한 코드를 상당히 단순화할 수 있습니다. 이전 버전의 SQL Server에서 재귀 쿼리를 실행하려면 일반적으로 임시 테이블, 커서 및 논리를 사용하여 재귀 단계의 흐름을 제어해야 합니다. 공통 테이블 식에 대한 자세한 내용은 공통 테이블 식 사용을 참조하십시오.
Transact-SQL의 재귀적 CTE 구조는 다른 프로그래밍 언어의 재귀 루틴과 비슷합니다. 다른 언어의 재귀 루틴은 스칼라 값을 반환하지만 재귀적 CTE는 여러 행을 반환할 수 있습니다.
재귀적 CTE는 다음 세 요소로 구성됩니다.
-
루틴의 호출
재귀적 CTE의 첫 번째 호출은 UNION ALL, UNION, EXCEPT 또는 INTERSECT 연산자로 조인된 하나 이상의 CTE_query_definitions로 구성됩니다. 이러한 쿼리 정의는 CTE 구조의 기본 결과 집합을 형성하기 때문에 앵커 멤버라고 합니다.
CTE_query_definitions는 CTE 자체를 참조하지 않는 경우 앵커 멤버로 간주됩니다. 모든 앵커 멤버 쿼리 정의를 첫 번째 재귀 멤버 정의 앞에 배치하고 UNION ALL 연산자를 사용하여 마지막 앵커 멤버를 첫 번째 재귀 멤버와 조인해야 합니다.
-
루틴의 재귀 호출
재귀 호출에는 CTE 자체를 참조하는 UNION ALL 연산자로 조인된 하나 이상의 CTE_query_definitions가 포함됩니다. 이러한 쿼리 정의를 재귀 멤버라고 합니다.
-
종료 확인
종료 확인은 암시적으로 수행됩니다. 이전 호출에서 반환되는 행이 없을 때 재귀가 중지됩니다.
참고 |
|---|
|
잘못 구성된 재귀적 CTE로 인해 무한 루프가 발생할 수 있습니다. 예를 들어 재귀 멤버 쿼리 정의가 부모 열과 자식 열 모두에 대해 동일한 값을 반환하면 무한 루프가 생성된 것입니다. 재귀 쿼리의 결과를 테스트할 때 INSERT, UPDATE, DELETE 또는 SELECT 문의 OPTION 절에서 MAXRECURSION 힌트 및 0과 32,767 사이의 값을 사용하여 특정 문에 허용되는 재귀 수준의 수를 제한할 수 있습니다. 자세한 내용은 쿼리 힌트(Transact-SQL) 및 WITH common_table_expression(Transact-SQL)을 참조하십시오. |
의사 코드 및 의미 체계
재귀적 CTE 구조에는 앵커 멤버와 재귀 멤버가 적어도 하나씩 포함되어야 합니다. 다음 의사 코드에서는 단일 앵커 멤버와 단일 재귀 멤버가 포함된 간단한 재귀적 CTE의 구성 요소를 보여 줍니다.
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
재귀 실행의 의미 체계는 다음과 같습니다.
-
CTE 식을 앵커 멤버와 재귀 멤버로 분할합니다.
-
앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만듭니다.
-
Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행합니다.
-
빈 집합이 반환될 때까지 3단계를 반복합니다.
-
결과 집합을 반환합니다. 이것은 T0에서 Tn까지의 UNION ALL입니다.
다음 예에서는 Adventure Works Cycles 회사에서 직급이 가장 높은 직원부터 시작되는 계층적 직원 목록을 반환하여 재귀적 CTE 구조의 의미 체계를 보여 줍니다. 이 예 다음에는 코드 실행 연습이 있습니다.
-- Create an Employee table. CREATE TABLE dbo.MyEmployees ( EmployeeID smallint NOT NULL, FirstName nvarchar(30) NOT NULL, LastName nvarchar(40) NOT NULL, Title nvarchar(50) NOT NULL, DeptID smallint NOT NULL, ManagerID int NULL, CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) ); -- Populate the table with values. INSERT INTO dbo.MyEmployees VALUES (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL) ,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1) ,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273) ,(275, N'Michael', N'Blythe', N'Sales Representative',3,274) ,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274) ,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273) ,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285) ,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273) ,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);
USE AdventureWorks2008R2;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO
코드 연습 예
-
재귀적 CTE인 DirectReports는 앵커 멤버와 재귀 멤버를 하나씩 정의합니다.
-
앵커 멤버가 기본 결과 집합 T0을 반환합니다. 이 직원은 회사에서 직급이 가장 높습니다. 즉, 상급 관리자가 없습니다.
다음은 앵커 멤버에서 반환하는 결과 집합입니다.
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ NULL 1 Chief Executive Officer 0
-
재귀 멤버는 앵커 멤버 결과 집합에 있는 직원의 직속 하급자를 반환합니다. 이것은 Employee 테이블과 DirectReports CTE 간의 조인 작업을 통해 수행됩니다. 재귀 호출을 설정하는 것은 바로 CTE 자신에 대한 이 참조입니다. 입력(Ti)으로 사용된 CTE DirectReports 의 직원을 기준으로 조인(MyEmployees.ManagerID = DirectReports.EmployeeID)은 (Ti)를 관리자로 둔 직원을 출력(Ti+1)으로 반환합니다. 따라서 재귀 멤버의 첫 번째 반복은 다음 결과 집합을 반환합니다.
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 1 273 Vice President of Sales 1
-
재귀 멤버가 반복적으로 활성화됩니다. 재귀 멤버의 두 번째 반복에서 3단계의 단일 행 결과 집합(EmployeeID273 포함)이 입력 값으로 사용되어 다음 결과 집합을 반환합니다.
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 273 16 Marketing Manager 2 273 274 North American Sales Manager 2 273 285 Pacific Sales Manager 2
재귀 멤버의 세 번째 반복에서 위의 결과 집합이 입력 값으로 사용되어 다음 결과 집합을 반환합니다.
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ 16 23 Marketing Specialist 3 274 275 Sales Representative 3 274 276 Sales Representative 3 285 286 Sales Representative 3
-
실행 중인 쿼리가 반환하는 최종 결과 집합은 앵커 멤버와 재귀 멤버가 생성한 모든 결과 집합의 합집합입니다.
다음은 이 예에서 반환되는 전체 결과 집합입니다.
ManagerID EmployeeID Title Level --------- ---------- ----------------------------- ------ NULL 1 Chief Executive Officer 0 1 273 Vice President of Sales 1 273 16 Marketing Manager 2 273 274 North American Sales Manager 2 273 285 Pacific Sales Manager 2 16 23 Marketing Specialist 3 274 275 Sales Representative 3 274 276 Sales Representative 3 285 286 Sales Representative 3
'DBMS > MS-SQL' 카테고리의 다른 글
| MS SQL 2005 관련 관리 툴들의 메뉴 한글 깨짐 현상 (1) | 2008.09.08 |
|---|---|
| MS-SQL 2008 설치시 Microsoft Windows 유니코드 언어 설정 (0) | 2008.09.08 |