오라클 재귀쿼리/계층형쿼리

> 계층형 쿼리란

한 테이블에 담겨 있는 여러 레코드들이 서로 상하 관계(부모, 자식) 관계를 이루며 존재할 때,

이 관계에 따라 레코드를 hierarchical(상하위) 한 구조로 가져올 때 사용되는 SQL을 의미합니다.

오라클에서는 계층형 쿼리를 위해 CONNECT BY문을 지원한다.

> CONNECT BY의 문법적 구조

LEVEL :
현재 테이블에는 존재하지 않는 컬럼
오라클의 모든 SQL에서 사용할수 있는 것으로 해당 데이터가 몇 번째 단계이냐를 의미함

START WITH :
어디부터 시작할 것인지를 정함 ( 최상위 레코드 )

CONNECT BY PRIOR :
계층 구조에서 각 행의 연결 관계를 설정
EMP 테이블에서 EMPNO, MGR 같은

PRIOR :
어디부터 시작할 것인지를 정해 준다.

예시 :

SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
CONNECT_BY_ROOT  empno "Root empno", level
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
 

ENAME                    EMPNO  Root empno     LEVEL
------------------     ------- -----------   -------
KING                      7839    7839           1
    JONES                 7566    7839           2
        SCOTT             7788    7839           3
            ADAMS         7876    7839           4
        FORD              7902    7839           3
            SMITH         7369    7839           4

> 오라클에서만 먹힌다

오라클11g부터는 CONNECT BY절을 쓸 수 있지만 다른 DBMS나 이전버전에선 안먹히니
WITH를 사용한 재귀 쿼리 (계층형 쿼리) 사용을 하도록하자

예제 : WITH를 사용한 1부터 10까지 출력하는 재귀쿼리

WITH NUMBERS AS(
SELECT 1 AS NUM
FROM DUAL
UNION ALL
SELECT A.NUM + 1 AS NUM
FROM NUMBERS A
WHERE A.NUM < 10
)
SELECT B.NUM
FROM NUMBERS B

참고)
https://velog.io/@kms0211/Oracle-WITH%EB%AC%B8%EA%B3%BC-%EC%9E%AC%EA%B7%80%EC%BF%BC%EB%A6%AC
https://yunamom.tistory.com/247
https://camel-context.tistory.com/16