======= **** 계층형 쿼리 **** =======

       계층형 쿼리는 Spring 프레임워크 시간에 답변형 게시판에서 사용한다.
       또한 전자결재 에서도 사용된다.

 

    -- 결재라인 만들기
    -- 104 ==> 103 ==> 102 ==> 100 ==> NULL
    --  1       2       3       4

  

 

 -- 104번 사원의 직속결재권자 찾기

    select level 
         , employee_id as 사원번호
         , first_name || ' ' || last_name as 사원명
         , manager_id as 직속결재권자
    from employees
    start with employee_id = 104 
    connect by prior manager_id = employee_id;

 

    ----- ===== ***** !!!! prior 다음에 나오는 manager_id 컬럼은 start with 되는 행의 manager_id 컬럼의 값이다. !!!! ***** ===== -----
    
    /*
        start with employee_id = 104    ==>   start with employee_id = 103   ==>   start with employee_id = 102
        connect by prior 103 = employee_id;  ==>   connect by prior 102 = employee_id;   ==>   connect by prior 100 = employee_id;
                                   104                                        103                                         102
    */

 

 

---

-- 위치 바꾸기 --
    
    -- 100번 사원부터 아래로

   select level 
         , employee_id as 사원번호
         , first_name || ' ' || last_name as 사원명
         , manager_id as 직속결재권자
    from employees
    start with employee_id = 100
    connect by prior employee_id = manager_id
    order by level;
    /*
        start with employee_id = 100   =>   start with employee_id = 101   =>   start with employee_id = 108   =>   start with employee_id = 109   110   111   112   113 (말단직원)
        connect by prior 101 = manager_id;   =>   connect by prior 108 = manager_id;   =>   connect by prior 109 = manager_id;
                                  100                                        101                                      110   111   112   113 (없음)
    */