WITH 절, 재귀 CTE (Common Table Expression)

저번 CTE관련해서, 재귀 CTE

CTE관련된 첫번째 포스팅: WITH 절 CTE (Common Table Expression) 임시테이블?

* 재귀 CTE??

- 적어도 2개이상의 쿼리를 통해 정의

- 적어도 하나는 고정멤버, 나머지 하나는 재귀멤버가 되는 형태

* 기본적인 재귀 CTE 형식

WITH <CTE의 이름> [ (<대상 컬럼목록>) ]

AS

(

<기준 멤버>

UNION ALL

<재귀 멤버>

)

<CTE 외부 쿼리>;

- 기준 멤버는 관계형 결과 테이블을 반환하는 쿼리.

- 재귀 쿼리가 아닌 일반적인 테이블표현식으로 정의되는 쿼리인 기준 멤버는 한 번만 호출

- 기준 멤버와 재귀 멤버의 쿼리가 반환하는 컬럼의 숫자나 컬럼의 데이터 형식은 반드시 일치해야

- 외부 쿼리에서 CTE 이름을 참조하는것은 기준 멤버로부터 만들어지는 단일 결과셋 또는 재귀멤버의 모든 반복적인 호출을 통해

  만들어지는 결과셋을 의미

(여튼 뭔소리인지는 좀 헷갈리네요...)

Ex) 특정 직원에 대한 정보와 이 직원이 관리하는 모든 부하직원들의 목록을 출력하는 방법

WITH TempCTE AS (

select

empid, mgrid, firstname, lastname

from hr.Employees 

where empid=2

UNION ALL

select

C.empid, C.mgrid, C.firstname, C.lastname

from TempCTE AS P

join hr.Employees as C 

ON c.mgrid=P.empid

)

select empid, mgrid, firstname, lastname from EmpsCTE;

-- 결과값

empid mgrid     firstname lastname

2         1         Don         Funk

3         2         Judy         Lew

5         2         Sven         Buck

6         5         Paul         Suurs

7         5         Russell King

9         5         Zoya         Dolgopyatova

4         3         Yael         Peled

8         3         Maria         Cameron

-> 기준 멤버에선 hr.Employees 테이블을 대상으로 직원ID가 2인 행을 반환

재귀 멤버는 (이전의 결과값을 나타내는) CTE를 hr.Employees테이블과 조인한 다음,

이전 결과 집합에서 반환된 직원의 바로 아래단계에 있는 부하직원들을 반환

재귀멤버는 반복적으로 호출되며 호출될때마다 바로 다음 단계에 있느 부하직원들을 출력하게 됨.

- 처음 호출시 직원 2의 바로 다음에 있는 부하직원인 3,5번 반환

- 두번째 호출시 3, 5 직원의 다음에 있는 부하직원인 4,6,7,8,9 번 반환

- 세번째 호출시 결과값없기에 (이들의 부하직원이 없다) 재귀멤버는 빈 값 반환, 작업 완료

* 주의할점

조인할때 논리적인 오류가 발생하거나 반복처리 중 문제 발생시 재귀멤버는 잠재적으로 무한대로 호출.

기본적으로 재귀멤버가 반복해 수행할수있는 횟수를 100번으로 제한함. 100번 호출시 쿼리는 실패로 처리.

이런 최대 반복횟수의 제한 기본값은

외부 쿼리 마지막에 OPTION(MAXRECURSION n)이란 힌트를 통해 변경가능, (n= 0~32,767)

SQL Server. MS SQL은 중간처리값을 tempdb내 작업 테이블에 저장하게되는데 tempdb의 용량이 늘어나다가

디스크 부족 등으로 더이상 증가할수없게되면 쿼리는 실패로 처리