CREATE OR REPLACE FUNCTION GETWORKDAYS
( begindate IN varchar,
enddate IN varchar)
RETURN varchar IS
nextworkday VARCHAR (20 BYTE);
p_holiday VARCHAR (20 BYTE);
tmpvar NUMBER;
tmpchar VARCHAR (5 BYTE);
days NUMBER;
start_day DATE;
tmp_day DATE;
BEGIN
tmpchar := TO_CHAR (SYSDATE, 'mm');
IF tmpchar = '12'
--本月是否为12月
THEN
--节假日为元旦
p_holiday := 'legalday_1';
ELSE
--取最近的节假日记录
SELECT a1
INTO p_holiday
FROM (SELECT a.param_id AS a1,
MIN (ABS ( SYSDATE
- TO_DATE (SUBSTR (a.param_value, 1, 5),
'mm-dd'
)
)
)
FROM t_gl_config a
WHERE a.param_id LIKE 'legalday%'
GROUP BY a.param_id
ORDER BY 2)
WHERE ROWNUM = 1;
END IF;
--定义最近节假日的放假开始日期
SELECT SUBSTR (param_value, 1, 2) || SUBSTR (param_value, 4, 2),
TO_NUMBER (SUBSTR (param_value, 7))
INTO tmpchar,
days
FROM t_gl_config c
WHERE c.param_id = p_holiday;
IF tmpchar = '0101'
--节假日是否元旦
THEN
start_day :=
TO_DATE ((TO_CHAR (SYSDATE, 'yyyy') + 1) || tmpchar,
'yyyymmdd');
ELSE
start_day := TO_DATE (tmpchar, 'mmdd');
END IF;
tmpvar := SYSDATE - start_day;
IF tmpvar > -1 AND tmpvar < days
--判断是否是节假日的前一天或者是否在节假日中
THEN
nextworkday := TO_CHAR (start_day + days, 'yyyy-mm-dd');
ELSE
--判断今天是星期几
SELECT 7 - (NEXT_DAY (SYSDATE, 1) - SYSDATE)
INTO tmpvar
FROM DUAL;
IF tmpvar = 5 OR tmpvar = 6
THEN
--如果是周五或周六,则下一个工作日为下周一
SELECT NEXT_DAY (SYSDATE, 2)
INTO tmp_day
FROM DUAL;
ELSE
--否则,是第二天
SELECT SYSDATE + 1
INTO tmp_day
FROM DUAL;
END IF;
tmpvar := tmp_day - start_day;
IF tmpvar > 0 AND tmpvar < days
--判断下一个工作日是否在节假日中
THEN
nextworkday := TO_CHAR ((SYSDATE + 1), 'yyyy-mm-dd');
ELSE
nextworkday := TO_CHAR (tmp_day, 'yyyy-mm-dd');
END IF;
END IF;
RETURN nextworkday;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END;
( begindate IN varchar,
enddate IN varchar)
RETURN varchar IS
nextworkday VARCHAR (20 BYTE);
p_holiday VARCHAR (20 BYTE);
tmpvar NUMBER;
tmpchar VARCHAR (5 BYTE);
days NUMBER;
start_day DATE;
tmp_day DATE;
BEGIN
tmpchar := TO_CHAR (SYSDATE, 'mm');
IF tmpchar = '12'
--本月是否为12月
THEN
--节假日为元旦
p_holiday := 'legalday_1';
ELSE
--取最近的节假日记录
SELECT a1
INTO p_holiday
FROM (SELECT a.param_id AS a1,
MIN (ABS ( SYSDATE
- TO_DATE (SUBSTR (a.param_value, 1, 5),
'mm-dd'
)
)
)
FROM t_gl_config a
WHERE a.param_id LIKE 'legalday%'
GROUP BY a.param_id
ORDER BY 2)
WHERE ROWNUM = 1;
END IF;
--定义最近节假日的放假开始日期
SELECT SUBSTR (param_value, 1, 2) || SUBSTR (param_value, 4, 2),
TO_NUMBER (SUBSTR (param_value, 7))
INTO tmpchar,
days
FROM t_gl_config c
WHERE c.param_id = p_holiday;
IF tmpchar = '0101'
--节假日是否元旦
THEN
start_day :=
TO_DATE ((TO_CHAR (SYSDATE, 'yyyy') + 1) || tmpchar,
'yyyymmdd');
ELSE
start_day := TO_DATE (tmpchar, 'mmdd');
END IF;
tmpvar := SYSDATE - start_day;
IF tmpvar > -1 AND tmpvar < days
--判断是否是节假日的前一天或者是否在节假日中
THEN
nextworkday := TO_CHAR (start_day + days, 'yyyy-mm-dd');
ELSE
--判断今天是星期几
SELECT 7 - (NEXT_DAY (SYSDATE, 1) - SYSDATE)
INTO tmpvar
FROM DUAL;
IF tmpvar = 5 OR tmpvar = 6
THEN
--如果是周五或周六,则下一个工作日为下周一
SELECT NEXT_DAY (SYSDATE, 2)
INTO tmp_day
FROM DUAL;
ELSE
--否则,是第二天
SELECT SYSDATE + 1
INTO tmp_day
FROM DUAL;
END IF;
tmpvar := tmp_day - start_day;
IF tmpvar > 0 AND tmpvar < days
--判断下一个工作日是否在节假日中
THEN
nextworkday := TO_CHAR ((SYSDATE + 1), 'yyyy-mm-dd');
ELSE
nextworkday := TO_CHAR (tmp_day, 'yyyy-mm-dd');
END IF;
END IF;
RETURN nextworkday;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END;