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;
