ETL跑批日历表提数结束时间功能的实现

最新推荐文章于 2022-08-13 18:57:58 发布

燕赵一剑

最新推荐文章于 2022-08-13 18:57:58 发布

阅读量1.9k

点赞数

分类专栏： ETL 文章标签： ETL ORACLE

本文链接：https://blog.csdn.net/A279217194/article/details/52144004

版权

ETL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

实现ETL跑批日历表功能,主要是针对旬报，月报功能，每个月的上中下三旬都需要提供数据，上旬数据11号提供，中旬数据21号提供，下旬数据下一个月的1号提供，其中下旬数据可以在月报数据里面直接根据相关字段得到20号至下个月月初前一天的数据。由于相关跑批程序在传参过程中时间参数只能传入一个（而且传入参数是字符串型的），因此需要通过传进去的时间字符串参数计算所需要的正确提数的结束时间。为此，写了下面的oracle自定义函数FUN_ETL_DATE_TRUN_END.

相关代码如下：

CREATE OR REPLACE FUNCTION FUN_ETL_DATE_TRUN_END (V_ETL_DATE  VARCHAR2)
RETURN DATE
IS
/***************************************************************************************
  NAME  : FUN_ETL_DATE_TRUN_END
  AUTO  : MAOJIANBO
  DESC  : 实现ETL跑批日历表功能,主要是针对旬报，月报功能，每个月的上中下三旬都需要提供数据，
          上旬数据11号提供，中旬数据21号提供，下旬数据下一个月的1号提供，其中下旬数据可以在
          月报数据里面直接根据相关字段得到20号至下个月月初前一天的数据。由于相关跑批程序在
          传参过程中时间参数只能传入一个（而且传入参数是字符串型的），因此需要通过传进去的
          时间字符串参数计算所需要的正确提数的结束时间。
  INPUT : V_ETL_DATE 传入当前时间字符串，获取所需取数的结束时间END_DATE
  CALL  : SELECT FUN_ETL_DATE_TRUN_END('20160301') FROM DUAL; 2016/2/29 
          SELECT FUN_ETL_DATE_TRUN_END('20150301') FROM DUAL; 2015/2/28 
          SELECT FUN_ETL_DATE_TRUN_END('20160801') FROM DUAL; 2016/7/31
  DATE  : 2016/08/06
****************************************************************************************/
OUT_RESULT DATE;
RESULT VARCHAR2(32):='';
BEGIN
    IF SUBSTR(V_ETL_DATE,5,2) = '02'  
        THEN IF TO_CHAR(LAST_DAY(TO_DATE(V_ETL_DATE,'YYYYMMDD')),'DD')='28'
            THEN  CASE 
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='01'
                 THEN  RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN  SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='02'
                 THEN  RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='03'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='04'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='05'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='06'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='07'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='08'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='09'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='10'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='11'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='12'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='13'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='14'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='15'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='16'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='17'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='18'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='19'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='20'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='21'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='22'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='23'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='24'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='25'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='26'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='27'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='28'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               END CASE;  
           ELSE 
                CASE 
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='01'
                 THEN  RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN  SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='02'
                 THEN  RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='03'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='04'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='05'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='06'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='07'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='08'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='09'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='10'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='11'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='12'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='13'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='14'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='15'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='16'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='17'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='18'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='19'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='20'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='21'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='22'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='23'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='24'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='25'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='26'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='27'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='28'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='29'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';       
               END CASE;
            END IF;     
  ELSE          
                       
    CASE        WHEN  SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='01'
                 THEN  RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN  SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='02'
                 THEN  RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='03'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='04'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='05'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='06'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='07'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='08'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='09'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='10'
                  THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'MM')-1,'YYYYMMDD');
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='11'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='12'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='13'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='14'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='15'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='16'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='17'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='18'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='19'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
                WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='20'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='21'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='22'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='23'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='24'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='25'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='26'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='27'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='28'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='29'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';       
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='30'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
               WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,'YYYYMMDD'),'YYYYMMDD'),7,2)='31'
                 THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'20';
      END CASE;
 END IF;  
 OUT_RESULT := TO_DATE(RESULT,'YYYYMMDD');   
RETURN OUT_RESULT;
END FUN_ETL_DATE_TRUN_END;

当然类似的正确的开始取数时间我们也可以根据上面程序来依次类推完成。

燕赵一剑

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
5
评论
ETL跑批日历表提数结束时间功能的实现

实现ETL跑批日历表功能,主要是针对旬报，月报功能，每个月的上中下三旬都需要提供数据，上旬数据11号提供，中旬数据21号提供，下旬数据下一个月的1号提供，其中下旬数据可以在月报数据里面直接根据相关字段得到20号至下个月月初前一天的数据。由于相关跑批程序在传参过程中时间参数只能传入一个（而且传入参数是字符串型的），因此需要通过传进去的时间字符串参数计算所需要的正确提数的结束时间。为此，写了下面的oracle自定义函数FUN_ETL_DATE_TRUN_END.
复制链接

扫一扫

专栏目录