SQL CONNECT BY
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 CONNECT_BY 예시[편집]
1.1 CONNECT_BY_ISCYCLE 가상컬럼[편집]
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0.
You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.
1.2 CONNECT_BY_ISLEAF 가상컬럼[편집]
SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL <= 3 AND department_id = 80
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4
ORDER BY "Employee", "IsLeaf";1.3 LEVEL 가상컬럼[편집]
For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on. A root row is the highest row within an inverted tree. A child row is any nonroot row. A parent row is any row that has children. A leaf row is any row without children. Figure 3-1 shows the nodes of an inverted tree with their LEVEL values.
1.4 사용예제[편집]
SELECT *
FROM DUAL A
CROSS JOIN (SELECT LEVEL LV
FROM DUAL
CONNECT BY LEVEL <= 7) B;[결과]
DUMMY LV
----- --
X 1
X 2
X 3
X 4
X 5
X 6
X 71.4.1 원하는 기간 리턴 (일자만큼)[편집]
SELECT V_DATE
FROM (SELECT TO_CHAR(TO_DATE('20140201', 'YYYYMMDD') + LEVEL - 1, 'YYYYMMDD') V_DATE
FROM DUAL
CONNECT BY LEVEL <= TO_DATE('20140314', 'YYYYMMDD') - TO_DATE('20140201', 'YYYYMMDD')+1
);1.4.2 원하는 기간 리턴 (월별)[편집]
SELECT V_DATE
FROM (SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201401','YYYYMM'), LEVEL-1),'YYYYMM') AS V_DATE
FROM DUAL
CONNECT BY LEVEL <= MONTHS_BETWEEN(TO_DATE(TO_CHAR(SYSDATE,'YYYYMM'),'YYYYMM'),TO_DATE('201401','YYYYMM'))+1);1.4.3 현재달 날짜 구하기[편집]
SELECT TRUNC(SYSDATE, 'month') + (LEVEL - 1) AS TDAY
FROM DUAL
CONNECT BY LEVEL <= (LAST_DAY(SYSDATE) - TRUNC (SYSDATE, 'month') + 1);