实现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'),7,2)='01'
- THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'MM')-1,'YYYYMMDD');
- WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,2)='02'
- THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,'YYYYMMDD');
- WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,2)='03'
- THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,2)='04'
- THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,2)='05'
- THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,2)='06'
- THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,2)='07'
- THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,2)='08'
- THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,2)='09'
- THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,2)='10'
- THEN RESULT := TO_CHAR(TRUNC(TO_DATE(V_ETL_DATE,2)='11'
- THEN RESULT := SUBSTR(V_ETL_DATE,1,6)||'10';
- WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,2)='12'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='13'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='14'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='15'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='16'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='17'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='18'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='19'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='20'
- THEN RESULT := SUBSTR(V_ETL_DATE,6)||'10';
- WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,2)='21'
- THEN RESULT := SUBSTR(V_ETL_DATE,6)||'20';
- WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,2)='22'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='23'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='24'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='25'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='26'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='27'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='28'
- THEN RESULT := SUBSTR(V_ETL_DATE,6)||'20';
- END CASE;
- ELSE
- CASE
- WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,2)='29'
- THEN RESULT := SUBSTR(V_ETL_DATE,6)||'20';
- END CASE;
- END IF;
- ELSE
- CASE WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,6)||'20';
- WHEN SUBSTR(TO_CHAR(TO_DATE(V_ETL_DATE,2)='30'
- THEN RESULT := SUBSTR(V_ETL_DATE,2)='31'
- THEN RESULT := SUBSTR(V_ETL_DATE,6)||'20';
- END CASE;
- END IF;
- OUT_RESULT := TO_DATE(RESULT,'YYYYMMDD');
- RETURN OUT_RESULT;
- END FUN_ETL_DATE_TRUN_END;