[신규 기능]Recursive With
Recursive With
테스트 환경
OS : OEL release 7.2
Tibero : 6 FS04
Recursive With
- WITH 절은 Subquery Factoring이라고도 하며 Subquery를 Fatoring 하는 것이다. (
읭???) - Subquery는 SQL 구문안에서 사용되는 SELECT 쿼리를 말한다.
- Subquery가 사용된 Query는 Main Query와 Subquery로 나뉜다.
- Factoring은 수학 용어로 인수분해(Factorization)를 의미한다.
- 따라서, Subquery Factoring은 WITH 절에서 선언된
query_name으로 Subquery를 인수화 하는것이라 볼수 있다.
(물론 내 나름의 추측해석이므로 판단은 각자 하자.) - 즉, 임시적인 Subquery의 결과 집합이라고 정의할 수 있다.
- Recursive With에서 Recursive는 재귀, With는 위의 그것이다.
- 따라서, WITH 절에서 선언된
query_name을 Subquery의 from 절에서 다시 호출하는 것을 말한다. - 단, 무한루프에 빠지지 않게 적절한 조건을 줘야 한다. (무한루프면 대게 에러가 난다.)
기능 테스트
- 우선 WITH 절은 다음과 같이 구성된다.
SQL> WITH query_name [(column_alias, ...)]
AS
(Subquery)
QUERY ;- 위에 설명한 바와 같이 Recursive With는 WITH 절의 Subquery 내에서
query_name을 호출하므로 아래아 같이 작성할 수 있다. - 또한,
Recursive With문은Initial Subquery(Anchor member)와Recursive Subquery(Recursive member)로 구성된다.
SQL> WITH query_name [(column_alias, ...)]
AS
(SELECT
FROM -- Initial Subquery(Anchor member)
UNION ALL
SELECT
FROM query_name -- Recursive Subquery(Recursive member)
WHERE ...)
QUERY ;[예제1] n Factorial(n!) 구하기
- n Factorial(n!)는 계승이라고도 하며 양수 에 대하여 에서 까지의 모든 자연수의 곱으로 나타낸다.
- 즉, 이다.
SQL> WITH temp (n, fact) AS
(SELECT 0, 1 FROM dual -- Initial Subquery(Anchor member)
UNION ALL
SELECT n+1, (n+1)*fact FROM temp -- Recursive Subquery(Recursive member)
WHERE n < 9)
SELECT * FROM temp ;
N FACT
------- -------
0 1
1 1
2 2
3 6
4 24
5 120
6 720
7 5040
8 40320
9 362880
10 rows selected.
SQL>동작 원리
Initial Subquery가 수행된다.
따라서, 최초temp의 결과는 , 이 된다.- 1.의 결과(
temp)가Recursive Subquery의temp로 호출되어 수행된다.
즉, , 이 된다. Recursive Subquery의 결과(temp)가 다시Recursive Subquery의temp로 호출되어 수행된다.
,- 호출되는
temp가WHERE절을 만족하는 동안 3.을 반복수행한다.
- 까지 수행되므로 의 결과까지 반환된다.
[예제2] 계층 쿼리
- 계층 쿼리란, 계층형 데이터를 조회하기 위한 쿼리다.
- 계층형 데이터는 동일 테이블에서 계층적으로 상위, 하위 관계가 있는 데이터를 의미한다.
- 예로 사원 테이블의 사원과 상사의 관계를 들 수 있다.
- Tibero와 Oracle은 이러한 계층형 데이터를 처리하기 위한 계층 쿼리를 지원한다.
- 이러한 계층 쿼리는
Recursive With문으로 구현할 수 있다. - 테스트를 위한 테이블을 만들어 데이터를 넣는다.
SQL> CREATE TABLE EMPLOYEE (
EMP_NO VARCHAR(8) , /* 사번 */
EMP_NAME VARCHAR(20), /* 이름 */
DEPT_CD VARCHAR(4) , /* 부서 */
MANAGER VARCHAR(8) /* 관리자 */
);
SQL> INSERT INTO EMPLOYEE VALUES ('20063428', 'James', '0000', '19953472')
,('19953472', 'Owner', '1000', NULL)
,('19982201', 'Sandra', '1000', '19953472')
,('20005012', 'Helen', '1000', '19982201')
,('20018786', 'David', '1000', '20005012')
,('20032813', 'Nicolas', '1000', '20018786')
,('19963998', 'Bill', '2000', '19953472')
,('19972002', 'Paul', '2000', '19963998')
,('19976229', 'Fernando','2000', '19972002')
,('19992589', 'John', '2000', '19976229')
,('20027015', 'Karen', '2000', '19992589')
,('20028795', 'Mickey', '2000', '20027015')
,('19963077', 'Chris', '3000', '19953472')
,('19980185', 'Jane', '3000', '19963077')
,('19982915', 'Bob', '3000', '19980185')
,('19994601', 'Nick', '3000', '19982915')
,('20003969', 'Robert', '3000', '19994601')
,('20034532', 'Leonardo','3000', '20003969')
,('20055195', 'Luis', '3000', '20034532')
,('20064224', 'Joy', '3000', '20055195');
SQL>- 계층 쿼리를 통해 결과를 도출한다.
SQL> SELECT LEVEL LVL, EMP_NO, EMP_NAME, MANAGER, PRIOR EMP_NAME MGR_NAME
FROM EMPLOYEE
START WITH MANAGER IS NULL
CONNECT BY PRIOR EMP_NO = MANAGER
ORDER BY LVL ;
LVL EMP_NO EMP_NAME MANAGER MGR_NAME
---------- -------- -------------------- -------- --------------------
1 19953472 Owner
2 19982201 Sandra 19953472 Owner
2 19963998 Bill 19953472 Owner
2 20063428 James 19953472 Owner
2 19963077 Chris 19953472 Owner
3 20005012 Helen 19982201 Sandra
3 19972002 Paul 19963998 Bill
3 19980185 Jane 19963077 Chris
4 20018786 David 20005012 Helen
4 19982915 Bob 19980185 Jane
4 19976229 Fernando 19972002 Paul
5 19992589 John 19976229 Fernando
5 19994601 Nick 19982915 Bob
5 20032813 Nicolas 20018786 David
6 20027015 Karen 19992589 John
6 20003969 Robert 19994601 Nick
7 20028795 Mickey 20027015 Karen
7 20034532 Leonardo 20003969 Robert
8 20055195 Luis 20034532 Leonardo
9 20064224 Joy 20055195 Luis
20 rows selected.
SQL>- 위의 계층 쿼리를 다음과 같은
Recursive With문으로 구현할 수 있다.
SQL> WITH EMP_CTE (LVL, EMP_NO, EMP_NAME, MANAGER, MGR_NAME) AS
(SELECT 1 as LVL , EMP_NO, EMP_NAME, MANAGER, '' as MGR_NAME
FROM EMPLOYEE -- Initial Subquery
WHERE MANAGER IS NULL
UNION ALL
SELECT EC.LVL+1 , E.EMP_NO, E.EMP_NAME, E.MANAGER, EC.EMP_NAME as MGR_NAME
FROM EMPLOYEE E, EMP_CTE EC -- Recursive Subquery
WHERE EC.EMP_NO = E.MANAGER
)
SELECT * FROM EMP_CTE ;여담
사실은 Tibero5에서 추가된 기능이다.
Tibero6 매뉴얼에 신규기능으로 소개되어 해당항목에 있다.
어지간한 Tibero5에서는 된다. 찾아보면 이렇게 매뉴얼에 없는 기능들이 조금씩 있다.
(물론 안되는 하위/상세 버전도 있다.)
참조
하기를 참조하였다.
- Hierarchical and recursive queries in SQL
- Recursive Subquery Factoring
- 공통 테이블 식을 사용하는 재귀 쿼리
'Tibero > Tibero6' 카테고리의 다른 글
| 신규 기능 - 원격 저장소를 가진 실체화 뷰 (0) | 2017.05.22 |
|---|---|
| 신규기능 - Partition Exchange (0) | 2017.01.05 |
| 신규기능 - BINARY TIP (0) | 2017.01.03 |
| 신규 기능 - Listener Multi-port (0) | 2016.11.10 |
| 신규 기능 - OFFLINE FOR DROP (0) | 2016.11.03 |
댓글