오라클,SQL, 계층형 쿼리(Hierarchical Query), START WITH , CONNECT BY , ORDER SIBLINGS BY

오라클,SQL, 계층형 쿼리(Hierarchical Query), START WITH , CONNECT BY , ORDER SIBLINGS BY

​

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=626

 

오라클,SQL, 계층형 쿼리(Hierarchical Query), START WITH , CONNECT BY , ORDER SIBLINGS BY

오라클,SQL, 계층형 쿼리(Hierarchical Query), START WITH , CONNECT BY , ORDER SIBLINGS BY6.6 계층형 쿼리(Hierarchical Query)n EMP 테이블의 MGR 칼럼은 외래키로 자기자신 테이블 EMP의 EMPNO 값을 참조하는 관리자를 뜻하는 칼럼이다. 이렇게 계층관계 칼럼이 있는 경우 계층구조…

ojc.asia

6.6 계층형 쿼리(Hierarchical Query)

n EMP 테이블의 MGR 칼럼은 외래키로 자기자신 테이블 EMP의 EMPNO 값을 참조하는 관리자를 뜻하는 칼럼이다. 이렇게 계층관계 칼럼이 있는 경우 계층구조를 이용하여 데이터를 추출 할 수 있는 계층적 질의문을 사용하면 된다.

[형식]

SELECT 칼럼 FROM 테이블 WHERE 조건 START WITH 조건 CONNECT BY [PRIOR] [NOCYCLE] [ORDER SIBLINGS BY 칼럼, 칼럼]

START WITH : 시작 데이터를 지정한다.

CONNECT BY : 계층구조에서 다음에 연결될 데이터를 지정한다.

PRIOR : CONNECT BY 절에 이용되며 현재 읽은 칼럼을 지정한다.

PRIOR 자식 = 부모 (TOP DOWN 형태 출력)

PRIOR 부모 = 자식 (BOTTOM UP 형태 출력)

NOCYCLE : 데이터를 펼치면서 이미 나타났던 데이터가 다시 나타나는 경우 CYCLE이 형성되

었다고 한다. 이때 오류가 발생하는데 NOCYCLE을 추가하면 사이클이 발생한

이후의 데이터를 출력하지 않는다.

ORDER SIBLINGS BY : 동일한 LEVEL인 경우 노드를 형제 노드라고 하고 형제노드들의

정렬순서를 지정한다.

n 계층형 질의에서 사용되는 가상칼럼

LEVEL : 최상위 루트 데이터가 1, 하위로 갈수록 1씩 증가

CONNECT_BY_ISLEAF : 최하단 리프데이터이면 1, 아니면 0

CONNECT_BY_ISCYCLE : 해당 데이터가 조상으로써 존재하면 1, 아니면 0, 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 의미한다.

n 계층형 쿼리에서 사용가능한 함수

SYS_CONNECT_BY_PATH (칼럼, 경로분리자): 루트데이터에서부터 전개할 데이터 까지의 경로를 표시한다.

CONNECT_BY_ROOT 칼럼 : 현재 전개할 데이터의 루트 데이터를 표시한다.

SELECT EMPNO, ENAME, CONNECT_BY_ROOT ENAME, SYS_CONNECT_BY_PATH(ENAME, '/') 경로 FROM EMP START WITH MGR IS NULL --시작데이터를 지정 CONNECT BY PRIOR EMPNO = MGR; --상위(부모)의 EMPNO를 MGR값으로 가지는 데이터가 다음 연결될 데이터이다. 이 연결고리를 지정 7839 KING KING /KING 7566 JONES KING /KING/JONES 7788 SCOTT KING /KING/JONES/SCOTT …… 7844 TURNER KING /KING/BLAKE/TURNER 7900 JAMES KING /KING/BLAKE/JAMES 7782 CLARK KING /KING/CLARK 7934 MILLER KING /KING/CLARK/MILLER -- EMP 테이블에서 KING부터 시작하여 아래와 같은 결과를 출력하는 쿼리를 작성하세요. -- 트리구조에서 위에 있는 직원이 관리자임을 뜻한 -- (empno, mgr 칼럼을 적절히 이용하세요) KING 5000 JONES 2975 20 SCOTT 3000 20 ADAMS 1100 20 FORD 3000 20 SMITH 800 20 BLAKE 2850 30 ALLEN 1600 30 WARD 1250 30 MARTIN 1250 30 TURNER 1500 30 JAMES 950 30 CLARK 2450 10 MILLER 1300 10 select lpad(' ',(level-1)*2) || ename, sal, deptno from emp start with ename = 'KING' connect by prior empno = mgr create table Emp2 ( ename varchar2(20), mname varchar2(20), job varchar2(20) ); insert into Emp2 values ('김길동','가길동','SALESMAN'); insert into Emp2 values ('남길동','하길동','ANALYST'); insert into Emp2 values ('박길동','가길동','ANALYST'); insert into Emp2 values ('가길동','남길동','CLERK'); insert into Emp2 values ('하길동',NULL,'SALESMAN'); commit; select * from emp2; ENAME MNAME JOB -------------------- -------------------- -------------------- 김길동 가길동 SALESMAN 남길동 하길동 ANALYST 박길동 가길동 ANALYST 가길동 남길동 CLERK 하길동 SALESMAN -- 최고 관리자 하길동부터 TOP DOWN형태로 출력 select LPAD(' ',(level-1)*2,' ')||ename, job from emp2 start with ename = '하길동' connect by prior ename = mname -- 상위(관리자) ename을 mname으로 가지는레코드 하길동 SALESMAN 남길동 ANALYST 가길동 CLERK 김길동 SALESMAN 박길동 ANALYST -- 최하위 박길동부터 최상위 하길동까지... 김길동은 안나온다. select LPAD(' ',(level-1)*2,' ')||ename, job from emp2 start with ename = '박길동' connect by prior mname = ename 박길동 ANALYST 가길동 CLERK 남길동 ANALYST 하길동 SALESMAN -- 계층쿼리에서 가지제거(노드제거) where절로 제한하면 그 노드만 빠지지만 connect by 절에서 제거하면 그 이하 모든 노드가 제거 * where절에서 가길동만 제거 select LPAD(' ',(level-1)*2,' ')||ename, job from emp2 where ename != '가길동' start with ename = '하길동' connect by prior ename = mname 하길동 SALESMAN 남길동 ANALYST 김길동 SALESMAN 박길동 ANALYST * connect by 절에서 제거, 가길동 이하 모든 노드가 제거된다. select LPAD(' ',(level-1)*2,' ')||ename, job from emp2 start with ename = '하길동' connect by prior ename = mname and ename != '가길동' 하길동 SALESMAN 남길동 ANALYST -- EMP 테이블에서 'SMITH' 사원에 대해 입사이후 현재까지의working day수(토/일요일, 공휴일 제외)를 출력하세요. 토/일요일 이외의 휴일은 4일로 offday라는 테이블에 다음과 같이 존재한다고 하자. create table offday ( ilja varchar2(8), cmt varchar2(50) ) insert into offday values ('20100301','삼일절'); insert into offday values ('20120301','삼일절'); insert into offday values ('20121225','성탄절'); insert into offday values ('20130505','어린이날'); commit; SELECT COUNT(*) AS WORKINGDAY FROM( SELECT LEVEL, TO_CHAR((HIREDATE + LEVEL), 'YYYYMMDD') PLUSDATE, TO_CHAR((HIREDATE + LEVEL), 'DAY') PLUSDAY FROM ( SELECT HIREDATE, SYSDATE AS SYSD FROM emp WHERE ename = 'SMITH' ) CONNECT BY LEVEL < SYSD – HIREDATE + 1 ORDER BY LEVEL DESC ) WHERE PLUSDATE NOT IN(SELECT ILJA FROM OFFDAY) AND PLUSDAY NOT IN ('토요일', '일요일')