在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
）;

使用Oracle的flashback table来恢复数据使用存储过程读取Oracle中的clob字段的数据相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）