oracle自定义函数剔除假期

需求：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;