START WITH : 트리 구조의 최상위 행을 지정합니다.
CONNECT BY : 부모, 자식의 관계를 지정합니다.
PRIOR : CONNECT BY 절에 사용되며 PRIOR에 지정된 컬럼이 맞은편 컬럼을 찾아갑니다.
CONNECT BY PRIOR 자식 컬럼 = 부모 컬럼 : 부모 → 자식 순방향 전개(TOP DOWN 형태 출력)
CONNECT BY PRIOR 부모 컬럼 = 자식 컬럼 : 자식 → 부모 역방향 전개(BOTTOM UP 형태 출력)
ORDER SIBLINGS : 계층형 쿼리에서 정렬을 수행합니다.
□ 계층형 질의에서 사용되는 가상칼럼
LEVEL : 최상위 루트 데이터가 1, 하위로 갈수록 1씩 증가
CONNECT_BY_ISLEAF : 최하단 리프 데이터이면 1, 아니면 0
CONNECT_BY_ISCYCLE : 해당 데이터가 조상으로써 존재하면 1, 아니면 0, 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 의미한다
select r.*, CONNECT_BY_ISLEAF AS LEAF, level as lv
from (
select a.companycd, b.teamcd as id,
b.teamcd, a.u_linecd, GET_TEAM_NAME(a.companycd, b.teamcd,'KO') as teamnm,
b.LISTORDER as sort_no, 'T' as user_type
from cm0065t a, xm605 b
where a.companycd = b.companycd
and a.LINECD = b.TEAMCD
and a.companycd = '00'
and (a.bymd is null or a.bymd > sysdate)
union all
select t1.companycd, t1.id,
t1.teamcd, t1.u_linecd, t1.teamnm,
rownum as sort_no, 'U' as user_type
from (
select t2.*
from (
select a.companycd, a.teamcd||'!'||a.xuser as id,
a.teamcd as u_linecd, a.xusernm as teamnm,
a.RANKID, a.xuser as teamcd
from c_0010 a
where a.useyn = 'Y'
and a.gubun = '0'
and a.companycd = '00'
) t2
order by rankid
) t1
) r
start with r.u_linecd = 'ZZ'
connect by nocycle prior r.id = r.u_linecd
order siblings by sort_no
START WITH (컬럼) (조건)
CONNECT BY PRIOR 연결컬럼(찾을코드) = 기준컬럼(부모코드)
ORDER SIBLINGS BY 정렬순위컬럼
SELECT R2.* ,LEVEL AS levelNo
FROM (
SELECT DISTINCT R1.ID_, R1.UID_, R1.UID__, R1.PGM_ORDER, R1.URL, R1.DESCR as pnm, R1.LEAF
FROM (
SELECT A.ID_, NVL(A.UID_,-1) AS UID_, A.UID_ AS UID__, A.PGM_ORDER, A.URL, B.DESCR, CONNECT_BY_ISLEAF AS LEAF
FROM C_0042 A, C_0040 B
WHERE A.ID_ = B.ID_
AND A.COMPANYCD = '00'
AND A.USEYN = 'Y'
AND B.USEYN = 'Y'
START WITH (A.COMPANYCD, A.ID_, A.UID_) IN (
SELECT DISTINCT D.COMPANYCD, D.ID_, D.UID_
FROM PLM_C_0020 A, PLM_C_0031 B, C_0040 C, C_0042 D
WHERE A.SYSTEMCD = B.SYSTEMCD
AND A.GRADE = B.GRADE
AND B.SYSTEMCD = C.SYSTEMCD
AND B.PGMID = C.PGMID
AND A.COMPANYCD = D.COMPANYCD
AND C.ID_ = D.ID_
AND A.COMPANYCD = '00'
AND A.XUSER = 'PREMIER'
AND A.USEYN = 'Y'
AND C.USEYN = 'Y'
AND D.USEYN = 'Y'
)
CONNECT BY NOCYCLE PRIOR A.UID_ = A.ID_ AND A.COMPANYCD = '00' AND A.USEYN = 'Y'
ORDER SIBLINGS BY A.PGM_ORDER DESC
) R1
)R2
START WITH R2.UID_ = -1 AND R2.ID_ IN (897, 898, 899, 900, 35, 36, 1014, 283, 16)
CONNECT BY NOCYCLE PRIOR R2.ID_ = R2.UID_
ORDER SIBLINGS BY R2.PGM_ORDER