• Oracle 创建时间维度表并更新是否工作日字段


    create or replace PROCEDURE PF_ETL_CREATE_DIM_OA_TIME  is
      /*PF_ETL_CREATE_DIM_OA_TIME: 生成OA时间维数据并更新工作日字段(designer:guo)
      begin_date: 起始时间20160101
      end_date:结束时间20161231
      exec PF_ETL_CREATE_DIM_OA_TIME
      delete from Dim_OA_Time where fyear>=2022;
      select * from Dim_OA_Time 
      select distinct fyear from Dim_OA_Time  order by fyear
      */
    
      begin_date nvarchar2(20);
      end_date nvarchar2(20);
      dDate date;
      f_year int;
      f_yearname nvarchar2(20);
      f_quarter int;
      f_quarter2 int;
      f_quartername nvarchar2(20);
      f_month int;
      f_monthname nvarchar2(20);
      f_datename nvarchar2(20);
      f_week int;
      f_weekname varchar2(20);
      f_weekday varchar2(20);
      f_yearfirstday date;
      f_yearlastday date;
      f_quarterfirstday date;
      f_quarterlastday date;
      f_monthfirstday date;
      f_monthlastday date;
      f_NextDate date;
      f_nextDayName nvarchar2(30);
      f_NextmonthName nvarchar2(64);
      f_TBmonthName nvarchar2(64);
      f_DateStr varchar2(64);
      adddays int;
      end_year varchar2(64);
      begin_year varchar2(64);
    BEGIN
      SELECT  to_char(extract(year from sysdate)) into begin_year from dual;
      delete from Dim_OA_Time where fyear>=to_number(begin_year);
      begin_date :=begin_year||'0101';
      SELECT  to_char(extract(year from sysdate)+5) into end_year from dual;
      end_date   := end_year||'1231';
      adddays := 1 ;
      dDate := to_date(begin_date,'yyyymmdd');
      WHILE (dDate <= to_date(end_date,'yyyymmdd'))
      loop
         f_year :=to_number(to_char(dDate, 'yyyy'));
         f_yearname := to_char(dDate,'yyyy')|| '';
    
         f_NextDate := dDate+1;
         f_DateStr := to_char(dDate,'yyyy-mm-dd');
         f_quarter := f_year*100+to_number(to_char(dDate, 'q'));
         f_quartername := f_yearname || to_char(dDate, 'q') || '季度';
         f_month := f_year*100+ to_number(to_char(dDate, 'mm'));
         f_monthname := f_yearname || to_char(dDate, 'mm')||'';
         f_datename := f_monthname||to_char(dDate, 'dd')||'';
    
         f_nextDayName := to_char(f_NextDate,'yyyy')||''||to_char(f_NextDate, 'mm')||''||to_char(f_NextDate, 'dd')||'';
         --f_weekname :=f_yearname || subStr('000'|| to_char(dDate,'fmww'), -2)||'周';
         f_weekname :=f_yearname || subStr('000'|| to_char(dDate,'IW'), -2)||'';
         f_week   := f_year*100+ to_number(to_char(dDate,'IW')); --201501
         -- f_weekday := to_char(dDate, 'day'); --星期几
         f_weekday := to_char(dDate, 'day','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE'''); --星期几
         f_yearfirstday := to_date(to_char(f_year)||'0101', 'yyyymmdd');
         f_yearlastday := to_date(to_char(f_year)||'1231', 'yyyymmdd');
         f_monthfirstday := to_date(to_char(dDate, 'yyyy')||to_char(dDate, 'mm')||'01','yyyymmdd');
         f_monthlastday :=  ADD_MONTHS(f_monthfirstday,1)-1;
    
         f_NextmonthName  := to_char(ADD_MONTHS(f_monthfirstday,1),'yyyy')||''||to_char(ADD_MONTHS(f_monthfirstday,1), 'mm')||'';
    
         f_TBmonthName := to_char(f_year+1)||''||to_char(f_NextDate, 'mm')||'';
         f_quarter2 := to_number(to_char(dDate, 'q'));
         f_quarterfirstday := ADD_MONTHS(f_yearfirstday,(f_quarter2-1)*3);
         f_quarterlastday := ADD_MONTHS(f_yearfirstday,(f_quarter2)*3)-1;
        insert into Dim_OA_Time(FDATE,FDATENAME,FYEAR,YEARNAME,FQUARTER, QUARTERNAME,FMONTH,MONTHNAME,dayname,Weekname,fWeek,Weekday
               ,Yearfirstdate,Yearlastdate,Quarterfirstdate,Quarterlastdate,Monthfirstdate,Monthlastdate,HBDayName,HBMonthName,TBmonthName)
                            values(dDate,f_DateStr,f_year,f_yearname,f_quarter,f_quartername,f_month,f_monthname,f_datename,f_weekname,f_week,f_weekday
                                 ,f_yearfirstday,f_yearlastday,f_quarterfirstday,f_quarterlastday,f_monthfirstday,f_monthlastday,f_nextDayName,f_NextmonthName,f_TBmonthName);
         dDate :=  dDate + adddays;
      END loop;
    
        --更新周末          
        update dim_oa_time set is_work ='0' where weekday in ('星期六','星期日'); 
        update dim_oa_time set is_work ='1' where weekday  not in ('星期六','星期日');
    --更新不是周末的休息日 merge into dim_oa_time using (select distinct to_date(date_num,'yyyy-mm-dd hh24:mi:ss') as date_num, is_rest from WORKTIME_SPECIALDAY where IS_REST !=0 and ORG_ACCOUNT_ID='670869647114347' ) table2 on (dim_oa_time.fdate = table2.date_num) when matched then update set dim_oa_time.is_work = '0'; --更新是周末的工作日 merge into dim_oa_time using (select distinct to_date(date_num,'yyyy-mm-dd hh24:mi:ss') as date_num, is_rest from WORKTIME_SPECIALDAY where IS_REST =0 and ORG_ACCOUNT_ID='670869647114347') table2 on (dim_oa_time.fdate = table2.date_num) when matched then update set dim_oa_time.is_work = '1'; end;
  • 相关阅读:
    SSH 转发学习【转】
    JAVA 垃圾笔记一溜堆
    PHP CURL 伪造IP和来路
    PHP CURL 抓取失败 自己调试
    Tomcat 基本配置
    windows 64位 下 安装 tomcat
    UBUNTU下MONGODB出现PHP Fatal error: Uncaught exception 'MongoConnectionException' with message 和 Authentication failed on database 'admin' with username
    汽车牌牌知识
    一个用pyton写的监控服务端进程的软件hcm
    Python3.x和Python2.x的区别
  • 原文地址:https://www.cnblogs.com/xiaobaidejiucuoben/p/14630923.html
Copyright © 2020-2023  润新知