11 장 SQL 프로그래밍 2-CTE

1. 11장 SQL 프로그래밍 2-CTE

2. 뷰와 파생된 테이블(테이블의 일부분)의 장점을 결합하여 사용하는 방법 • CTE란? • Common Table Expressions(공통테이블 표현)의약자 • 재귀 CTE(Recursive CTE)와 비재귀 CTE(Non-Recursive CTE) • 장점 • ANSI-SQL99 호환(DBMS에서도 응용이 가능) • 다른 기능에 비해 짧은 코드로 계층 구조를 처리 • 성능 향상(내부 구현이 커서나 임시 테이블이 아닌 집합 기반의 쿼리 사용) • 뷰와 파생된 테이블, 사용자 정의 함수의 장점을 결합한 형태 • 실행되는 동안에만 존재하고 코드 길이 짧고, 일괄처리에서 정의된 사용자 정의변수를 참조할 수 있는 명명된 테이블 식 • 파생된(Derived) 테이블 • 쿼리가수행되는 동안 존재하는 명명된 테이블(테이블처럼 사용할 수 있는 식) 식 • 뷰(view) – 가상 테이블 • 삭제하기 전까지테이블처럼 사용할 수 있는 테이블 • 정의된 변수를 사용해야 하는 단점이 존재 • 사용자 정의 함수 • 입력 매개변수를 참조할 수 있지만 뷰와 같은 특성을 지님

3. 파생된 테이블, 뷰, 사용자 정의함수, CTE 비교 • 비재귀 CTE • 뷰, 파생된 테이블, 사용자 정의함수(UDF)의 대안 • 비재귀 CTE 기본작성 문법

4. 비재귀CTE의 외부쿼리 문법구조 • 비재귀 CTE 작성 및 사용 관리자가 관리하는 고용인 수 CTE 정의

5. 비재귀 CTE 작성 및 사용 금년도와 전년도 총 매출 비교 - 하나의 CTE에서 조인 후 결과 도출

6. 비재귀 CTE에서 다중 CTE 작성 • 하나의 with문으로 여러 개의 CTE 정의

7. 비재귀 CTE : CTE를 통한 데이터 수정 • CTE에서 갱신할 데이터를 생성한 후 CTE를 통해 데이터 변경

8. 재귀 CTE • 일반적인 재귀 호출은 코드의 길이가 길다. • 자신에대한 참조가 가능한 CTE – 코드의 길이를 단축, 집합 기반의 쿼리 사용 • 단일 부모 구조 작성 • CTE 가능 구문 • AM UA RM / AM UA RM UA RM / AM U AM UA RM / AM UA AM UA RM DB에서의 쿼리 형상

9. 재귀 CTE 작동방식 • AM은 최초의 시작점 지정, 오직 한번만 호출되고 CTE 이름을 참조하지 않음 • RM은 CTE를 참조하고 첫 번째 쿼리 결과(AM의 결과)를사용해 빈 결과 집합이 리턴될 때까지 반복적으로 호출 • 즉, 외부쿼리에서 CTE를 참조하면, AM 쿼리와 RM쿼리에서 리턴되는 결과를 UNION ALL해서 반환함 재귀호출 수 제한(동 레벨) SELECT * FROM EmpCTE order by MgrID option (MAXRECURSION 2); -- 외부 쿼리 --

10. 재귀 CTE • 다중 부모 계층 구조 • 계층 구조(BOM)를 처리하기 위한 방법 • BOM(billOf Material) : 하나의 제품을 생성하기 위해서는 여러 개의 소스가 요구됨 • 하나의 재료가 여러 제품에 사용됨 • 형제데이터 정렬하기 • 레벨단위 정렬 • Order by는 전체 레벨에 상관없이 정렬 • 따라서, 연관성 있는 데이터들을 정렬하는 방법