Oracle에서 Data를 계층형 구조로 가져오는 유용한 쿼리에 대해 알아본다.
DB를 설계하다 보면 상위계층과 하위계층이 연관관계를 가지는 테이블 구조가 많이 나온게 된다. 예를 들어 상품의 카테고리, 게시물과 답변글의 관계 등.
이런 계층형 구조를 가지는 테이블의 Data를 계층형으로 구조로 조회할 수 있는 쿼리를 Oracle에서 지원한다. 그것이 바로 START WITH ~ CONNECT BY ~ 이다.
START WITH : 계층질의의 최상위(부모)로 사용할 기준 Data를 정의한다. 서브쿼리도 사용할 수 있다.
CONNECT BY : 계층구조의 상위와 하위의 관계를 정의한다. 서브쿼리는 사용할 수 없다.
그럼 간단한 사용예를 알아본다.
CREATE TABLE TB_PRGM (
PRGM_ID VARCHAR2(10) NOT NULL,
PRTN_PRGM_ID VARCHAR2(10),
PRGM_NM VARCHAR2(50) NOT NULL,
DISP_ORDER NUMBER(3) NOT NULL,
USE_YN CHAR(1) NOT NULL
)
위와 같이 프로그램에 대해 간단히 정리한 테이블이 있다. PRGM_ID는 프로그램 아이디이며, PRNT_PRGM_ID는 해당 프로그램이 속하는 상위 프로그램의 아이디를 명시한다. 루트 프로그램은 NULL 값을 가진다. DISP_ORDER는 동일 PRNT_PRGM_ID로 묶여 있는 프로그램들의 display ordering 값이다.
PRGM100 NULL 프로그램100 1 Y
PRGM110 NULL 프로그램110 1 Y
PRGM111 NULL 프로그램111 1 Y
PRGM112 NULL 프로그램112 2 N
PRGM120 NULL 프로그램120 2 Y
PRGM121 NULL 프로그램121 1 N
PRGM200 NULL 프로그램200 1 Y
위와 같은 Data가 TB_PRGM 테이블에 저장이 되어 있고, 위의 프로그램 정보를 계층구조로 조회하는 쿼리는 다음과 같다.
SELECT PRGM_ID, PRGM_NM, LEVEL
FROM TB_PRGM
START WITH PRTN_PRGM_ID IS NULL
CONNECT BY PRIOR PRGM_ID = PRTN_PRGM_ID
여기서 LEVEL은 계층구조의 depth를 표현해주는 예약어이다. 위 쿼리는 부모에서 자식으로 계층구조를 만든것이고, 반대로 자식에서 부모로 계층구조를 만들수도 있는데 PRIOR의 위치를 변경하면 된다.
CONNECT BY PRIOR 자식컬럼 = 부모컬럼 // 부모에서 자식으로 계층구조
CONNECT BY 자식컬럼 = PRIOR 부모컬럼 // 자식에서 부모로 계층구조
그럼 여기서 트리(계층구조)로 형성 된 Data를 동일 계층내에서 정렬을 할려면 어떻게 하면 될까? ORDER BY를 사용하게 되면 계층구조로 만들어진 Data들이 ORDER BY 절에 명시 된 컬럼에 의해 다시 정렬이 되어 계층구조는 모두 사라지게 된다. 이때는 ORDER SIBLINGS BY를 사용해야 한다.
SELECT PRGM_ID, PRGM_NM, LEVEL
FROM TB_PRGM
START WITH PRTN_PRGM_ID IS NULL
CONNECT BY PRIOR PRGM_ID = PRTN_PRGM_ID
ORDER SIBLINGS BY DISP_ORDER
한가지 더 WHERE 절을 어떻게 사용하면 되느냐?
SELECT PRGM_ID, PRGM_NM, LEVEL
FROM TB_PRGM
START WITH PRTN_PRGM_ID IS NULL
CONNECT BY PRIOR PRGM_ID = PRTN_PRGM_ID
WHERE USE_YN = 'Y'
위와 같이 WHERE 절을 CONNECT BY 밑에 사용하면 된다.
-- START WITH ~ CONNECT BY ~ 의 실행 순서
1. START WITH 절이 실행되어 계층구조의 루트 기준을 잡는다.
2. CONNECT BY 절에 의해 계층구조를 만든다.
3. WHERE 절에 의해 조건이 실행된다.
DB를 설계하다 보면 상위계층과 하위계층이 연관관계를 가지는 테이블 구조가 많이 나온게 된다. 예를 들어 상품의 카테고리, 게시물과 답변글의 관계 등.
이런 계층형 구조를 가지는 테이블의 Data를 계층형으로 구조로 조회할 수 있는 쿼리를 Oracle에서 지원한다. 그것이 바로 START WITH ~ CONNECT BY ~ 이다.
START WITH : 계층질의의 최상위(부모)로 사용할 기준 Data를 정의한다. 서브쿼리도 사용할 수 있다.
CONNECT BY : 계층구조의 상위와 하위의 관계를 정의한다. 서브쿼리는 사용할 수 없다.
그럼 간단한 사용예를 알아본다.
CREATE TABLE TB_PRGM (
PRGM_ID VARCHAR2(10) NOT NULL,
PRTN_PRGM_ID VARCHAR2(10),
PRGM_NM VARCHAR2(50) NOT NULL,
DISP_ORDER NUMBER(3) NOT NULL,
USE_YN CHAR(1) NOT NULL
)
위와 같이 프로그램에 대해 간단히 정리한 테이블이 있다. PRGM_ID는 프로그램 아이디이며, PRNT_PRGM_ID는 해당 프로그램이 속하는 상위 프로그램의 아이디를 명시한다. 루트 프로그램은 NULL 값을 가진다. DISP_ORDER는 동일 PRNT_PRGM_ID로 묶여 있는 프로그램들의 display ordering 값이다.
PRGM100 NULL 프로그램100 1 Y
PRGM110 NULL 프로그램110 1 Y
PRGM111 NULL 프로그램111 1 Y
PRGM112 NULL 프로그램112 2 N
PRGM120 NULL 프로그램120 2 Y
PRGM121 NULL 프로그램121 1 N
PRGM200 NULL 프로그램200 1 Y
위와 같은 Data가 TB_PRGM 테이블에 저장이 되어 있고, 위의 프로그램 정보를 계층구조로 조회하는 쿼리는 다음과 같다.
SELECT PRGM_ID, PRGM_NM, LEVEL
FROM TB_PRGM
START WITH PRTN_PRGM_ID IS NULL
CONNECT BY PRIOR PRGM_ID = PRTN_PRGM_ID
여기서 LEVEL은 계층구조의 depth를 표현해주는 예약어이다. 위 쿼리는 부모에서 자식으로 계층구조를 만든것이고, 반대로 자식에서 부모로 계층구조를 만들수도 있는데 PRIOR의 위치를 변경하면 된다.
CONNECT BY PRIOR 자식컬럼 = 부모컬럼 // 부모에서 자식으로 계층구조
CONNECT BY 자식컬럼 = PRIOR 부모컬럼 // 자식에서 부모로 계층구조
그럼 여기서 트리(계층구조)로 형성 된 Data를 동일 계층내에서 정렬을 할려면 어떻게 하면 될까? ORDER BY를 사용하게 되면 계층구조로 만들어진 Data들이 ORDER BY 절에 명시 된 컬럼에 의해 다시 정렬이 되어 계층구조는 모두 사라지게 된다. 이때는 ORDER SIBLINGS BY를 사용해야 한다.
SELECT PRGM_ID, PRGM_NM, LEVEL
FROM TB_PRGM
START WITH PRTN_PRGM_ID IS NULL
CONNECT BY PRIOR PRGM_ID = PRTN_PRGM_ID
ORDER SIBLINGS BY DISP_ORDER
한가지 더 WHERE 절을 어떻게 사용하면 되느냐?
SELECT PRGM_ID, PRGM_NM, LEVEL
FROM TB_PRGM
START WITH PRTN_PRGM_ID IS NULL
CONNECT BY PRIOR PRGM_ID = PRTN_PRGM_ID
WHERE USE_YN = 'Y'
위와 같이 WHERE 절을 CONNECT BY 밑에 사용하면 된다.
-- START WITH ~ CONNECT BY ~ 의 실행 순서
1. START WITH 절이 실행되어 계층구조의 루트 기준을 잡는다.
2. CONNECT BY 절에 의해 계층구조를 만든다.
3. WHERE 절에 의해 조건이 실행된다.
