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

84 阅读 0 评论 56 点赞

我是靠谱客的博主甜美苗条，最近开发中收集的这篇文章主要介绍ETL跑批日历表提数结束时间功能的实现，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

实现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;

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