Dummy 레코드 만들기 CONNECT BY LEVEL

LEVEL

SELECT LEVEL FROM DUAL

CONNECT BY LEVEL <= 10

LEVEL 을 이용한 달력 

SELECT LEVEL

                ,DECODE(SUBSTR((DT+0+(LEVEL-1)*7),4,2),SUBSTR(:nMonth,5,2),TO_CHAR(DT+0+(LEVEL-1)*7,'DD'),'') SUN

                ,DECODE(SUBSTR((DT+1+(LEVEL-1)*7),4,2),SUBSTR(:nMonth,5,2),TO_CHAR(DT+1+(LEVEL-1)*7,'DD'),'') MON

                ,DECODE(SUBSTR((DT+2+(LEVEL-1)*7),4,2),SUBSTR(:nMonth,5,2),TO_CHAR(DT+2+(LEVEL-1)*7,'DD'),'') TUE

                ,DECODE(SUBSTR((DT+3+(LEVEL-1)*7),4,2),SUBSTR(:nMonth,5,2),TO_CHAR(DT+3+(LEVEL-1)*7,'DD'),'') WED

                ,DECODE(SUBSTR((DT+4+(LEVEL-1)*7),4,2),SUBSTR(:nMonth,5,2),TO_CHAR(DT+4+(LEVEL-1)*7,'DD'),'') THU

                ,DECODE(SUBSTR((DT+5+(LEVEL-1)*7),4,2),SUBSTR(:nMonth,5,2),TO_CHAR(DT+5+(LEVEL-1)*7,'DD'),'') FRI

                ,DECODE(SUBSTR((DT+6+(LEVEL-1)*7),4,2),SUBSTR(:nMonth,5,2),TO_CHAR(DT+6+(LEVEL-1)*7,'DD'),'') SAT

            FROM (SELECT TRUNC(TO_DATE(:nMonth, 'YYYYMMDD'),'MM') + 1 - TO_NUMBER(TO_CHAR(TRUNC(TO_DATE(:nMonth, 'YYYYMMDD')), 'D')) DT FROM DUAL)

            CONNECT BY LEVEL < 6

지정 년월 부터 년월까지 한달씩 증가 하는 쿼리 

select  ADD_MONTHS(TO_DATE('20130101', 'YYYYMMDD'),  -months_between( TO_DATE('20130101', 'YYYYMMDD') , TO_DATE('20050101', 'YYYYMMDD'))+LEVEL-1)    from dual

CONNECT BY LEVEL <= months_between( TO_DATE('20130101', 'YYYYMMDD') , TO_DATE('20050101', 'YYYYMMDD'))