1. 지정된 임의의 기간 출력
SELECT TO_CHAR(TO_DATE(:P_SDATE, 'YYYYMMDD') + LEVEL - 1, 'YYYYMMDD') AS DD
FROM DUAL
CONNECT BY LEVEL <= TO_DATE(:P_EDATE, 'YYYYMMDD') - TO_DATE(:P_SDATE, 'YYYYMMDD') + 1;
2. 24 시간
select to_char(level - 1, 'fm00') || '시'
from dual
connect by level <= 24;
3. Dumy 날짜 테이블 생성
CREATE TABLE COMT_YMD
AS
SELECT TO_CHAR(DAY, 'YYYYMMDD') AS YMD
-- , DENSE_RANK() OVER (PARTITION BY TO_CHAR(DAY, 'YYYYMM') ORDER BY DECODE(TO_CHAR(DAY, 'D'), 1, DAY, LEAST(NEXT_DAY(DAY, 1), LAST_DAY(DAY)))) AS W
, DENSE_RANK() OVER (PARTITION BY TO_CHAR(DAY, 'YYYYMM') ORDER BY LEAST(NEXT_DAY(DAY, 2), LAST_DAY(DAY))) AS W
, TO_CHAR(DAY, 'DD') AS DD
, TO_CHAR(DAY, 'D') AS D
, TO_CHAR(DAY, 'DY', 'NLS_DATE_LANGUAGE=KOREAN') AS DY_KOR
, TO_CHAR(DAY, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') AS DY_ENG
, DECODE(TO_CHAR(DAY, 'D'), '1', '星期天', '2', '星期一', '3', '星期二', '4', '星期三', '5', '星期四', '6', '星期五', '7', '星期六') AS DY_CHN
, DECODE(TO_CHAR(DAY, 'D'), '1', 'Y', '7', 'Y') AS HOLIDAY
, '99991231' AS YMD_LUNAR
FROM (SELECT TO_DATE('19000101', 'YYYYMMDD') + LEVEL - 1 AS DAY
FROM DUAL
CONNECT BY LEVEL <= TO_DATE('22001231', 'YYYYMMDD') - TO_DATE('19000101', 'YYYYMMDD') + 1);* W : 원래 월의 주차 의미로 oracle에서는 1~7 1주, 8~14 2주...이런 식으로 하나 사용자의 요구로 월~일 까지를 한주로 간주 한다하여 위의 쿼리 처럼 하였음.
4. Window 함수
SELECT LEVEL
, SUM(ROWNUM) OVER ()
, SUM(ROWNUM) OVER (ORDER BY ROWNUM)
, COUNT(*) OVER ()
FROM DUAL
CONNECT BY LEVEL <= 10;
5. 달력
SELECT MIN(DECODE(D, 1, DD)) AS SUN
, MIN(DECODE(D, 2, DD)) AS MON
, MIN(DECODE(D, 3, DD)) AS TUE
, MIN(DECODE(D, 4, DD)) AS WED
, MIN(DECODE(D, 5, DD)) AS THU
, MIN(DECODE(D, 6, DD)) AS FRI
, MIN(DECODE(D, 7, DD)) AS SAT
FROM ( SELECT DAY
, DD
, DENSE_RANK() OVER (ORDER BY NEXT_DAY(DAY, 1)) AS W
, TO_CHAR(DAY, 'D') AS D
FROM ( SELECT LEVEL AS DD
, TO_DATE('201604' || '01', 'YYYYMMDD') - 1 + LEVEL AS DAY
FROM DUAL
CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(TO_DATE('201604', 'YYYYMM')), 'DD')))
GROUP BY W
ORDER BY W;
-- 희안한 방법
SELECT LEVEL, LAST, WEEK
, CASE WHEN (LEVEL * 7 - 6 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 6 - WEEK) ELSE '' END AS 일
, CASE WHEN (LEVEL * 7 - 5 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 5 - WEEK) ELSE '' END AS 월
, CASE WHEN (LEVEL * 7 - 4 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 4 - WEEK) ELSE '' END AS 화
, CASE WHEN (LEVEL * 7 - 3 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 3 - WEEK) ELSE '' END AS 수
, CASE WHEN (LEVEL * 7 - 2 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 2 - WEEK) ELSE '' END AS 목
, CASE WHEN (LEVEL * 7 - 1 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 1 - WEEK) ELSE '' END AS 금
, CASE WHEN (LEVEL * 7 - 0 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 0 - WEEK) ELSE '' END AS 토
FROM (SELECT TO_CHAR(LAST_DAY(TO_DATE('201510', 'YYYYMM')), 'DD') AS LAST FROM DUAL)
, (SELECT TO_CHAR(TO_DATE('201510', 'YYYYMM'), 'D') - 1 AS WEEK FROM DUAL)
CONNECT BY LEVEL <= CEIL((WEEK + LAST) / 7);