[SQLD] Oracle SQL - 계층형쿼리 (Hierarchy Query)

에필로그

실무에서 실제로 사용되고 있는 것을 정리 및 요약하려고 합니다. ' 이것들이 어디에 사용될까? ' 라고 생각하시는 분들을 위해서 재무와 연관지어서 작성을 하겠습니다. 다만, 정보보호 및 내용, 용어 등은 현재 실무와 전혀 상관없이 필자가 임의로 작성한 것입니다. 계층형 쿼리는 주로 사용되지는 않아 쉽게 까먹을 수 있으니, 자주 보고 이렇게 활용 될수도 있다고만 봐주시면 감사하겠습니다.

 

계층형 쿼리란?

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용합니다. 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말합니다.

 

자료 구조 중, 트리 구조를 생각하시면 조금 더 이해하시기 편하실 거라고 생각됩니다.

 

START WITH

계층 구조 전개의 시작(루트) 지점을 지정하는 구문

START WITH 상위코드 IS NULL 로 시작하는 경우가 대다수 이다.
최상단 노드 (루트 노드) 의 경우에는 하나인 경우가 많아 다음과 같이 설정하는 경우가 많다. 하지만 상위코드의 경우 PK 값이라고 볼 수는 없다. 중복된 코드 값이 설정 될 수도 있기 때문에 PK 라고 본다기 보다는 처음 시작하는 구문에 사용되는 곳만 유일한 값일 것이라는 생각만 하면 될 것 같다.

 

CONNECT BY [PRIOR, NOCYCLE] 

연결 관계를 설정하는 구문

순방향 전개 : 계층 구조가 부모 데이터 (상위 데이터) 에서 자식 데이터 (하위 데이터) 방향으로 전개되는 것
예) PRIOR 자식 = 부모 / 부모에서 자식으로 방향

역방향 전개 : 계층 구조가 자식 데이터 (하위 데이터) 에서 부모 데이터 (상위 데이터) 방향으로 전개되는 것
예) PRIOR 부모 = 자식 / 자식에서 부모으로 방향

NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이 상황을 사이클(CYCLE)이 형성되었다고 말합니다. 사이클이 발생한 데이터는 런타임 오류가 발생합니다. 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않습니다.

순방향 전개, 역뱡향 전개 라는 내용은 시험에 자주 객관식으로 출제되고 있습니다. 하지만 실무에서는 내용들을 출력해주는 방향만 알면 되기 때문에 '이건 역방향으로 전개해야만 해' 이런 느낌은 딱히 없는 것 같습니다. 다만 CONNECT BY 부분은 까먹거나 헷갈릴 수도 있는 부분이 있기 때문에 가끔 봐보도록 하면 좋을 것 같습니다.

 

ORDER SIBLINGS BY 컬럼명

동일한 레벨의 노드 (형제 노드) 사이에서 정렬을 수행합니다.

 

예시 - 계정 과목 관련 테이블

테이블 구조는 다음과 같습니다.

ACCT_CODE (PK)	ACCT_NAME	TOP_ACCT_CODE
계정 코드 (PK)	계정 명	상위 계정 코드

 

계정 테이블 예시

 

 

SQL - 코드

SELECT LEVEL         /* LEVEL */
	,  ACCT_CODE     /* 계정 코드 */
	,  ACCT_NAME     /* 계정 명 */
    ,  TOP_ACCT_CODE /* 상위 계정 코드 */
 FROM  DUAL          /* 테이블 명 */
 START WITH TOP_ACCT_CODE IS NULL
 CONNECT BY PRIOR ACCT_CODE = TOP_ACCT_CODE;

 

SQL - 결과

LEVEL	ACCT_CODE	ACCT_NAME	TOP_ACCT_CODE
1	10000	부채	(NULL)
2	11000	미지급금	10000
3	11010	원화미지급금	11000
3	11020	외화미지급금	11000