需求:oracle无法使用数据库情况下剔除春节及十月一假期计算工作日差
解决方案:
--测试
select FN_DURATION('2021-02-09','2021-03-08') from dual
--新建自定义函数
create or replace function FN_DURATION(para1 in varchar2, para2 in varchar2)
return varchar2
as
begin
if TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') < TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') and TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss')<TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss')
then return TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss')-TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') ;
elsif (TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') < TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') and (TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss')))
then return TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') - TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss');
elsif (TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') < TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') and (TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')))
then return TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss')-TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss')-(TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss')-TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss')) ;
elsif (TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') < TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') and (TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss')))
then return TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')-TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') ;
elsif (TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') < TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') and TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') >TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss'))
then return TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss')-TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss')-(TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss')-2021-02-06)-(TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss')-TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')) ;
elsif ((TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss')) and (TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss')))
then return 0 ;
elsif ((TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss')) and (TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')))
then return TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss')-TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss') ;
elsif ((TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss')) and (TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss')))
then return TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')-TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss') ;
elsif ((TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-02-06','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss')) and TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') >TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss'))
then return TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss')-TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss')-(TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss')-TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')) ;
elsif ((TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')) and (TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')))
then return TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss')-TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') ;
elsif ((TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')) and (TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss')))
then return TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')-TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') ;
elsif ((TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-03-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')) and TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') >TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss'))
then return TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss')-TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss')-(TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss')-TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss')) ;
elsif ((TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss')) and (TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss')))
then return 0 ;
elsif ((TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') between TO_DATE('2021-10-01','yyyy-MM-dd hh24-mi-ss') and TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss')) and TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss') >TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss'))
then return TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss')-TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss') ;
elsif (TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss')>TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss') and TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss')>TO_DATE('2021-10-08','yyyy-MM-dd hh24-mi-ss'))
then return TO_DATE(para2,'yyyy-MM-dd hh24-mi-ss')-TO_DATE(para1,'yyyy-MM-dd hh24-mi-ss') ;
end if;
end FN_DURATION;
--删除自定义函数
drop function FN_DURATION;
oracle自定义函数剔除假期
最新推荐文章于 2024-06-11 18:44:15 发布