在Oracle數(shù)據(jù)庫中創(chuàng)建日期主檔表

 Oracle提供的日期函數(shù)可以滿足我們?nèi)粘Ｓ玫降膶?duì)日期的所有操作。通常，在設(shè)計(jì)表結(jié)構(gòu)的時(shí)候，我們也常常會(huì)遇到日期類型的字段。如果業(yè)務(wù)里面需要類似匯總給定日期所在周的資料，匯總給定日期所在月的各周資料等操作，那直接寫SQL語句將比較復(fù)雜。

    所以，我是直接在數(shù)據(jù)里面建立一個(gè)日期的主檔表，在里面將可能用到的統(tǒng)計(jì)字段先計(jì)算后，后面業(yè)務(wù)邏輯的匯總只需連接這個(gè)表就可以得到想要的結(jié)果。-
 

    下面是這個(gè)表的建立和數(shù)據(jù)的生成過程，可以在里面新加字段以滿足不同的需求，相關(guān)的索引可以根據(jù)自己的需要去建立！

    同時(shí)歡迎大家一起討論更好的方法！不足之處，請(qǐng)不吝指教！

    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. 設(shè)置相關(guān)字段的值

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'));   
  
--設(shè)置周一為每周的第一天   
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. 設(shè)置每月的周別

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.設(shè)置每周的第一天和最后一天

--設(shè)置每周的第一天   
UPDATE Date_main   
 SET first_day_of_week  = trunc(date_time,'DD')-to_char(date_time -1,'D') + 1;   
  
--設(shè)置每周的最后一天   
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   
); 

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]