在Oracle数据库中创建日期主档表

 Oracle提供的日期函数可以满足我们日常用到的对日期的所有操作。通常，在设计表结构的时候，我们也常常会遇到日期类型的字段。如果业务里面需要类似汇总给定日期所在周的资料，汇总给定日期所在月的各周资料等操作，那直接写SQL语句将比较复杂。

    所以，我是直接在数据里面建立一个日期的主档表，在里面将可能用到的统计字段先计算后，后面业务逻辑的汇总只需连接这个表就可以得到想要的结果。-
 

    下面是这个表的建立和数据的生成过程，可以在里面新加字段以满足不同的需求，相关的索引可以根据自己的需要去建立！

    同时欢迎大家一起讨论更好的方法！不足之处，请不吝指教！

    1. 建立日期主档表

CREATE TABLE DATE_MAIN   
(   
  DATE_TIME                  DATE,   
  DAY_OF_WEEK                INTEGER,   
  WEEK_OF_YEAR               INTEGER,   
  MONTH_OF_YEAR              INTEGER,   
  WEEK_OF_MONTH              INTEGER,   
  YEAR_AND_MONTH             CHAR(7),   
  FIRST_DAY_OF_WEEK          DATE,   
  LAST_DAY_OF_WEEK           DATE,   
  FIRST_DAY_OF_WEEK_OF_MONTH DATE,   
  LAST_DAY_OF_WEEK_OF_MONTH  DATE   
)    
    2. 插入日期资料

declare    
  i integer;   
  begin_date char(10);   
begin   
  begin_date := '2008-01-01';   
  FOR i IN 0..3650   
  LOOP   
      execute IMMEDIATE   
      'INSERT INTO date_main(Date_Time)   
      VALUES(:X)' USING to_date(begin_date,'YYYY-MM-DD') + i;   
  END LOOP;   
end; 

    3. 设置相关字段的值

update DATE_MAIN   
SET month_of_year = to_number(to_char(date_time,'MM')),   
    week_of_year = to_number(to_char(date_time,'IW')),   
    day_of_week = to_number(to_char(date_time,'D'));   
  
--设置周一为每周的第一天   
UPDATE date_main SET day_of_week =day_of_week - 1;   
UPDATE date_main SET day_of_week = 7   
WHERE day_of_week =0;     
    4. 设置每月的周别

Declare   
  current_year CHAR(4);   
  current_month INT;   
  current_week INT;   
  temp_year CHAR(4);   
  temp_month INT;   
  weekOfMonth INT;   
  dayOfWeek INT;   
begin   
  -- Test statements here   
  current_year := '1999';   
  current_month := -1;   
  current_week :=-1;   
  
  FOR X IN (   
      SELECT to_char(date_time,'YYYYMMDD') dat   
          ,month_of_year   
          ,week_of_year   
          ,day_of_week   
          ,week_of_month   
      FROM date_main s   
      ORDER BY s.date_time   
  )   
  LOOP   
     temp_year := substr(X.dat,1,4);   
  
     IF temp_year <> current_year THEN  --跨年   
         current_year := temp_year;   
         current_month := X.month_of_year;   
         current_week := X.week_of_year;   
         dayOfWeek := X.day_of_week;   
         weekOfMonth := 1;   
     ELSE  --同一年   
         temp_month := X.month_of_year;   
         IF temp_month <> current_month THEN --跨月   
             weekOfMonth := 1;   
             current_month := temp_month;   
         ELSE --同月份   
             IF current_week <> X.week_of_year THEN   
                 current_week := X.week_of_year;   
                 weekOfMonth := weekOfMonth + 1;   
             END IF;   
         END IF;   
     END IF;   
  
     EXECUTE IMMEDIATE   
         'UPDATE date_main '  
         || 'SET week_of_month = :A '  
         || 'WHERE date_time = to_date(:B,''YYYYMMDD'')' USING weekOfMonth,X.DAT;   
  END LOOP;   
end; 

    5.设置每周的第一天和最后一天

--设置每周的第一天   
UPDATE Date_main   
 SET first_day_of_week  = trunc(date_time,'DD')-to_char(date_time -1,'D') + 1;   
  
--设置每周的最后一天   
UPDATE Date_main   
SET last_day_of_week = first_day_of_week + 6;   
  
UPDATE Date_main A   
SET (first_day_of_Week_of_Month,LAST_day_of_week_of_Month) = (   
    SELECT mind,maxd FROM   
    (   
        SELECT MIN(date_time) MIND ,MAX(date_time) MAXD,year_and_month,week_of_month   
        FROM date_main GROUP BY year_and_month,week_of_month   
    ) B    
    WHERE A.Year_And_Month = B.year_and_month AND A.week_of_month = B.week_of_month   
); 

服务器租用/服务器托管中国五强！虚拟主机域名注册顶级提供商！15年品质保障！--亿恩科技[ENKJ.COM]