connect by Clause
출처 : http://cafe.naver.com/itea11.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=236
Oracle10g부터 CONNECT BY 절에서 제공하는 CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF
기능에 대해서 알아보겠습니다.
SQLPLUS scott/tiger
SQL> SET LINESIZE 100
SQL> SET PAGESIZE 100
SQL> COL ename FORMAT A20
◈ 상관관계 쿼리 예제
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
ENAME EMPNO MGR JOB
-------------------- ---------- ---------- ---------
KING 7839 PRESIDENT
JONES 7566 7839 MANAGER
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
-- 여기서 START WITH job='PRESIDENT'부분을 START WITH EMPNO = :AS_EMPNO로 바꿔서
해당 사 번에 대한 계층구조를 구할 수도 있음..
⊙ START WITH와 CONNECT BY를 이용해 데이터를 계층적인 순서로 조회할 수 있습니다.
◈ START WITH
- 계층 질의의 루트(부모행)로 사용될 행을 지정합니다..
- 서브 쿼리를 사용할 수도 있습니다.
◈ CONNECT BY
- 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정합니다.
- 보통 PRIOR 연산자를 많이 사용합니다..
- 서브 쿼리를 사용할 수 없습니다..
◈ CONNECT BY의 실행 순서는 다음과 같습니다.
- 첫째 START WITH절
- 둘째 CONNECT BY 절
- 셋째 WHERE 절 순서로 풀리게 되어있습니다.
◈ 데이터가 많아질 경우....
- 첫째로 풀리는 START WITH job='PRESIDENT' job 칼럼에 index가 생성되어 있지 않는다면
속도를 보장할 수 없습니다.
- 그리고 둘째로 풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의 칼럼 값이 상수가
되기 때문에 MGR칼럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.
- 계층구조를 CONNECT BY, START WITH로 풀면 부분 범위 처리가 불가능하고 Desc으로
표현하기가 어렵습니다.
CONNECT_BY_ROOT
- 상관관계 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있습니다.
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
CONNECT_BY_ROOT empno "Root empno", level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
ENAME EMPNO Root empno LEVEL
-------------------- ---------- ----------- ----------
KING 7839 7839 1
JONES 7566 7839 2
SCOTT 7788 7839 3
ADAMS 7876 7839 4
FORD 7902 7839 3
SMITH 7369 7839 4
SYS_CONNECT_BY_PATH
- 상관관계 쿼리에서 현재 로우까지의 PATH 정보를 쉽게 얻어 올 수 있습니다.
SQL> COL path FORMAT A40
SQL> SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
SYS_CONNECT_BY_PATH(ename, '/') "Path"
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
ENAME EMPNO Path
-------------------- ---------- -------------------------------
KING 7839 /KING
JONES 7566 /KING/JONES
SCOTT 7788 /KING/JONES/SCOTT
ADAMS 7876 /KING/JONES/SCOTT/ADAMS
FORD 7902 /KING/JONES/FORD
SMITH 7369 /KING/JONES/FORD/SMITH
CONNECT_BY_ISLEAF
- 상관관계 쿼리에서 로우의 최하위 레벨 여부를 반환합니다.
SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno,
CONNECT_BY_ISLEAF "leaf", level
FROM emp
START WITH job='PRESIDENT'
CONNECT BY NOCYCLE PRIOR empno=mgr;
ENAME EMPNO leaf LEVEL
-------------------- ---------- ---------- ----------
KING 7839 0 1
JONES 7566 0 2
SCOTT 7788 0 3
ADAMS 7876 1 4
FORD 7902 0 3
SMITH 7369 1 4
[출처] Oracle10g에서 CONNECT BY의 새로운 기능들 |작성자 쩡 라이
|
SELECT SUBSTR (MAX (SYS_CONNECT_BY_PATH (user_nm, ',')), 2)
FROM ( SELECT ROWNUM rnum , decode(side_gb, 'C002701', (select name_k from to_user where user_id=u.user_id) , 'C002702', (select name_k from tm_expert where expert_id=user_id) ) user_nm FROM tk_journaluser u ) START WITH rnum = 1 CONNECT BY PRIOR rnum = rnum - 1 1. SYS_CONNECT_BY_PATH(column,char)은 데이터의 셀프조인에 의한 트리구조 데이테에 대해서
한row에서 표현할때 사용하는 것입니다.
간단히 말씀드리면, FIle시스템에서 파일이 위치하는 경로에 대해서 표현하는것과 같다고 생각하시면
됩니다.
2. "START WITH rnum = 1" 은 경로중에서 root에 해당하는 부분을 어디서 부터 할것인지 설정한는
조건을 말합니다.
3. "CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR a = a"은 계층을 만들기 위한 조인부분으로
경로의 다음 연결 경로를 지정하는 역할을 합니다..
즉, A -> B -> C 로 데이터의 연결을 만들기 위함입니다.
4. 이함수는 Oracle에서 사용하는 것으로, 특별한 환경 설정은 없으며, 단지 Data에 대해서 계층구조를
가지고 있다면 언제든지 사용을 할수 있습니다..
가장 대표적인 예제로는 회의의 조직도 Data가 되겠네요.. ^^
계층구조를 가지는 테이블에서 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
이런식으로 나올수 있다.
|
| 글쓴이 : 김홍선 * SYS_CONNECT_BY_PATH 함수는 연결하려는 문자열의 수가 많아질 경우, 해당 쿼리의 performance 에 문제가 생길 수 있습니다. 충분히 테스트하고 적용하시기 바랍니다. SYS_CONNECT_BY_PATH 함수 사용으로 performance 에 문제가 생길 경우, plsql로 해결하시면 되겠습니다. 예제 1) 다중 Row 결과를 단일행으로 컴마로 분리해 출력하는 방법(1) 에서, 만약 행들이 그룹번호에 따라 그룹으로 나뉘어져 있고, 각 그룹별로 하나의 행으로 컴마로 분리해 출력하려고 하면 어떻게 해야 할 것인가? 이름 그룹 ========== 홍길동 1 김길동 1 이길동 1 홍순신 2 김순신 2 이순신 2 ========== --> 그룹 이름 ====================== 1 홍길동,김길동,이길동 2 홍순신,김순신,이순신 ====================== emp.ename 컬럼, 그리고 그룹번호로 emp.deptno 컬럼을 예로 들어 쿼리를 구성해 보자. emp 테이블의 deptno, ename 컬럼은 아래와 같다. 10 CLARK 10 KING 10 MILLER 20 ADAMS 20 FORD 20 JONES 20 SCOTT 20 SMITH 30 ALLEN 30 BLAKE 30 JAMES 30 MARTIN 30 TURNER 30 WARD 쿼리와 쿼리 결과는 아래와 같다. 쿼리에서 정확히 어떤 컬럼들이 어떤 역할을 하는지 이번에도 숙지하도록 하자. SELECT deptno, SUBSTR (MAX (SYS_CONNECT_BY_PATH (ename, ',')), 2) path# FROM (SELECT ename, deptno, ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY ename) rnum FROM emp) START WITH rnum = 1 CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR deptno = deptno GROUP BY deptno 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 예제 2) 이번에는 deptno 와 같이 그룹으로 나뉘는 컬럼이 2개(deptno,job) 일 때를 고려해 보자. (group by deptno, job) 3개 이상도 비슷한 방법으로 해결한다. 즉 아래의 테이블을, 10 CLERK MILLER 10 MANAGER CLARK 10 PRESIDENT KING --------------------------- 20 ANALYST FORD 20 ANALYST SCOTT 20 CLERK ADAMS 20 CLERK SMITH 20 MANAGER JONES --------------------------- 30 CLERK JAMES 30 MANAGER BLAKE 30 SALESMAN ALLEN 30 SALESMAN MARTIN 30 SALESMAN TURNER 30 SALESMAN WARD 아래와 같이 출력이 되도록 쿼리를 만들어 보자. 10 CLERK MILLER 10 MANAGER CLARK 10 PRESIDENT KING 20 CLERK ADAMS,SMITH 20 ANALYST FORD,SCOTT 20 MANAGER JONES 30 CLERK JAMES 30 MANAGER BLAKE 30 SALESMAN ALLEN,MARTIN,TURNER,WARD 쿼리는 아래와 같다. 1번 예제의 쿼리에서 추가된 부분을 눈여겨 보자. SELECT deptno, job, SUBSTR (MAX (SYS_CONNECT_BY_PATH (ename, ',')), 2) path# FROM (SELECT deptno, job, ename, ROW_NUMBER () OVER (PARTITION BY deptno, job ORDER BY ename) rnum FROM emp) START WITH rnum = 1 CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR deptno = deptno AND PRIOR job = job GROUP BY deptno, job * 글쓴이 : 김홍선 * 위 내용을 이 곳에서 처음 보신분은 다른 곳에 게재하실 때 반드시 출처를 밝혀주시기 바랍니다. * 위 내용에 관해서 잘못된 부분이 있거나 질문이 있으신 분은 답글로 알려주시기 바랍니다. |
||
'Computer Science & Engineering > Oracle' 카테고리의 다른 글
| [SQL_오라클] 계층형 QUERY (0) | 2022.06.10 |
|---|---|
| 데이터 베이스의 용도?? (0) | 2022.06.10 |
| [SQL_오라클] SET 연산자 (0) | 2022.06.10 |
| [SQL_오라클] 서브쿼리 (0) | 2022.06.10 |
| [SQL_오라클] JOIN (0) | 2022.06.10 |
댓글