오라클 connect by 예제

connecty by 사용 전

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, 'B' AS P_CODE , '이동수' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'F' AS CODE, 'C' AS P_CODE , '이진수' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'G' AS CODE, 'C' AS P_CODE , '조둔서' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'H' AS CODE, 'B' AS P_CODE , '개뿔딱' AS TEAM_NAME  FROM DUAL
)
SELECT A.*
FROM   A;

 

connecty by 사용 후
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, 'B' AS P_CODE , '이동수' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'F' AS CODE, 'C' AS P_CODE , '이진수' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'G' AS CODE, 'C' AS P_CODE , '조둔서' AS TEAM_NAME  FROM DUAL
    UNION ALL
    SELECT 'H' AS CODE, 'B' AS P_CODE , '개뿔딱' AS TEAM_NAME  FROM DUAL
)
SELECT CODE,P_CODE,LPAD(' ',  8 * (LEVEL-1))||TEAM_NAME
FROM   A
START WITH P_CODE IS NULL
CONNECT BY  PRIOR CODE = P_CODE;