"SQL CONNECT BY"의 두 판 사이의 차이
DB CAFE
21번째 줄: | 21번째 줄: | ||
https://docs.oracle.com/database/121/SQLRF/img/sqlrf001.gif | https://docs.oracle.com/database/121/SQLRF/img/sqlrf001.gif | ||
+ | |||
+ | == 사용예제 == | ||
+ | <source lang=sql> | ||
+ | SELECT * | ||
+ | FROM DUAL A | ||
+ | CROSS JOIN (SELECT LEVEL LV | ||
+ | FROM DUAL | ||
+ | CONNECT BY LEVEL <= 7) B; | ||
+ | </source> | ||
+ | <source lang=sql> | ||
+ | [결과] | ||
+ | DUMMY LV | ||
+ | ----- -- | ||
+ | X 1 | ||
+ | X 2 | ||
+ | X 3 | ||
+ | X 4 | ||
+ | X 5 | ||
+ | X 6 | ||
+ | X 7 | ||
+ | </source> | ||
+ | |||
+ | === 원하는 기간 리턴 (일자만큼) === | ||
+ | <source lang=sql> | ||
+ | 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 | ||
+ | ); | ||
+ | </source> | ||
+ | |||
+ | === 원하는 기간 리턴 (월별) === | ||
+ | <source lang=sql> | ||
+ | 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); | ||
+ | </source> | ||
+ | |||
+ | === 현재달 날짜 구하기 === | ||
+ | <source lang=sql> | ||
+ | SELECT TRUNC(SYSDATE, 'month') + (LEVEL - 1) AS TDAY | ||
+ | FROM DUAL | ||
+ | CONNECT BY LEVEL <= (LAST_DAY(SYSDATE) - TRUNC (SYSDATE, 'month') + 1); | ||
+ | </source> | ||
+ | |||
+ | |||
[[Category:oracle]] | [[Category:oracle]] |
2020년 3월 20일 (금) 18:11 판
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 7
1.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);