sql - 优化SQL日期函数

Question

我编写了一个函数 (Oracle SQL)，用于根据不同国家/地区的日历计算两个日期之间的工作日数。它需要运行数千次，因此速度非常重要。您输入开始日期、结束日期和给定日历的名称，例如“美国”、“英国”等。输入参数在不同的 SQL 中传递，但现在我仍在测试中。该函数使用假日表来查找各天之间的非工作日。该表包含不同国家的所有周末和国定假日。问题是，尽管使用了游标，该函数还是相当慢。有没有人有任何关于如何使该功能更快的提示？我怀疑函数中的选择查询是问题所在。我可能是错的，但我不知道该怎么办。

    CREATE OR REPLACE FUNCTION Test (in_dt1         DATE,
                                                in_dt2         DATE,
                                                in_Calender    VARCHAR)
    RETURN NUMBER
    IS
    Count_days    NUMBER := 0;
    beg_dt        DATE;
    End_dt        DATE;
    Curr_dt       DATE;
    Cursor_dato   DATE;

    CURSOR C1
    IS
      SELECT b.holiday
        FROM calendars a
             INNER JOIN holidays b ON a.calik = b.calik
       WHERE a.cal = in_Calender AND holiday >= in_dt1 AND holiday <= in_dt2;
    BEGIN
      OPEN c1;

   beg_dt := LEAST (in_dt1, in_dt2);
   End_dt := GREATEST (in_dt1, in_dt2);

   Curr_dt := beg_dt + 1;

  <<OUTER>>
   WHILE Curr_dt <= End_dt
   LOOP
      FETCH c1 INTO cursor_dato;

      WHILE cursor_dato IS NULL
      LOOP
         Count_days := Count_days + 1;
         Curr_dt := Curr_dt + 1;
         EXIT OUTER WHEN Curr_dt = End_dt + 1;
      END LOOP;

      WHILE Curr_dt < cursor_dato
      LOOP
         Count_days := Count_days + 1;
         Curr_dt := Curr_dt + 1;
         EXIT OUTER WHEN Curr_dt = End_dt;
      END LOOP;

      IF Curr_dt > cursor_dato
      THEN
         Count_days := Count_days + 1;
      END IF;

      Curr_dt := Curr_dt + 1;
   END LOOP;

   CLOSE c1;

   RETURN Count_days;
END;

Answer 1

select trunc (end_dt) - trunc(beg_dt) from dual; 

给出两个日期之间的天数。

select count(1) from holidays where holiday >= beg_dt AND holiday <= end_dt;

为您提供两个日期之间的假期数。

第一 - 第二给你结果。没有游标、循环等。

所以类似:

    CREATE OR REPLACE FUNCTION Test (in_dt1         DATE,
                                     in_dt2         DATE,
                                     in_Calender    VARCHAR2,
                                     p_country      varchar2)
    RETURN NUMBER
    IS
      Count_days    NUMBER := 0;
      beg_dt        DATE;
      End_dt        DATE;
      count_holidays number := 0;
    begin

      beg_dt := LEAST (in_dt1, in_dt2);
      End_dt := GREATEST (in_dt1, in_dt2);

      count_days := trunc(end_dt) - trunc(beg_dt);

      select count(1) 
      into count_holidays 
      from holidays 
      where holiday >= beg_dt
      AND holiday <= end_dt
      and country = p_country;

      return Count_days - count_holidays;
    end;

我添加了国家/地区参数来计算适当的假期。