oracle 一段时间内有几个工作日（天数）

create or replace
FUNCTION GETWORKDAY3
( sdate IN DATE
, edate IN DATE
) RETURN NUMBER AS
 holidays number:=0;
 st Date;
 en Date;
BEGIN
 for h in (select holiday_id hid from T_ATTE_HOLIDAYS) loop
    select DATE_START into st from T_ATTE_HOLIDAYS where holiday_id=h.hid;
    select DATE_END into en from T_ATTE_HOLIDAYS where holiday_id=h.hid;
   
    if(st>sdate and en>edate and edate>=st) then
      begin
      holidays:=(edate-st)+1+holidays-workend(st,edate);
    -- holidays:=(st-sdate)-workend(sdate,st)+holidays;
       dbms_output.put_line('left'||to_char(edate-st));
      end;
      Elsif(st<=sdate and en>edate) then
        begin
        holidays:=(edate-sdate)+1-workend(edate,sdate)+holidays;
      --  holidays:=(st-sdate)+(en-edate)+holidays-workend(sdate,st)-workend(edate,en);
         dbms_output.put_line('midder');
        end;
      Elsif(st<sdate and en<edate and sdate<en) then
        begin
        holidays:=(en-sdate)+1-workend(sdate,en)+holidays;
       -- holidays:=(edate-en)-workend(en,edate)+holidays;
         dbms_output.put_line('right');
        --  dbms_output.put_line(to_char(st,'yyyy-MM-dd')||'<'||to_char(sdate,'yyyy-MM-dd')||' and '||to_char(en,'yyyy-MM-dd')||'<'||to_char(edate,'yyyy-MM-dd'));
        end;
      Elsif(st>=sdate and en<=edate) then
        begin
        holidays:=(en-st)-workend(st,en)+1+holidays;
      -- holidays:=(st-sdate)+(edate-en)-workend(sdate,st)-workend(en,edate)+holidays;
         dbms_output.put_line('outer'||to_char(en-st)||to_char(workend(st,en)));
        end;
      else
        begin
        holidays:=0+holidays;
      -- holidays:=(edate-sdate)+1-workend(sdate,edate);
         dbms_output.put_line('else');
        end;
      end if;
     
  end loop;
  RETURN (edate-sdate)+1-holidays-workend(sdate,edate);
 --return holidays;
END GETWORKDAY3;

create or replace FUNCTION WORKEND
( sdate IN DATE
, edate IN DATE
) RETURN NUMBER AS
v_date Date;
v_days number:=0;
BEGIN
    v_date:=sdate;
  while v_date<=edate loop
    if to_char(v_date,'d') in ('1','7') then
      v_days:=v_days+1;
    end if;
    v_date:=v_date+1;
  end loop;
  return v_days;
END WORKEND;

 

------------------------第二种方法---------------------------------------------------------

create or replace
FUNCTION F_GETWORKDAY( BDATE_ IN DATE, EDATE_ IN DATE) RETURN NUMBER AS
/*************************************************************************************************************************
 * 功能：
 *    计算两个日期点之间的工作日的个数
 *
 * 参数：
 *    BDATE: 开始日期
 *    EDATE: 结束日期
 *
 * 返回值：
 *    返回工作日的个数
 *
 * 作者&时间:
 *    周继伟，2010年03月10日
 *
 * 修改：
 *    第1次：[某人]于[时间]，[原因]
 *    第2次：[某人]于[时间]，[原因]
 *    第3次：[某人]于[时间]，[原因]
 *    第4次：[某人]于[时间]，[原因]
 *    第5次：[某人]于[时间]，[原因]
 *
 *************************************************************************************************************************/ 

  TYPE holiday_type IS TABLE OF VARCHAR(10) INDEX BY BINARY_INTEGER;
  unworkdays holiday_type;/*申请非工作日内存表变量*/
 
  TYPE holiday_record_type IS RECORD (sdate DATE,edate DATE);
  holiday_record holiday_record_type;/*存储从表中取出设置好的单条节假日*/
 
  CURSOR cur_holiday IS SELECT HOLIDAY_BDATE,HOLIDAY_EDATE from T_HOLIDAY;
 
  datetmp DATE;
  bWorkDay BOOLEAN DEFAULT TRUE;
  i NUMBER DEFAULT 0;
  iRtn NUMBER DEFAULT 0;
 
BEGIN
 
  /*验证,如果开始日期或工作日数为空,则直接返回NULL*/                 
  IF BDATE_ IS NULL OR EDATE_ IS NULL THEN RETURN 0; END IF;

/*将所有的非工作日存储在临时内存表中*/   
  OPEN cur_holiday;
    IF cur_holiday%ISOPEN THEN
      LOOP
        <<mycontinue1>>
        FETCH cur_holiday INTO holiday_record;
       
        EXIT WHEN cur_holiday%NOTFOUND;
       
        IF holiday_record.sdate IS NULL OR holiday_record.edate IS NULL THEN GOTO mycontinue1;  END IF;
       
        IF holiday_record.sdate <= holiday_record.edate THEN
          WHILE holiday_record.sdate <= holiday_record.edate
          LOOP
            unworkdays(i) := TO_CHAR(holiday_record.sdate,'MM-dd');
            holiday_record.sdate := holiday_record.sdate + 1;
            i := i + 1;
          END LOOP;
        END IF;
       
      END LOOP;
    END IF;
  CLOSE cur_holiday;
 
  datetmp := BDATE_;
 
  WHILE datetmp <= EDATE_
  LOOP
    IF TO_CHAR(datetmp,'d') IN (1,7) THEN
      bWorkDay := FALSE;
      GOTO mycontinue2;
    END IF;
   
    IF bWorkDay THEN
      IF unworkdays.LAST IS NOT NULL THEN
        FOR j IN 0..unworkdays.LAST LOOP
          IF TO_CHAR(datetmp,'MM-dd') = unworkdays(j) THEN
            bWorkDay := FALSE;
            GOTO mycontinue2;
          END IF;
        END LOOP;
      ELSE
        bWorkDay := FALSE;
      END IF;
    END IF;
   
    <<mycontinue2>>
    IF bWorkDay THEN iRtn := iRtn + 1; END IF;
   
    datetmp := datetmp + 1;
    bWorkDay := TRUE;
   
  END LOOP;
   
  RETURN iRtn;
END F_GETWORKDAY;

 

-----------------------------------------第三种方法------------------------------------------------

create or replace function te2(startdate in date, enddate in date) return integer is
  rs integer;
  counts integer; --两日期间的天数
  yunum integer; --余数
  weeknum integer; --星期几
  yk integer; --余数中有几天周末
begin
  select floor(enddate-startdate) into counts from dual; --不包括enddate这一天
  counts:=counts+1; --//包括enddate这一天(加1)
  rs:=counts;
  yk:=0;

  select mod(counts,7) into yunum from dual;
  select to_number(to_char(startdate,'D')) into weeknum from dual;
  if yunum>0 then
     begin
           if weeknum=6 then
              begin
                  if yunum>1 then yk:=2;
                  else yk:=1;
                  end if;
               end;
           elsif weeknum=1 then                         --注意不是elseif
                 yk:=1;
           else
               begin
                 yk:=yunum-(7-weeknum);
                 if yk<0 then
                     yk:=0;
                  end if;
               end;
         end if;
       end;
     end if;

  rs:=rs-floor(rs/7) *2-yk; --//注意integer 类型的rs/7
  return rs;
end te2;

 

把if/elsif改成case:

case weeknum
           when 6 then
              begin
                  if yunum>1 then yk:=2;
                  else yk:=1;
                  end if;
               end;
           when 1 then
                yk:=1;
           else
               begin
                 yk:=yunum-(7-weeknum);
                 if yk<0 then
                     yk:=0;
                  end if;
               end;
       end case;