1. 비재귀 CTE
View나 사용자정의함수 혹은 하위Query를 통한 Table집계등 어떤 결과를 산출하는 식을 대체할 수 있는 것으로서 다음과 같은 방법으로 생성할 수 있습니다.
| With [CTE이름](Column) As ( 처리내용 ) |
생성된 CTE는 다음과 같이 일반 Select문을 사용하여 쉽게 호출할 수 있습니다.
| Select * From CTE이름 |
| With SalesOrderPrice(ID, Price) As ( Select SalesOrderID, (UnitPrice * OrderQty) From Sales.SalesOrderDetail ) Select ID, Price From SalesOrderPrice; |
CTE를 활용한 간단한 예를 보여주고 있습니다.
UnitPrice * OrderQty를 계산(사실은 LineTotal열값과 같음)한 SalesOrderPrice CTE를 생성하고 조회 합니다. 이때 CTE를 생성하는 부분과 이를 호출하는 Select Query는 하나의 단위가 되어야 하므로 같이 실행하고 있습니다.
이러한 이유로 CTE 정의시에 구현된 Query문의 끝에 ;를 붙여 Query가 끝났음을 알리게 되면 오류가 발생하게 됩니다.
With으로 CTE를 정의할때는 하나가 아닌 여러개의 CTE를 정의를 한꺼번에 할 수도 있습니다.
With SalesOrderPrice(ID, Price) As(
Select SalesOrderID, (UnitPrice * OrderQty) From Sales.SalesOrderDetail ), SalesOrderNumber(ID, Price, OrderNumber) As( Select ID, Price, B.SalesOrderNumber From SalesOrderPrice As A, Sales.SalesOrderHeader As B Where A.ID = B.SalesOrderID ), SalesAccountNumber(ID, Price, OrderNumber, AccountNumber) As( Select ID, Price, B.SalesOrderNumber, B.AccountNumber From SalesOrderPrice As A, Sales.SalesOrderHeader As B Where A.ID = B.SalesOrderID )
Select ID, Price, OrderNumber
From SalesOrderNumber; |
여러 CTE를 정의할경우 각 CTE는 ,문자로 구분해야 합니다.
또한 CTE를 정의할때는 이전에 정의한 CTE를 참조하거나 전혀 다른 Table을 가져와 새로운 CTE로 정의할 수도 있습니다.
CTE를 정의하면 단순한 조회뿐만이 아니라 CTE를 통해 새로운 Data를 저장하거나 수정하는 것이 가능합니다.
With InsertDepartment(ID, Name, [Group]) As(
Select DepartmentID, Name, GroupName From HumanResources.Department )
Insert Into InsertDepartment
Values('Admin', 'AdminGroup'); |
Insert 구문으로 InsertDepartment이름의 CTE를 통해 Data를 추가합니다.
| Select DepartmentID, Name, GroupName From HumanResources.Department; |
With UpdateDepartment(ID, Name, [Group]) As(
Select DepartmentID, Name, GroupName From HumanResources.Department Where DepartmentID = 17 )
Update UpdateDepartment
Set Name = 'Name', [Group] = 'Group'; |
UpdateDepartment로 정의한 CTE를 통해 DepartmentID가 17(위에서 추가한)인 Name과 GroupName값을 수정합니다.
| Select DepartmentID, Name, GroupName From HumanResources.Department; |
변경 사항을 확인합니다.
2. 재귀CTE
재귀호출이라함은 자기자신을 다시 호출하는 경우를 말합니다. 이러한 재귀호출이 가능한 CTE가 바로 재귀CTE입니다.
재귀CTE는 비재귀CTE와는 달리 두가지의 Query문으로 CTE가 정의됩니다.
| With [CTE이름](Column) As( 처리내용 Union All 처리내용 ) |
첫번째 처리내용에 포함되는 Query문을 AM(Anchor Member)이라 하고 두번째 처리문의 Query문을 RM(Recursive Member)이라고 합니다. AM과 RM은 필요하다면 두가지 이상이 들어갈 수 있으며 Union이나 Union All을 통해 연결되지만 주의할 점은 반드시 AM이 먼저 나와야 한다는 것입니다.
재귀CTE가 동작하는 방식은 처음 재귀CTE가 호출되면 AM이 먼저 실행(한번만 실행!)되고 그 값을 가지고 RM이 재귀호출되면서 결과를 만들어 내는 식으로 처리가 진행됩니다. 이때 AM은 재귀CTE를 포함하지 않는 독립된 형태의 Query로 구성되며 RM에서 재귀CTE가 포함되는 구조를 가지고 재귀호출이 발생하게 되는 것입니다.
| With HumanLevel(ID, [Level]) As( Select BusinessEntityID, OrganizationLevel From HumanResources.Employee Where BusinessEntityID = 2 Union All Select BusinessEntityID, OrganizationLevel From HumanResources.Employee As A, HumanLevel As B Where A.OrganizationLevel = B.ID ) Select ID, [Level] From HumanLevel |
재귀 CTE를 호출하면 첫번째 Query요소인 AM에 해당하는
| Select BusinessEntityID, OrganizationLevel From HumanResources.Employee Where BusinessEntityID = 2 |
문이 실행되어 BusinessEntityID가 2인 BusinessEntityID와 OrganizationLevel값을 반환하게 됩니다.
그럼 RM에서는 AM이 반환한 값을 가지고 BusinessEntityID(재귀 CTE를 참조한 값이므로 여기서는 ID가 됩니다.)와 OrganizationLevel값이 같은 항목을 조회하여 최초로 다음 형태의 값을 주게 되는 것입니다.
앞서 말씀드렸듯이 AM은 한번만 실행되지만 RM은 반환하는 값이 전혀없을때까지 재귀호출(자기자신을 호출)되면서 동작합니다. 이는 결과적으로 OrganizationLevel값이 3인것을 다시 조회하고 그 조회한 값에서 다시 OrganizationLevel과 같은 값을 조회하면서 계속 반복하고 최종적으로 다음과 같은 형태의 결과값을 출력하게 됩니다.
RM에서 재귀호출로 인한 동작이 발생하면 더이상 반환값이 없을때까지 반복한다고 설명을 드렸는데 만일 재귀호출될 때마다 값을 반환하게 되면 어떻게 될까요?
재귀호출을 중단하는 조건은 더이상 값을 반환하지 않을 때까지 이므로 무조건 값을 반환하는 형태라면 무한 Loop로 빠지게 되는 문제가 생기게 될 것입니다. 이 문제를 해결하기 위해서 Maxrecursion라는 옵션이 마련되어 있습니다.
Maxrecursion을 사용하려면 재귀CTE를 호출하는 Select문에서 다음과 같이 추가합니다.
| Select ID, [Level] From HumanLevel Option(Maxrecursion 10) |
Maxrecursion 뒤에 숫자를 지정하면 해당 수만큼만 재귀호출이 발생합니다.(Maxrecursion를 지정하지 않을시 기본값은 100이며 0으로 지정하게 되면 무한대가 됩니다.)
댓글 없음:
댓글 쓰기