ORACLE函数使用之计算2个时间段的工作日，返回天数

oracle函数使用之计算2个时间段的工作日，返回天数

create or replace function func_getworkdaynum(fromdate date, --开始日期
                                              todate   date --截至日期
                                              ) return number is

  result            number(4, 0);
  v_tempfromdate    date;
  v_temptodate      date;
  v_tempfromdatevar varchar2(8);
  v_temptodatevar   varchar2(8);
  v_numholiday      number(4, 0);
begin
  v_tempfromdate    := fromdate;
  v_temptodate      := todate;
  v_tempfromdatevar := to_char(v_tempfromdate, 'yyyymmdd');
  v_temptodatevar   := to_char(v_temptodate, 'yyyymmdd');

  if v_tempfromdatevar > v_temptodatevar then
    result := 0;
  else
    --计算之间的工作日
    v_numholiday := 0;
    --计入第一天
    select count(1)
      into v_numholiday
      from t_sys_holiday
     where isholiday = '0'
       and to_char(to_date(year||'-'||month||'-'||day,'yyyy-mm-dd'),'yyyymmdd') <= v_temptodatevar
       and to_char(to_date(year||'-'||month||'-'||day,'yyyy-mm-dd'),'yyyymmdd') >= v_tempfromdatevar;
    result := v_numholiday;

  end if;

  return(result);
end func_getworkdaynum;

附带依赖表结构，自己往里面插数据配置是否节假日标识t_sys_holiday

-- create table
create table t_sys_holiday
(
  id             number not null,
  year           varchar2(4) not null,
  month          varchar2(2) not null,
  day            number not null,
  isholiday      varchar2(1) default 0 not null,
  modifypersonid number,
  modifytime     date,
  others         varchar2(100),
  time           date
)
tablespace ftxzzf_new
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 768k
    next 1m
    minextents 1
    maxextents unlimited
  );
-- add comments to the columns 
comment on column t_sys_holiday.id
  is 'id主键';
comment on column t_sys_holiday.year
  is '年';
comment on column t_sys_holiday.month
  is '月';
comment on column t_sys_holiday.day
  is '日';
comment on column t_sys_holiday.isholiday
  is '是否节假日（1：节假日0：工作日）';
comment on column t_sys_holiday.modifypersonid
  is '修改人';
comment on column t_sys_holiday.modifytime
  is '修改时间';
comment on column t_sys_holiday.others
  is '其他';
comment on column t_sys_holiday.time
  is '时间';
-- create/recreate primary, unique and foreign key constraints 
alter table t_sys_holiday
  add constraint pk_holiday_id primary key (id)
  using index 
  tablespace ftxzzf_new
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 384k
    next 1m
    minextents 1
    maxextents unlimited
  );
alter table t_sys_holiday
  add constraint uk_holiday unique (year, month, day)
  using index 
  tablespace ftxzzf_new
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 704k
    next 1m
    minextents 1
    maxextents unlimited
  );