12. 계층형쿼리 (Hierarchical Query) -연습문제다시풀어보자.

12. 계층형쿼리 (Hierarchical Query) : 화면에 출력할 때 상하 관계를 표시하는 것.

 

: 사장실은 level1 ->1*6바이트로 찍되 빈자리 있으면 *찍어야하는데, '사장실'이 6byte꽉차니까 *없음.

 

1. 주요 문법

  > SELECT LPAD(dname,LEVEL*6,'*') 부서명
  2     FROM   dept2
  3  CONNECT BY PRIOR dcode = pdept      : 서로 어떻게 연결되어야 하는지 조건을 지정.

  4    START   WITH   dcode=0001 ;

 

 - PRIOR키워드는 해당키워드가 설정되어있는 칼럼에서 바로 이전의 데이터값 찾는데 사용.

    ex. dcode = PRIOR pdept : 상위부서(pdept) 출력.

      (0001번 부서부터 '이전'부서들의 pdept코드 찾는건데, 0001부서가 최상위 부서이므로 '사장실'만 나옴.)

 

* 계층형 쿼리가 수행되는 순서

 

1. START WITH 절에 시작조건을 찾습니다 2. CONNECT BY 절에 연결 조건을 찾습니다 3. WHERE 절의 조건을 검색합니다   -> 대량의 데이터가 있을 경우에 시간이 오래걸리므로, START WITH절과 CONNECT BY절, WHERE절의 칼럼에 반드시 인덱스가 적절하게 설정되어 있어야 한다.

                                                                                                                                                                                                                                                                                         

- 계층형쿼리에서 결과를 정렬하고 싶을 때, order siblings by

 

2. CONNECT_BY_ISLEAF ( ) 함수

일반적으로 계층쿼리는 시작지점부터 중간내용과 마무리지점까지 정보가 다 나오지만, 중간지점의 데이터는 안보고 가장 마지막지점의 데이터만 보고 싶을 때 쓰는 함수이다. (예. 특정 사원의 상사내역을 보고 싶을 때, 가장 말단 사원부터 출발해서, 중간 과정의 내용은 다 보지 않고 가장 최종상사만 보고싶은 경우)

->중간내용을 요약해주는 SYS_CONNECT_BY_PATH() 함수를 사용하면 편리하다.

 

1) connect_by_isleaf 함수를 사용하지 않은경우.          2) connect_by_isleaf = 0          3) connect_by_isleaf = 1

 

- 특정 사원과 상위 상사 조회.

SCOTT>SELECT name "부하직원명", CONNECT_BY_ROOT   name "상사명" ,    2              SYS_CONNECT_BY_PATH(name ,'->') "상사  -> 부하"    3  FROM emp2    4  WHERE    LEVEL>1    5  AND empno='20000101'    6  CONNECT BY PRIOR empno=pempno ;   부하직원명        상사명           상사  -> 부하 ------------ ------------- ------------------------------------------ 이태백            노정호            ->노정호->이윤나->이태백 이태백            이윤나            ->이윤나->이태백 이태백            나사장            ->나사장->노정호->이윤나->이태백

 

- 위 결과 중에서 최상위 상사만 보고싶은 경우 : LEVEL값 조정.

SCOTT>SELECT name "부하직원명", CONNECT_BY_ROOT   name "상사명" ,    2          SYS_CONNECT_BY_PATH(name ,'->') "상사  -> 부하"    3  FROM emp2    4  WHERE  LEVEL=4    5  AND empno='20000101'    6  CONNECT BY PRIOR empno=pempno ;

 

 

 

---

 

 

 

 

[시행착오.1] 결과와 level이 다름.

[시행착오.2] 결과와 level이 다름.  - 연결연산자와 다른칼럼들 내용을 lpad안에 모두 넣음.

[시행착오.3] 결과와 level이 다름.  - 부서의 상위부서를 찾는게아니라, 사원의 상위레벨을 찾는거니까, connect by prior을 emp2테이블에서 설정해줌.  - 연결연산자와 다른칼럼들은 lpad 밖으로 다시 꺼냄.

[시행착오.4] 결과와 level은 같지만, 순서가 다름. - connect by prior e.empno=e.pempno  - 연결연산자와 다른칼럼들을 lpad안에 모두 넣어줌.

[정답]  - lpad 안에 연결연산자와 다른칼럼들을 모두 넣고, connect by prior에 emp2테이블로 설정해줌.  - 정렬은 order by가 아닌, order siblings by 로 정렬해야 한다.

 

 

 

 

 

 

 

 

 

 

답)

  1  select lpad(e.name||' '||d.dname||' '||nvl(e.position,'사원'),level*19,'-')
  2  "이름과직급"
  3  from emp2 e, dept2 d
  4  where e.deptno=d.dcode
  5  start with e.empno=20000334
  6* connect by e.empno= prior e.pempno

 

 

답)

 select name"사원명", prior name "상사이름"
  2  from emp2
  3  where pempno is null
  4  connect by prior empno=pempno;

 

 답)

   1  select e.empno, e.name||' '||d.dname||' ' ||nvl(e.position,'사원')"사원명",
  2  (select count(*)
  3  from emp2
  4  start with empno=e.empno
  5  connect by prior empno=pempno)-1 "부하직원수"
  6  from emp2 e, dept2 d
  7  where e.deptno=d.dcode
  8* order by 3 desc

 

 

답)

select lpad(e.name||' '||d.dname||' '||nvl(e.position,'사원'),level*18,'-')
"이름과직급", sys_connect_by_path(e.name,'-')"전체경로"
from emp2 e, dept2 d
where e.deptno=d.dcode
start with e.empno=19966102
connect by prior e.empno=  e.pempno;