[oracle] SYS_CONNECT_BY_PATH 함수 사용법

출처 :https://m.blog.naver.com/PostView.nhn?blogId=gamejung13&logNo=90184513061&proxyReferer=https:%2F%2Fwww.google.com%2F

 

SYS_CONNECT_BY_PATH( , ) 는 오라클의 유용한 계층형 쿼리 함수중 하나다.

첫번째 파라미터에는 컬럼이 두번째 컬럼에는 분리자 문자열이 들어간다. 

 

사용법의 원형은 하기에서 예시로 쓴 코드보다 단순한 형태지만 되도록이면 예시와 같은 형태로 사용하는 것을 추천한다.

WHERE절의 경우, 조회하려는 테이블을 SELECT * 형태로 괄호 안에 써서 호출하는 것이 개발이나 유지보수 측면으로 볼 때 장기적으로 보면 안정적이다. (데이터가 두번씩 호출되는 경우를 미연에 방지하기 위해서다.)

 

사용 예)

 

SELECT  SYS_CONNECT_BY_PATH(부서이름컬럼, ' - ')  

   FROM (SELECT * FROM 부서테이블 WHERE YEAR = '2012' AND DELETE_DT IS NULL)
    START WITH 부서아이디 = '0'                                               
  CONNECT BY PRIOR 부서아이디 = 상위부서아이디   

 

이렇게 하면 트리형태로  

 - **회사

 - **회사 - 본사 

 - **회사 - 본사 -8888처

 - **회사 - 강원지부

 - **회사 - 강원지부 - 3333처

 

식으로 조회가 된다. 

 

구분자 를 바꾸면 - 대신 바꾼 구분자가 입력될 것이다. 

 

유용한 함수지만 제약이 많다.  

 

 

 

문제 : ORA-30004: SYS_CONNECT_BY_PATH 함수를 사용할 때 열 값의 일부로 분리자를 사용할 수 없습니다

 

이유 :  첫번째 파라미터에서 조회되는 컬럼 데이터 값이 두번째 파라미터인 '분리자'와 같은 값을 포함했다.

           예를 들어 조회한 데이터중 _ (언더바)를 포함하고 있다면 구분자에는 '_' 가 올 수 없다.

 

생각해보라 각각의 부서명마다 - 로 연결을 하려고 했는데

부서명 중에 -를 포함하고 있다면 다음과 문제가 발생해서 혼란을 줄 것이다.

 

부서중에 유지-보수 팀 이라는 부서가 있는데 함수의 분리자로 -를 사용했다면

 

-**회사-본사-유지-보수 팀 이렇게 사용자에게 혼란을 줄 것이다..

아마 이런 부분을 해결하기 위해서 제약을 걸어 두었다고 추측한다. 

 

해결 방법 : 조회되는 데이터가 포함되 다른 분리자를 사용해야 한다.

                  편법으로 띄어쓰기를 넣어서 할 수 있다. ' _ ' 이렇게

 

 

----------

SELECT
      SYS_CONNECT_BY_PATH(T1.FCLTY_NM||'('||T1.FCLTY_CD||')','>') MID_BL_CD
    , T1.MGC_CD   MGC_CD
    , T1.FCLTY_CD FCLTY_CD
FROM
    CM_FCLTY T1
START WITH T1.SECTION_SE_CD = 'J01'
CONNECT BY PRIOR T1.FCLTY_CD = T1.UPER_FCLTY_CD;

 

<<결과>>

>아산1(000201)	JS000001	201
>아산1(000201)>용화배수권역(000301)	JS000001	301
>아산1(000201)>용화배수권역(000301)>용화12323232323(000401)	JS000001	401
>아산1(000201)>용화배수권역(000301)>용화22323232(000402)	JS000001	402
>아산1(000201)>용화배수권역(000301)>용화344444(000403)	JS000001	403
>아산1(000201)>용화배수권역(000301)>용화4636346436(000404)	JS000001	404
>아산1(000201)>용화배수권역(000301)>용화5(000405)	JS000001	405
>아산1(000201)>용화배수권역(000301)>용화6(000406)	JS000001	406
>아산1(000201)>용화배수권역(000301)>용화7(000407)	JS000001	407