获得一个日期在当周是否有节日并返回日期-优快云博客

本文介绍了一个Oracle数据库函数，用于查询给定日期所在周内的节假日。通过该函数可以获取指定日期所在周的所有节假日列表。

函数：

CREATE OR REPLACE FUNCTION FUN_GET_HOLIDAY (TEMDATE VARCHAR2)
   RETURN VARCHAR2
IS
   holiday_ALL   VARCHAR2 (200);

   CURSOR c_date
   IS
      SELECT RQ
        FROM CHINESECALENDAR
       WHERE     TO_DATE (RQ, 'YYYY-MM-DD') 
        --查询输入日期所在日期的这周的起止日期--
           BETWEEN TRUNC ( TO_DATE (TEMDATE,'yyyy-mm-dd'),'iw') AND   TRUNC (TO_DATE (TEMDATE,'yyyy-mm-dd'),'iw') + 6
             AND JR IS NOT NULL;

   c_row         c_date%ROWTYPE;
BEGIN
   holiday_ALL := '';

   OPEN c_date;

   LOOP
      FETCH c_date INTO c_row;

      EXIT WHEN c_date%NOTFOUND;

      holiday_ALL := holiday_ALL || ',' || c_row.rq;
   END LOOP;

   CLOSE c_date;

   RETURN holiday_ALL;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN -1;
END FUN_GET_HOLIDAY;
/

--调用方法--

SELECT FUN_GET_HOLIDAY('2015-08-05') FROM dual;

--执行结果--

FUN_GET_HOLIDAY('2015-08-05')

,2015-08-08,2015-08-09

获得一个日期的这周的开始日期和结束日期

      SELECT RQ
        FROM CHINESECALENDAR
       WHERE     
       TO_DATE (RQ, 'YYYY-MM-DD') BETWEEN trunc(date'2015-08-05','iw') AND trunc(date'2015-08-05','iw')+6;

--执行结果--

2015-08-03
2015-08-04
2015-08-05
2015-08-06
2015-08-07
2015-08-08
2015-08-09