Oracle计算对公天数

  create  or replace function calTwoDays(startDate in date,endDate in date)
  return number
  is 
    dtStaDat date;
    dtEndDat date;
    
    start_date_str varchar2(100);
    end_date_str varchar2(100);
    
    iStaYear number;
    iStaMonth number;
    iStaDay number;
    
    iEndYear number;
    iEndMonth number;
    iEndDay number;
    
    iYear number;
    iMonth number;
    iMonthStr varchar2(4);
    iDay number;
    iDayStr varchar2(4);
    
    lDay1 number;
    lDay2 number;
    lDays number;
    gcldTmp date;
  begin
    -- Test statements here
    dtStaDat := startDate;
    dtEndDat := endDate;
    
    start_date_str:= to_char(startDate,'YYYYMMDD'); 
    end_date_str  := to_char(endDate,'YYYYMMDD');
    
    iStaYear   := to_number(substr(start_date_str,1,4));
    iStaMonth  := to_number(substr(start_date_str,5,2));
    iStaDay    := to_number(substr(start_date_str,7,2));
    
    iEndYear   := to_number(substr(end_date_str,1,4));
    iEndMonth  := to_number(substr(end_date_str,5,2));
    iEndDay    := to_number(substr(end_date_str,7,2));
    
    iYear  := iEndYear;
    iMonth := iEndMonth;
    iDay   := iStaDay;
    
    if 4 = iMonth or 6 = iMonth or 9 = iMonth or 11 = iMonth then
       if iDay > 30 then
          iDay := 30;
       end if;
    elsif 2 = iMonth then
      --闰年
      if '29' = to_char(last_day(to_date(iYear||'0201','YYYYMMDD')),'DD') then 
         if iDay > 29 then
            iDay := 29;
         end if;
      else   
         if iDay > 28 then
            iDay := 28;
         end if;          
      end if;
    end if;
    
    if iDay > iEndDay then
      iYear := iEndYear;
      iMonth := iEndMonth - 1;
      iDay := iStaDay;
      
      if 0 = iMonth then
          iMonth := 12;
          iYear := iYear-1;
      end if;
      
      if 4 = iMonth or 6 = iMonth or 9 = iMonth or 11 = iMonth then
          if iDay > 30 then
            iDay := 30;
          end if;
      elsif 2 = iMonth then
         --闰年
        if '29' = to_char(last_day(to_date(iYear||'0201','YYYYMMDD')),'DD') then 
            if iDay > 29 then
              iDay := 29;
            end if;
         else 
            if iDay > 28 then
              iDay := 28;
            end if;
         end if;
       end if;  
     end if;
     
     lDay1 := (iYear - iStaYear) * 360 + (iMonth - iStaMonth) * 30;
     if iMonth < 10 then
       iMonthStr := '0'||iMonth;
     else
       iMonthStr :=''||iMonth;
     end if;
     if iDay < 10 then
       iDayStr := '0'||iDay;
     else
       iDayStr :=''||iDay;  
     end if;
     
     gcldTmp := to_date(iYear||iMonthStr||iDayStr,'YYYYMMDD');
     
     if dtEndDat > gcldTmp then
       lDay2 := dtEndDat - gcldTmp;
     else 
       lDay2 := gcldTmp - dtEndDat;  
     end if;
     
     lDays := lDay1 + lDay2;
     
     return lDays;
  end;