Database_68_계층 쿼리 함수_계층 질의, Level 의사열, TOP-DOWN, BOTTOM-UP, SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT

------------------------------------------------------------
    -- 버텀업 : 경로만 확인할 수 있습니다.
    -- 탑다운 : 다른 경로도 확인할 수 있습니다.
------------------------------------------------------------
-- 계층쿼리
    -- 계층 질의
        /*
        SELECT [ LEVEL ], column, expr...
        FROM table
        [ WHERE condition(s) ]
        [ START WITH condition(s) ]
        [ CONNECT BY PRIOR condition(s) ];
        */
        -- CONNECT BY : 부모 행과 자식 행 사이에 관계가 존재하는 열을 지정합니다.
        -- START WITH : 계층의 루트 행을 지정합니다.
        -- PRIOR : 계층 검색의 방향을 결정하는 필수절로 이전 행을 의미합니다..

        -- 검색 방향
            -- Top-Down
                -- 쿼리가 부모로부터 시작하여 자식 방향으로 수행됩니다.
                /*
                CONNECT BY PRIOR column1 = column2;
                */
                    /*
                        -- 탑다운 방식
                            -- START WITH의 행은 이전 행이 없습니다.
                            -- 현재 행의 이전 행은 START WITH의 행입니다.
                                -- START WITH에서 King의 사원번호 100을 추출합니다.
                                -- 현재 행에서 관리자 번호를 100을 가지는 사원 번호를 추출합니다.
                                    -- 이전 행에서의 사원번호가 현재 행에서 관리자 번호로 사용됩니다.           
                    */
            -- Bottom-Up
                -- 쿼리가 자식으로부터 부모 방향으로 수행됩니다.
                /*
                CONNECT BY PRIOR column2 = column1;
                */
                
    -- Top-Down
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id=100
CONNECT BY PRIOR employee_id=manager_id;
    /*
    EM_ID     LAST_NAME     JOB_ID           M_ID
    ---------------------------------------------------
    100            King           AD_PRES        ( null ) 
    101            Kochhar      AD_VP            100        : 100 번을 이전 행으로 가진 하위 행
    200            Whalen       AD_ASST         101
    205            Higgins       AC_MGR         101
    206            Gietz          AC_ACCOUNT  205
    102            De Haan     AD_VP            100        : 100 번을 이전 행으로 가진 하위 행
    103            Hunold       IT_PROG         102
    104            Ernst          IT_PROG         103
    107            Lorentz       IT_PROG         103
    114            Raphealy     AC_ACCOUNT  100        : 100 번을 이전 행으로 가진 하위 행
    124            Mourgos     ST_MAN         100        : 100 번을 이전 행으로 가진 하위 행
    141            Rajs           ST_CLERK        124
    142            Davies        ST_CLERK        124
    143            Matos         ST_CLERK        124
    144            Vargas        ST_CLERK        124
    149            Zlotkey       SA_MAN         100        : 100 번을 이전 행으로 가진 하위 행
    174            Abel           SA_REP           149
    176            Taylor         SA_REP           149
    178            Grant         SA_REP           149
    201            Hartstein     MK_MAN        100        : 100 번을 이전 행으로 가진 하위 행
    202            Fay            MK_REP          201
    214            Kim           AC_ACCOUNT   100        : 100 번을 이전 행으로 가진 하위 행
    215            Lee           AC_ACCOUNT   100        : 100 번을 이전 행으로 가진 하위 행
        -- Manager_id가 100이 나올 때마다 새로운 트리가 생성되는 것입니다.
    */

    -- Bottom-Up
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id=100
CONNECT BY employee_id= PRIOR manager_id;
        -- King의 부모 행이 하나도 없으므로 조회되지 않습니다.

    -- Bottom-Up 방식으로 끝 가지인 206번 사원을 기준으로 조회합니다.
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id=206
CONNECT BY employee_id= PRIOR manager_id;
        -- 자신의 바로 위의 부모 행의 값을 순차적으로 조회합니다.

    -- LEVEL 의사열의 사용
        -- Top-Down 방식에 LEVEL 의사열을 함께 작성합니다.


SELECT employee_id, last_name, job_id, manager_id, level
FROM employees
START WITH employee_id=100
CONNECT BY PRIOR employee_id=manager_id;
        -- LEVEL 값은 동적입니다.

SELECT employee_id, last_name, job_id, manager_id, level
FROM employees
START WITH employee_id=101
CONNECT BY PRIOR employee_id=manager_id;

    -- LPAD, LENGTH 함수를 사용하여 계층 보고서를 작성
        -- 가장 높은 레벨부터 다음레벨을 들여쓰기하여 작성합니다.
SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS 
org_chart
FROM employees 
START WITH last_name='King' 
CONNECT BY PRIOR employee_id=manager_id; 

    
    -- 계층 쿼리 함수
        -- SYS_CONNECT_BY_PATH
            -- 루트 데이터로부터 현재 전개할 데이터까지 경로를 표시합니다.
        -- CONNECT_BY_ROOT
            -- 현재 전개할 데이터의 루트 데이터를 표시합니다.
SELECT CONNECT_BY_ROOT(employee_id) AS root, employee_id, last_name, SYS_CONNECT_BY_PATH(employee_id, '/') AS path
FROM employees
START WITH employee_id=100
CONNECT BY PRIOR employee_id=manager_id;

        -- 계층 쿼리 WHERE 절 사용
SELECT employee_id, last_name, level
FROM employees
WHERE employee_id<>101
START WITH employee_id=100
CONNECT BY PRIOR employee_id=manager_id;
        -- 가지가 부자연스럽게 생략됩니다.

-- 가지를 먼저 실행한 후, 101번 사원을 제거합니다.
SELECT employee_id, last_name, level
FROM employees
START WITH employee_id=100
CONNECT BY PRIOR employee_id=manager_id
AND employee_id<>101;