oracle 日历

以下是从网上找到的相应的 oracle日历的sql并进行了改造可以生成任意一段时间内的日历。 create table calendar ( statis_month number(6), -- 统计月份 statis_week number(6), -- 周 sunday number(6), -- 星期日 monday number(6), -- 星期一 tuesday number(6), -- 星期二 wednesday number(6), -- 星期三 thursday number(6), -- 星期四 friday number(6), -- 星期五 saturday number(6) -- 星期六 ); -- truncate table calendar; declare vi_begin_year number(6); vi_end_year number(6); vi_year number(6); vi_month number(6); vs_month varchar2(6); vs_first_day varchar2(8); begin vi_begin_year :=1900; vi_end_year :=2300; vi_month :=1; vi_year := vi_begin_year; loop exit when(vi_year > vi_end_year); for i in 1..12 loop if length(i) =1 then vs_month := to_char(vi_year)||'0'||to_char(i); else vs_month := to_char(vi_year)||to_char(i); end if; vs_first_day := vs_month||'01'; insert into calendar ( statis_month, statis_week, sunday, monday, tuesday, wednesday, thursday, friday, saturday ) select substr(vs_first_day,1,6), ceil((to_char(everyday,'dd')+(to_char(to_date(vs_first_day,'yyyymmdd'),'d')-1))/7) as week, sum(decode(to_char(everyday,'dy'),'星期日',to_char(everyday,'dd'))) as 星期日, sum(decode(to_char(everyday,'dy'),'星期一',to_char(everyday,'dd'))) as 星期一, sum(decode(to_char(everyday,'dy'),'星期二',to_char(everyday,'dd'))) as 星期二, sum(decode(to_char(everyday,'dy'),'星期三',to_char(everyday,'dd'))) as 星期三, sum(decode(to_char(everyday,'dy'),'星期四',to_char(everyday,'dd'))) as 星期四, sum(decode(to_char(everyday,'dy'),'星期五',to_char(everyday,'dd'))) as 星期五, sum(decode(to_char(everyday,'dy'),'星期六',to_char(everyday,'dd'))) as 星期六 from(select to_date(vs_first_day,'yyyymmdd') + level - 1 as everyDay from dual connect by level <= (last_day(to_date(vs_first_day,'yyyymmdd')) - to_date(vs_first_day,'yyyymmdd') +1) ) group by ceil((to_char(everyday,'dd')+(to_char(to_date(vs_first_day,'yyyymmdd'),'d')-1))/7) ; commit; end loop; vi_year := vi_year+1; end loop; end;