Oracle计算当前日期在N个工作日之后的日期

最近项目中有一个自动扣款的需求，需要在商品出售之后 7 个工作日之后进行自动扣款，由此封装了一个存储过程以便于调用。

注意：本示例使用oracle。

一、创建一张GGHOLIDAY表并insert部分数据

①表中holiday字段长度为31，holiday字段：W-正常周末 H-全天假期 D-半天假期 X-这个月不存在这天 .-正常工作日'。

-- Create tablecreate table GGHOLIDAY(idVARCHAR2(32) not null,year VARCHAR2(12),month VARCHAR2(12),holidayVARCHAR2(31),creatorcode VARCHAR2(10),createtime DATE,updatercode VARCHAR2(10),updatetime DATE,validdate DATE,invaliddate DATE,validind VARCHAR2(1) not null,remarkVARCHAR2(4000))tablespace USERSpctfree 10initrans 1maxtrans 255storage(initial 64Knext 1Mminextents 1maxextents unlimited);-- Add comments to the table comment on table GGHOLIDAYis '节假日';-- Add comments to the columns comment on column GGHOLIDAY.idis 'id';comment on column GGHOLIDAY.yearis '年份';comment on column GGHOLIDAY.monthis '月份';comment on column GGHOLIDAY.holidayis '节假日标志，31位代表31天 格式： W-正常周末 H-全天假期 D-半天假期 X-这个月不存在这天 .-正常工作日';comment on column GGHOLIDAY.creatorcodeis '创建人';comment on column GGHOLIDAY.createtimeis '创建时间';comment on column GGHOLIDAY.updatercodeis '最后修改人';comment on column GGHOLIDAY.updatetimeis '最后修改时间';comment on column GGHOLIDAY.validdateis '生效日期';comment on column GGHOLIDAY.invaliddateis '失效日期';comment on column GGHOLIDAY.validindis '有效标志含义:1-有效；0-无效';comment on column GGHOLIDAY.remarkis '备注';-- Create/Recreate primary, unique and foreign key constraints alter table GGHOLIDAYadd constraint PK_GGHOLIDAY primary key (ID)using index tablespace USERSpctfree 10initrans 2maxtrans 255storage(initial 512Knext 512Kminextents 1maxextents unlimited);

②在GGHOLIDAY表中添加8月、9月、10月、11月、12月的节假日信息。若需更多，自行配置。

insert into ggholiday (ID, YEAR, MONTH, HOLIDAY, CREATORCODE, CREATETIME, UPDATERCODE, UPDATETIME, VALIDDATE, INVALIDDATE, VALIDIND, REMARK)values ('4028d0816980aba8016980c355ef0011', '', '08', '..WW.....WW.....WW.....WW.....W', '100000001', to_date('15-03- 17:51:00', 'dd-mm-yyyy hh24:mi:ss'), '100000001', to_date('15-03- 17:51:00', 'dd-mm-yyyy hh24:mi:ss'), null, null, '1', null);insert into ggholiday (ID, YEAR, MONTH, HOLIDAY, CREATORCODE, CREATETIME, UPDATERCODE, UPDATETIME, VALIDDATE, INVALIDDATE, VALIDIND, REMARK)values ('4028d0816980aba8016980c355ef0010', '', '09', 'W.....WW....HHH.....WW.....W..X', '100000001', to_date('15-03- 17:51:00', 'dd-mm-yyyy hh24:mi:ss'), '100000001', to_date('15-03- 17:51:00', 'dd-mm-yyyy hh24:mi:ss'), null, null, '1', null);insert into ggholiday (ID, YEAR, MONTH, HOLIDAY, CREATORCODE, CREATETIME, UPDATERCODE, UPDATETIME, VALIDDATE, INVALIDDATE, VALIDIND, REMARK)values ('4028d0816980aba8016980c355ef0001', '', '10', 'HHHHHHH.....W.....WW.....WW....', '100000001', to_date('15-03- 17:51:00', 'dd-mm-yyyy hh24:mi:ss'), '100000001', to_date('15-03- 17:51:00', 'dd-mm-yyyy hh24:mi:ss'), null, null, '1', null);insert into ggholiday (ID, YEAR, MONTH, HOLIDAY, CREATORCODE, CREATETIME, UPDATERCODE, UPDATETIME, VALIDDATE, INVALIDDATE, VALIDIND, REMARK)values ('4028d0816980aba8016980c355ef0012', '', '11', '.WW.....WW.....WW.....WW.....WX', '100000001', to_date('15-03- 17:51:00', 'dd-mm-yyyy hh24:mi:ss'), '100000001', to_date('15-03- 17:51:00', 'dd-mm-yyyy hh24:mi:ss'), null, null, '1', null);insert into ggholiday (ID, YEAR, MONTH, HOLIDAY, CREATORCODE, CREATETIME, UPDATERCODE, UPDATETIME, VALIDDATE, INVALIDDATE, VALIDIND, REMARK)values ('4028d0816980aba8016980c355ef0013', '', '12', 'W.....WW.....WW.....WW.....WW..', '100000001', to_date('15-03- 17:51:00', 'dd-mm-yyyy hh24:mi:ss'), '100000001', to_date('15-03- 17:51:00', 'dd-mm-yyyy hh24:mi:ss'), null, null, '1', null);

二、编写存储过程计算节假日：

1、计算某个日期N 个工作日之后的日期：

代码：

-- 计算传入日期之后v_days个工作日之后的日期，不支持跨2个月的操作FUNCTION getWorkDate(v_nowDatestr VARCHAR2, v_days NUMBER) RETURN DATE ISv_returnDate DATE;v_nowDate DATE; --当前时间v_strNowMonthHOLIDAY VARCHAR2(31); -- 当月v_nowDate后的节假日及工作日标识字符串v_strNextMonthHOLIDAY VARCHAR2(31); -- 下月节假日及工作日字符串v_nextDays NUMBER; -- 下月需要计算的工作日v_nowDays NUMBER; -- 当前是本月第几天v_nowMonthWorkDay NUMBER; -- 当月剩余工作日天数v_realyDays NUMBER DEFAULT 0; -- v_nowDate到v_days个工作日的实际天数v_workDaysFlag NUMBER DEFAULT 0;-- 循环时记录这是第几个工作日BEGINv_nowDate := to_date(v_nowDatestr,'yyyy-MM-dd');v_nowDays := to_number(to_char(v_nowdate,'dd'));-- 获取当月v_nowDate日期之后的节假日及工作日标识字符串SELECT replace(substr(T.HOLIDAY,v_nowDays+1,31-v_nowDays),'X','')INTO V_STRNOWMONTHHOLIDAYFROM GGHOLIDAY TWHERE T.YEAR = TO_CHAR(V_NOWDATE, 'yyyy')AND T.MONTH = TO_CHAR(V_NOWDATE, 'MM');SELECT nvl((LENGTHB(V_STRNOWMONTHHOLIDAY) -LENGTHB(REPLACE(V_STRNOWMONTHHOLIDAY, '.', ''))),0)INTO v_nowMonthWorkDayFROM DUAL;IF v_nowMonthWorkDay >= v_days THEN-- 如果当月v_nowDate日期之后的工作日还多于v_days天FOR i IN 1..length(v_strNowMonthHOLIDAY) LOOPIF substr(v_strNowMonthHOLIDAY,i,1)='.' THENv_workDaysFlag := v_workDaysFlag + 1;END IF;v_realyDays := v_realyDays + 1;IF v_workDaysFlag = v_days THENGOTO NEXT;END IF;END LOOP;<<NEXT>> NULL;ELSE-- 如果当月v_nowDate日期之后的工作日还少于或等于v_days天，就需要推迟到下个月v_nextDays := v_days-v_nowMonthWorkDay;--v_realyDays:= v_nextDays;-- 获取下月节假日及工作日标识字符串SELECT replace(T.HOLIDAY,'X','')INTO v_strNextMonthHOLIDAYFROM GGHOLIDAY TWHERE T.YEAR = TO_CHAR(add_months(v_nowDate,1), 'yyyy')AND T.MONTH = TO_CHAR(add_months(v_nowDate,1), 'MM');FOR i IN 1..length(v_strNextMonthHOLIDAY) LOOPIF substr(v_strNextMonthHOLIDAY,i,1)='.' THENv_workDaysFlag := v_workDaysFlag + 1;END IF;v_realyDays := v_realyDays + 1;IF v_workDaysFlag = v_nextDays THENGOTO NEXTSTR;END IF;END LOOP;<<NEXTSTR>> NULL;if v_strNowMonthHOLIDAY is null then v_realyDays := v_realyDays;elsev_realyDays := v_realyDays + length(v_strNowMonthHOLIDAY);end if;END IF;v_returnDate := v_nowDate + v_realyDays;RETURN v_returnDate;END getWorkDate;

2、计算某个日期之前 N 个工作日的日期：

-- 获取 V_NOWDATEstr 日期前 V_DAYS 个工作日的日期PROCEDURE GETBEFOREWORKDATE(V_NOWDATEstr IN VARCHAR2,V_DAYS IN NUMBER,V_MESSAGECODE OUT VARCHAR2,V_RETURNDATE OUT DATE) ISv_nowDate DATE;v_strNowMonthHOLIDAY VARCHAR2(31); -- 当月v_nowDate前的节假日及工作日标识字符串v_strLastMonthHOLIDAY VARCHAR2(31); -- 上月节假日及工作日字符串v_lastDays NUMBER; -- 上月需要计算的工作日v_nowDays NUMBER; -- 当前是本月第几天v_nowMonthWorkDay NUMBER; -- 当月剩余工作日天数v_realyDays NUMBER DEFAULT 0; -- v_nowDate到v_days个工作日的实际天数v_workDaysFlag NUMBER DEFAULT 0;-- 循环时记录这是第几个工作日BEGINv_nowDate := to_date(v_nowDatestr,'yyyy-MM-dd');v_nowDays := to_number(to_char(v_nowdate,'dd'))-1;-- 获取当月v_nowDate日期之前的节假日及工作日标识字符串SELECT replace(substr(T.HOLIDAY,0,v_nowDays),'X','')INTO V_STRNOWMONTHHOLIDAYFROM GGHOLIDAY TWHERE T.YEAR = TO_CHAR(V_NOWDATE, 'yyyy')AND T.MONTH = TO_CHAR(V_NOWDATE, 'MM');SELECT (LENGTHB(V_STRNOWMONTHHOLIDAY) -LENGTHB(REPLACE(V_STRNOWMONTHHOLIDAY, '.', '')))INTO v_nowMonthWorkDayFROM DUAL;IF v_nowMonthWorkDay >= v_days THEN-- 如果当月v_nowDate日期之后的工作日还多于v_days天FOR i IN 1..length(v_strNowMonthHOLIDAY) LOOPIF substr(v_strNowMonthHOLIDAY,length(v_strNowMonthHOLIDAY)-i+1,1)='.' THENv_workDaysFlag := v_workDaysFlag + 1;END IF;v_realyDays := v_realyDays + 1;IF v_workDaysFlag = v_days THENGOTO NEXT;END IF;END LOOP;<<NEXT>> NULL;ELSE-- 如果当月v_nowDate日期之后的工作日还少于或等于v_days天，就需要推迟到下个月v_lastDays := v_days-v_nowMonthWorkDay;-- 获取上月节假日及工作日标识字符串SELECT replace(T.HOLIDAY,'X','')INTO v_strLastMonthHOLIDAYFROM GGHOLIDAY TWHERE T.YEAR = TO_CHAR(v_nowDate - interval '1' month, 'yyyy')AND T.MONTH = TO_CHAR(v_nowDate - interval '1' month, 'MM');FOR i IN 1..length(v_strLastMonthHOLIDAY) LOOPIF substr(v_strLastMonthHOLIDAY,length(v_strLastMonthHOLIDAY)-i+1,1)='.' THENv_workDaysFlag := v_workDaysFlag + 1;END IF;v_realyDays := v_realyDays + 1;IF v_workDaysFlag = v_lastDays THENGOTO NEXTSTR;END IF;END LOOP;<<NEXTSTR>> NULL;v_realyDays := v_realyDays + length(v_strNowMonthHOLIDAY);END IF;v_returnDate := v_nowDate - v_realyDays;EXCEPTIONWHEN OTHERS THENV_MESSAGECODE := '000';END GETBEFOREWORKDATE;

三、进行测试

1、计算某个日期N 个工作日之后的日期：

SELECT ggHolidayPackage.getWorkDate('-09-21',3) FROM dual;输出：/9/25

2、计算某个日期之前 N 个工作日的日期：

DECLAREV_MESSAGECODE VARCHAR2(10);V_RETURNDATE DATE;BEGINggHolidayPackage.GETBEFOREWORKDATE('-09-21',3,V_MESSAGECODE,V_RETURNDATE);dbms_output.put_line('V_MESSAGECODE='||V_MESSAGECODE);dbms_output.put_line('V_RETURNDATE='||to_char(V_RETURNDATE,'yyyy-MM-dd'));END;输出：V_MESSAGECODE=V_RETURNDATE=-09-18