oracle 바인드변수 입력 받은 홀수,짝수 달력 만들기

oracle내에서 바인드 변수를 활용하여 입력한 년월의 달력을 표시해준다.

1입력시 짝수 달력, 0입력시 홀수 달력, null을 입력할시에 전체 달력의 월을 표시해주는 달력 실습

 

        select
        max(DECODE(DID, 1, YMD)) month,
        min(DECODE(WID, 1, DID)) SUN,
        min(DECODE(WID, 2, DID)) MON,
        min(DECODE(WID, 3, DID)) TUE,
        min(DECODE(WID, 4, DID)) WED,
        min(DECODE(WID, 5, DID)) THR,
        min(DECODE(WID, 6, DID)) FRI,
        min(DECODE(WID, 7, DID)) SAT
        from
        (
        select
            to_char(T1, 'YYYY-MM') YMD,
            extract(DAY from T1) DID,
            to_char(T1, 'D') WID,
            trunc(T1, 'D') WKID,
            to_char(T1, 'YYYY-MM') month
        from
            (
                select
                    SYM + no T1,
                    extract(DAY from to_date(:SM, 'YYYYMM') + ROWNUM - 1) DID,
                    to_char(to_date(:SM, 'YYYYMM') + ROWNUM - 1, 'D') WID
                from
                    (
                        select
                            ADD_MONTHS(SSM, DECODE(:NUM, NULL, ROWNUM - 1, ROWNUM+ROWNUM)) - 1 SYM,
                            extract(DAY from LAST_DAY(ADD_MONTHS(SSM, DECODE(:NUM, NULL, ROWNUM - 1, ROWNUM+ROWNUM)))) E
                        from
                            (
                                select
                                    to_date(:SM, 'YYYYMM') SM,
                                    case
                                        when :NUM = 0 then ADD_MONTHS(to_date(:SM, 'YYYYMM'), -2)
                                        when :NUM = 1 then ADD_MONTHS(to_date(:SM, 'YYYYMM'), -1)
                                        else to_date(:SM, 'YYYYMM')
                                    end SSM,
                                    to_date(:EM, 'YYYYMM') EM
                                from
                                    DUAL
                            )
                        connect by
                            ADD_MONTHS(SSM, DECODE(:NUM, NULL, ROWNUM - 1, ROWNUM+ROWNUM)) between SM and EM
                    ) A,
                    (
                        select
                            ROWNUM no
                        from
                            DUAL
                        connect by
                            LEVEL <= 31
                    ) B
                where
                    B.no between 1 and A.E
            )
        )
        group by
        YMD,
        WKID
        order by
        YMD,
        WKID;

 

홀수 달 출력

짝수달출력