[오라클 계층형구조] 행정표준코드(기관) 조직 목록 계층구조 활용 2

오라클의 계층쿼리를 사용하려면 테이블이 계층형 구조를 가져야 합니다

 

아래와 같은 데이터 구조가 필요합니다

행	코드	부모코드	정렬순서
1	00000		1
2	00001	00000	1
3	000011	00001	1
4	000012	00001	2

 

 

위 구조를 계층형 구조로 표현하고자 하면 오라클의 SELECT * FROM START WITH ~ CONNECT BY 구문을 활용합니다

 

# START WITH

START WITH 절에서 계층형 구조의 ROOT를 구별하는 조건절을 기재합니다

위 표에서 ROOT는 1번 행이 ROOT라고 가정했을 때 부모코드가 NULL인 조건으로 구분이 가능합니다

 * START WITH 1=1 AND 부모코드 IS NULL

 

 

# CONNECT BY 

그 다음으로 계층형 구조를 연결하는 방법을 기재하는것이 CONNECT BY 절입니다

CONNECT BY는 어떤것이 부모이며, 어떤 컬럼이 자식인지를 기재합니다

 

자식인 경우 컬럼앞에 PRIOR 키워드를 붙여서 어떤것이 자식컬럼인지 명시해야 합니다

 

위 표에서 자식 컬럼은 "코드"컬럼이므로 아래와 같이 사용합니다

CONNECT BY PRIOR 코드 = 부모코드 

 

# ORDER SIBLINGS BY

마지막으로 계층구조의 정렬구문입니다. ORDER SIBLINGS BY 구문을 사용합니다

기존 ORDER BY 구문을 예로 들어서 만들고자 하는 계층이 깊이가 5라고 하면

 

ORDER BY 깊이1정렬, 깊이2정렬, 깊이3정렬, 깊이4정렬, 깊이5정렬

위 정렬 구문과 같이 사용해야 제대로 된 정렬이 가능한데

 

계층형 구조에서는 한개 정렬 컬럼으로 ORDER SIBLINGS BY구문을 활용하여 위 ORDER BY 구문처럼 만들어줍니다

 

 *ORDER SIBLINGS BY 정렬 

 

 

 

 

 

 

앞 포스팅에서 계층구조로 활용할 데이터를 입력하지 못했다면 아래링크를 참고하시길 바랍니다

https://gimmotti.tistory.com/4

[오라클 계층형구조] 행정표준코드(기관) 조직 목록 계층구조 활용 1

 

 

 

 행안부에서 제공하는 조직코드목록 코드를 분석하여 다음과 같은 조건들을 파악합니다

 

#부모자식칼럼

 - 자식코드 : 기관코드

 - 부모코드 : 차상위기관코드

 

# 정렬칼럼 (정렬순서)

 - 유형분류_대 , 유형분류_중, 유형분류_소, 서열

 

# ROOT 조건

 - 차상위기관코드 = '0000000'

 

위 조건을 통해서 다음과 같은 계층형 쿼리가 만들어집니다

SELECT * 

FROM (
SELECT LEVEL 부서DEPTH
       ,SYS_CONNECT_BY_PATH(최하위기관명,'/') 부서PATH /* 1 SYS_CONNECT_BY_PATH 함수는 순환구조의 ROOT에서부터의 경로를 표시해줌 */        
       ,LPAD(' ',2*LEVEL,' ') ||최하위기관명 부서TREE /* 2 트리구조를 표현하기 위한 공백처리 */
       ,최하위기관명
       ,기관코드
       ,차상위기관코드
       ,서열
       ,소속기관차수
       ,유형분류_대
       ,유형분류_중
       ,유형분류_소
FROM (SELECT * FROM EXER_ORG_LIST WHERE 존폐여부 =0)
START WITH 1=1
      AND 차상위기관코드 = '0000000'  /* 3 최상위 ROOT를 결정짓는 조건절 */
CONNECT BY PRIOR 기관코드 =   차상위기관코드 /* 4 부모(차상위기관코드), 자식(기관코드) */ 
ORDER SIBLINGS BY 유형분류_대,유형분류_중,유형분류_소,서열 /* 5 정렬 */
)
WHERE 1=1 

 

# 주석1

계층형 쿼리에서 사용가능한 함수 SYS_CONNECT_BY_PATH가 있습니다

이 함수는 ROOT부터 현재 연결된 계층구조를 텍스트로 표현해줍니다

표현해줄 칼럼과 연결 구분자를 지정해줍니다

 

 

#주석2

  계층형 쿼리에서 LEVEL 칼럼을 사용할 수 있는데 계층형 구조의 깊이를 가지고 있습니다. 이를 이용해서 아래와 같이 하위 뎊스에 공백을 넣어줄 수 있습니다

부서1

  부서2

  부서4

    부서3

 

#주석3

 ROOT를 식별하는 조건입니다

 

#주석4

 계층구조를 어떻게 연결할지를 결정하는 조건입니다. 부모와 자식을 구분하는 PRIOR 키워드를 어디에 넣을지는 데이터를 부고 부모자식관계를 결정해야 합니다. 위 쿼리는 순행구조로 작성되어있습니다

 

#주석5

 계층구조의 정렬을 잡아주기 위한 부분입니다

 

 

 

전체 부서 목록이 계층화되어 출력되었습니다

 

조건절을 주어서 부분적으로 조직을 조회하는 방법은 다음과 같습니다

 

 

# 부분적인 부서의 트리구조를 검색할 경우 

 START WITH 절에  차상위기관코드 = '0000000' 조건을 사용하면 전체 계층구조를 조회할 수 있습니다

 과학기술통신부의 부서계층구조만 가져올 경우 기관코드 ='1721000'( 과학기술정보통신부 기관코드)를

 ROOT로 지정하면 과학기술정보통신부의 조직만 계층구조로 표현됩니다

  * 계층구조의 검색은 관점을 달리해서 다양하게 조회가 가능합니다

 

 

SELECT * 

FROM (
SELECT LEVEL 부서DEPTH
       ,SYS_CONNECT_BY_PATH(최하위기관명,'/') 부서PATH       
       ,LPAD(' ',2*LEVEL,' ') ||최하위기관명 부서TREE
       ,최하위기관명
       ,기관코드
       ,차상위기관코드
       ,서열
       ,소속기관차수
       ,유형분류_대
       ,유형분류_중
       ,유형분류_소
FROM (SELECT * FROM EXER_ORG_LIST WHERE 존폐여부 =0)
START WITH 1=1
      /* 1 START WITH절*/   
      --AND 차상위기관코드 = '0000000' /* 전체구조에서의 ROOT */
      AND 기관코드 ='1721000'        /* 부분검색의 ROOT */ 
CONNECT BY PRIOR 기관코드 =   차상위기관코드 
ORDER SIBLINGS BY 유형분류_대,유형분류_중,유형분류_소,서열
)
WHERE 1=1

 

 

 

# 해당 부서만 검색

 1) 부서코드로 부서명을 찾고자 할 때는 계층구조를 활용하지 않고 테이블에서 기관코드로 검색합니다

 

 계층형 구조를 검색하는것이 아니면 일반 쿼리의 WHERE 절에 질의하여 결과를 확인합니다

 

SELECT * 
FROM EXER_ORG_LIST 
WHERE 존폐여부 =0
AND 기관코드 ='1726127'

 2) 만약 계층형 구조에서 기관코드로 조회하여 DEPTH계층형 구조의 정보까지 검색해야 한다면 아래와 같습니다

SELECT * 

FROM (
SELECT LEVEL 부서DEPTH
       --,SYS_CONNECT_BY_PATH(최하위기관명,'/') 부서PATH        /* 해당절에서 ORA-30003 오류발생 */
       ,SYS_CONNECT_BY_PATH(최하위기관명,'#') 부서PATH        /* 구분자 변경 / -> # */       
       ,LPAD(' ',2*LEVEL,' ') ||최하위기관명 부서TREE
       ,최하위기관명
       ,기관코드
       ,차상위기관코드
       ,서열
       ,소속기관차수
       ,유형분류_대
       ,유형분류_중
       ,유형분류_소
FROM (SELECT * FROM EXER_ORG_LIST WHERE 존폐여부 =0)
START WITH 1=1
      /* 1 START WITH절*/   
      AND 차상위기관코드 = '0000000' /* 전체구조에서의 ROOT */
       
CONNECT BY PRIOR 기관코드 =   차상위기관코드 
ORDER SIBLINGS BY 유형분류_대,유형분류_중,유형분류_소,서열
)
WHERE 1=1
AND 기관코드 ='1726127'

 

위 검색시 ORA-30003 오류로 인해 SYS_CONNECT_BY_PATH의 구분자를 변경해 주었습니다. 구분자에서 오류가 발생하는 원인은 정확하게는 모르겠으나 슬래시 계열의 특수문자에서 해당오류가 발생하는것 같습니다

 

위 검색결과에 부서PATH와 DEPTH등의 정보를 가져올 수 있습니다

 

 

 

 

 

역방향 구조는 다음과 같습니다

SELECT * 

FROM (
SELECT LEVEL 부서DEPTH
       ,SYS_CONNECT_BY_PATH(최하위기관명,'#') 부서PATH       
       ,LPAD(' ',2*LEVEL,' ') ||최하위기관명 부서TREE
       ,최하위기관명
       ,기관코드
       ,차상위기관코드
       ,서열
       ,소속기관차수
       ,유형분류_대
       ,유형분류_중
       ,유형분류_소
FROM (SELECT * FROM EXER_ORG_LIST WHERE 존폐여부 =0)
START WITH 1=1
      --AND 차상위기관코드 = '0000000'   
      AND 기관코드 ='1726054' 

CONNECT BY  기관코드 = prior 차상위기관코드 
ORDER SIBLINGS BY 유형분류_대,유형분류_중,유형분류_소,서열
)
WHERE 1=1 

ROOT를 1726054(과학기술전략과)로 하여 결과를 출력하면 

과학기술 전력과를 중심으로 역방향 전개를 합니다

 

위 STARTWITH절에 ROOT가 되는 조건을 주고

CONNECT BY 절에 PRIOR 키워드 위치가 변경되었습니다

 

아래와 같이 출력됩니다

 

부서 tree에 띄어쓰기는 @로 대체하였습니다

행번호	부서PATH	부서TREE
1	/과학기술전략과	@과학기술전략과
2	/과학기술전략과/과학기술정책국	@@과학기술정책국
3	/과학기술전략과/과학기술정책국/과학기술혁신본부	@@@과학기술혁신본부
4	/과학기술전략과/과학기술정책국/과학기술혁신본부/과학기술정보통신부	@@@@과학기술정보통신부

 

 

 

이상 트리구조에 대한 포스팅을 마칩니다