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

港控/mmm° 2022-08-12 04:59 146阅读 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;