[ORACLE] CONNECT BY PRIOR 한방정리

WITH A AS(
    SELECT 'A' AS CODE, '' AS P_CODE , '인사팀' AS TEAM_NAME FROM DUAL
    UNION ALL
    SELECT 'B' AS CODE, '' AS P_CODE , '관리팀' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'C' AS CODE, '' AS P_CODE , '구매팀' AS TEAM_NAME  FROM DUAL
    UNION ALL
 
    SELECT 'D' AS CODE, 'A' AS P_CODE , '인사파트' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'E' AS CODE, 'A' AS P_CODE , '총무파트' AS TEAM_NAME  FROM DUAL
    UNION ALL
 
    SELECT 'F' AS CODE, 'B' AS P_CODE , '관리파트' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'G' AS CODE, 'B' AS P_CODE , '경리파트' AS TEAM_NAME  FROM DUAL
    UNION ALL
 
    SELECT 'J' AS CODE, 'D' AS P_CODE , '천과장' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'K' AS CODE, 'D' AS P_CODE , '이대리' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'L' AS CODE, 'D' AS P_CODE , '윤주임' AS TEAM_NAME  FROM DUAL
    UNION ALL
 
    SELECT 'M' AS CODE, 'E' AS P_CODE , '김과장' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'N' AS CODE, 'E' AS P_CODE , '홍대리' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'O' AS CODE, 'E' AS P_CODE , '안선임' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'P' AS CODE, 'E' AS P_CODE , '문사원' AS TEAM_NAME  FROM DUAL
    UNION ALL
     
    SELECT 'Q' AS CODE, 'F' AS P_CODE , '오과장' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'R' AS CODE, 'F' AS P_CODE , '도사원' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'S' AS CODE, 'G' AS P_CODE , '서부장' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'T' AS CODE, 'G' AS P_CODE , '허대리' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'U' AS CODE, 'G' AS P_CODE , '전주임' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'V' AS CODE, 'C' AS P_CODE , '고상무' AS TEAM_NAME  FROM DUAL
)
SELECT LEVEL, CODE,P_CODE,LPAD(' ',  8 * (LEVEL-1))||TEAM_NAME
FROM   A
START WITH P_CODE IS NULL
CONNECT BY  PRIOR CODE = P_CODE
;



***** ORDER BY & ORDER SIBLINGS BY
 - ORDER BY : 통으로 정렬
 - ORDER SIBLINGS BY : 계층별로 정렬