계층구조- SYS_CONNECT_BY_PATH 함수

오라클를 이용한 계층구조

계층구조- SYS_CONNECT_BY_PATH 함수 db  2006/03/28 14:59 http://blog.naver.com/peterppan/50002894715

계층구조를 가지는 테이블에서 Navigation Path 를 만들기 위하여
SYS_CONNECT_BY_PATH 함수를 사용할 수 있습니다.

순방향으로 전개할 때에는 이 path 가 root 에서 leaf 쪽으로 계층적으로
구성되어 반환되므로 상관이 없습니다.

예1) 순방향 전개시

SELECT EMPNO, LPAD(' ', 2*LEVEL-1)||SYS_CONNECT_BY_PATH(ENAME,'/') PATH
  FROM EMP
 START WITH JOB='PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR

EMPNO    PATH
-----    ------------------------------
7839     /KING
7566       /KING/JONES
7788         /KING/JONES/SCOTT
7876           /KING/JONES/SCOTT/ADAMS
7902         /KING/JONES/FORD
7369           /KING/JONES/FORD/SMITH
7698       /KING/BLAKE
7499         /KING/BLAKE/ALLEN
7521         /KING/BLAKE/WARD
7654         /KING/BLAKE/MARTIN
7844         /KING/BLAKE/TURNER
7900         /KING/BLAKE/JAMES
7782       /KING/CLARK
7934         /KING/CLARK/MILLER

역방향으로 전개할 때에는 이 path가 leaf 에서 root 쪽으로 구성됩니다.
더 정확하게 말하자면 start with 절에 해당하는 node 의 level 이 1부터 시작하여
순방향이던 역방향이던 전개하면서 level 이 1씩 증가므로 level 순으로
path를 만들어 내는 것입니다.

예2) 역방향 전개

SELECT EMPNO, LPAD(' ', 2*LEVEL-1)||SYS_CONNECT_BY_PATH(ENAME,'/') PATH
  FROM EMP
 START WITH EMPNO = 7876
CONNECT BY  EMPNO = PRIOR MGR

EMPNO    PATH
-----    ------------------------------
7876     /ADAMS
7788       /ADAMS/SCOTT
7566         /ADAMS/SCOTT/JONES
7839           /ADAMS/SCOTT/JONES/KING

따라서 역방향으로 전개할 때에는 path가 거꾸로 나오므로
개발자들은 다음과 같이 2번에 걸친 전개를 하여 값을 구하곤 합니다.
1) 역방향 전개로 원하는 결과집합 생성하여 inline view로 묶고
2) 해당 집합에서 순방향 전개를 하여 sys_connect_by_path 함수로 올바른 path 생성

위의 제약사항을 reverse 함수를 2번 사용하는 것으로 해결해 보았습니다.

*********************************************************************************
* 주의 : reverse 함수는 내부적으로만 사용되는 undocumented 함수입니다.
*        support 되지 않으므로 사용에 대한 책임은 전적으로 사용자에게 있습니다.
*********************************************************************************

reverse 함수는 byte 단위로 앞뒤를 변경하므로 character 값들만 변경의 의미가 있습니다.

예3) reverse 함수 예제

SQL> select reverse('123'), reverse('한글'), reverse(sysdate), reverse(12) from dual;

REV REVE REVERSE(S REVERSE(12)
--- ---- --------- -----------
321 旁饑 14-AUG-50  -(.000E+98

하지만 reverse를 한번 더 적용하면 ~(~T) = T 와 같이 원래 값으로 변경됩니다.

예4) reverse 함수 2번 적용 예제

SQL> select reverse(reverse('123')), reverse(reverse('한글')), reverse(reverse(sysdate)), reverse(reverse(12)) from dual;

REV REVE REVERSE(R REVERSE(REVERSE(12))
--- ---- --------- --------------------
123 한글 11-AUG-05                   12

따라서 reverse를 먼저 적용하여 path를 만들고 전체적으로 한번 더 뒤집은 후 '/' 등의 
delimeter 처리만 약간 해주면 우리가 원하는 path를 만들어 낼 수 있습니다.

예5) 역방향 전개시 reverse 1회 적용

SELECT EMPNO, 
       SYS_CONNECT_BY_PATH(REVERSE(ENAME),'/') PATH
  FROM EMP
 START WITH EMPNO = 7876
CONNECT BY  EMPNO = PRIOR MGR

EMPNO    PATH
-----    ------------------------------
7876    /SMADA
7788    /SMADA/TTOCS
7566    /SMADA/TTOCS/SENOJ
7839    /SMADA/TTOCS/SENOJ/GNIK

예6) 역방향 전개 및 reverse 2회 적용

SELECT EMPNO, 
       LPAD(' ', 2*LEVEL-1)||'/'||
       RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(ENAME),'/')),'/') PATH
  FROM EMP
 START WITH EMPNO = 7876
CONNECT BY  EMPNO = PRIOR MGR

EMPNO    PATH
-----    ------------------------------
7876     /ADAMS
7788       /SCOTT/ADAMS
7566         /JONES/SCOTT/ADAMS
7839           /KING/JONES/SCOTT/ADAMS

 

============================================

 

EX) SELECT MENU_ID, SEQ, PRT_MENU_ID,

LEVEL, LPAD(' ', LEVEL*7) || MENU_NM, SYS_CONNECT_BY_PATH(MENU_NM, '')
FROM TABLE

CONNECT BY PRIOR MENU_ID = PRT_MENU_ID
START WITH PRT_MENU_ID = ''

ORDER SIBLINGS BY SEQ

 

ORDER SIBLINGS BY 컬럼  <-- CONNECT BY에서 정렬을 시켜준다.

 

SYS_CONNECT_BY_PATH(MENU_NM, '') <--  상위메뉴에서 부터 구분자가 인 값을 가져올수 있다.

 

메뉴1

메뉴1하위1

메뉴1하위2

이런식으로 나올수 있다.

출처 : http://rahxephon.tistory.com/414