Oracle解决单表字符串日期自动更新问题

 

数据需求：

 

  方案摒弃GATHERTIME，存储表使用COLUMNVALUE7做为日期列，日期格式如下：

 

字符串格式	时间类型
2016	年
201610	月
20161008	日
201610081050	分

现在的需求是所有存储表的样例数据要满足自动更新,且都更新到本月,本日,当前分钟等

----------------------------------------漂亮的分隔符------------------------------------
解决思路：
1.需要定时更新，所以要使用Oracle定时器；
2.需要根据不同的时间类型去筛选数据，比如只能每天更新日时间字符串，所以这里想到使用自定义函数去判断字符串的日期格式；

3.需要更新所有存储表中的COLUMNVALUE7，考虑使用存储过程去处理；

-----------------------------------------漂亮的分隔符------------------------------------

第一步，创建判断字符串日期类型格式函数

/*判断字符串是否是为月格式*/
CREATE OR REPLACE FUNCTION is_month(parameter VARCHAR2) RETURN NUMBER IS
  val DATE;
BEGIN
  val := TO_DATE(NVL(parameter, 'a'), 'yyyymm');
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;

select is_month('201610') from dual;/*返回1*/

/*查询时间为每月数据*/
select *  from  z_dp_verification t
where is_month(t.columnvalue7)=1

结果如下：

/*判断字符串是否是为日格式*/
CREATE OR REPLACE FUNCTION is_day(parameter VARCHAR2) RETURN NUMBER IS
  val DATE;
BEGIN
  val := TO_DATE(NVL(parameter, 'a'), 'yyyymmdd');
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;

/*调用*/
select is_day('20161008') from dual;/*返回1*/

/*查询时间为每天数据*/
select *  from  z_dp_verification t
where is_day(t.columnvalue7)=1

结果如下：

/*判断字符串是否是为分种格式*/
CREATE OR REPLACE FUNCTION is_minute(parameter VARCHAR2) 
RETURN NUMBER IS
  val DATE;
BEGIN
  val := TO_DATE(NVL(parameter, 'a'), 'YYYYMMDDHH24MI');
  RETURN 1;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;

/*调用*/
select is_minute('201610081050') from dual;

/*查询时间为分钟数据*/
select *  from  z_dp_verification t
where is_minute(t.columnvalue7)=1 and is_day(t.columnvalue7)=0;

结果如下：

第二步，创建更新日期的存储过程

/*更新天类型字符串*/
create or replace procedure P_UpdateCol7_Day is
  v_sql     varchar2(1000);
begin
  declare
         cursor c_job
         is
         select u.TABLE_NAME from user_tables u where u.TABLE_NAME like 'Z_%';
         c_row c_job%rowtype;
  begin
         open c_job;
           loop
             fetch c_job into c_row;
             exit when c_job%notfound;
                 v_sql := 'update '|| c_row.TABLE_NAME ||' t set t.columnvalue7 = to_char(sysdate, ''YYYYMMDD'') where is_day(t.columnvalue7)=1 ';
                 execute immediate (v_sql);
           end loop;
        close c_job;
        commit;
  end;
exception
  when others then
    rollback;
end P_UpdateCol7_Day;

/*更新月类型字符串*/
create or replace procedure P_UpdateCol7_Month is
  v_sql     varchar2(1000);
begin
  declare
         cursor c_job
         is
         select u.TABLE_NAME from user_tables u where u.TABLE_NAME like 'Z_%';
         c_row c_job%rowtype;
  begin
         open c_job;
           loop
             fetch c_job into c_row;
             exit when c_job%notfound;
                 v_sql := 'update '|| c_row.TABLE_NAME ||' t set t.columnvalue7 = to_char(sysdate, ''YYYYMM'') where is_month(t.columnvalue7)=1 ';
                 execute immediate (v_sql);
           end loop;
        close c_job;
        commit;
  end;
exception
  when others then
    rollback;
end P_UpdateCol7_Month;

/*更新分类型字符串*/
<font color="#ff0000">create or replace procedure P_UpdateCol7_Minute is
  v_sql     varchar2(1000);
begin
  declare
         cursor c_job
         is
         select u.TABLE_NAME from user_tables u where u.TABLE_NAME like 'Z_%';
         c_row c_job%rowtype;
  begin
         open c_job;
           loop
             fetch c_job into c_row;
             exit when c_job%notfound;
                 v_sql := 'update '|| c_row.TABLE_NAME ||' t set t.columnvalue7 = to_char(sysdate, ''YYYYMMDDHH24MI'') where is_minute(t.columnvalue7)=1 and is_day(t.columnvalue7)=0 ';
                 execute immediate (v_sql);
           end loop;
        close c_job;
        commit;
  end;
exception
  when others then
    rollback;
end P_UpdateCol7_Minute;

最后，每个存储过程测试执行一遍，验证更新时间字符串无误。

 

第三步，创建Oracle定时器

/*更新天JOB*/

间隔：每天午夜12点: 'TRUNC(SYSDATE + 1)'

 

/*更新月JOB*/

间隔：每个月第一天的午夜12点: 'TRUNC(LAST_DAY(SYSDATE ) + 1)'

 

/*更新分JOB*/

间隔：每分钟执行:TRUNC(sysdate,'mi') + 1/ (24*60)