oralce sql 创建指定时间段内的日历信息

阳光穿透心脏的1/2处 2022-04-12 03:09 183阅读 0赞

\-- Create table  
create table TEMP\_CALENDAR  
(  
MONTH VARCHAR2(6),  
W\_7 VARCHAR2(2),  
W\_1 VARCHAR2(2),  
W\_2 VARCHAR2(2),  
W\_3 VARCHAR2(2),  
W\_4 VARCHAR2(2),  
W\_5 VARCHAR2(2),  
W\_6 VARCHAR2(2),  
WEEK VARCHAR2(20)  
) ;  
\-- Add comments to the table  
comment on table TEMP\_CALENDAR  
is '日期源数据表';  
\-- Add comments to the columns  
comment on column TEMP\_CALENDAR.MONTH  
is '月份（格式如：200801）';  
comment on column TEMP\_CALENDAR.W\_7  
is '周日的日期（如：06）';  
comment on column TEMP\_CALENDAR.W\_1  
is '周一（如：07）';  
comment on column TEMP\_CALENDAR.W\_2  
is '周二（如：08）';  
comment on column TEMP\_CALENDAR.W\_3  
is '周三（如：09）';  
comment on column TEMP\_CALENDAR.W\_4  
is '周四（如：10）';  
comment on column TEMP\_CALENDAR.W\_5  
is '周五（如：11）';  
comment on column TEMP\_CALENDAR.W\_6  
is '周六（如：12）';  
comment on column TEMP\_CALENDAR.WEEK  
is '本月第几周';  
\----生成 日历信息  
\-- 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 :=2000; ---start year  
vi\_end\_year :=2100; --- end year  
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 temp\_calendar ( month, week,w\_7, w\_1, w\_2, w\_3, w\_4, w\_5, w\_6 )  
  
select substr(vs\_first\_day,1,6) month,  
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;  
　 order by a.year,a.month, a.week;