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 );
如对本文有疑问, 点击进行留言回复!!
Oracle 基本概念 Dadabase,schema,user,table...
dbeaver连接Oracle中文乱码的解决方案--druid
Oracle常用语句(时间格式、去重、去特殊符号、查询表空间等)
Navicate 如何导出数据库中的存储过程、事件、视图等?
每日一记:Oracle升级2020年4月份的数据库补丁Database Patch Set Update : 11.2.0.4.200414 (30670774)
网友评论