PL/SQL函数计算日期天数差 处理相邻行衔接与空值场景

核心思路 #

不需要手动在循环里缓存前后行字段值，直接用Oracle内置窗口函数LEAD()即可按排序规则取到当前行的下一行指定字段值，完全匹配相邻行比对需求，配合CASE表达式直接套用三条计算规则即可，性能远高于手动逐行缓存的循环写法。

规则对应逻辑 #

• 常规无衔接场景：(until_date - from_date) + 1，截止日期当天计入
• 相邻行衔接场景：下一行from_date等于当前行until_date时，计算until_date - from_date，避免衔接日期重复计数
• 空截止日期场景：until_date为NULL时，计算sysdate - from_date

直接查询实现 #

直接执行SQL即可拿到每行对应的天数差，替换表名为你实际的数据集表名即可：

SELECT
  from_date,
  until_date,
  CASE
    WHEN until_date IS NULL THEN TRUNC(SYSDATE) - TRUNC(from_date)
    WHEN LEAD(TRUNC(from_date)) OVER (ORDER BY from_date) = TRUNC(until_date) THEN TRUNC(until_date) - TRUNC(from_date)
    ELSE TRUNC(until_date) - TRUNC(from_date) + 1
  END AS diff_days
FROM your_date_table;

用你给出的样例数据执行，非空行计算结果分别为62、22、70、11、4、4，符合规则要求。

封装为PL/SQL函数 #

基于窗口函数的简洁版本 #

CREATE OR REPLACE FUNCTION get_total_date_diff RETURN NUMBER
IS
  v_result NUMBER := 0;
BEGIN
  SELECT SUM(
    CASE
      WHEN until_date IS NULL THEN TRUNC(SYSDATE) - TRUNC(from_date)
      WHEN LEAD(TRUNC(from_date)) OVER (ORDER BY from_date) = TRUNC(until_date) THEN TRUNC(until_date) - TRUNC(from_date)
      ELSE TRUNC(until_date) - TRUNC(from_date) + 1
    END
  ) INTO v_result
  FROM your_date_table;
  RETURN v_result;
END;
/

显式循环版本（适配必须用循环处理的场景） #

如果业务逻辑强制要求循环逐行处理，直接在游标定义阶段用LEAD()把下一行起始日期查出来，循环时直接取值即可，不需要额外手动维护前后行变量：

CREATE OR REPLACE FUNCTION get_total_date_diff_loop RETURN NUMBER
IS
  v_total NUMBER := 0;
  CURSOR cur_date_data IS
    SELECT
      TRUNC(from_date) from_d,
      TRUNC(until_date) until_d,
      LEAD(TRUNC(from_date)) OVER (ORDER BY from_date) next_from_d
    FROM your_date_table;
BEGIN
  FOR rec IN cur_date_data LOOP
    IF rec.until_d IS NULL THEN
      v_total := v_total + TRUNC(SYSDATE) - rec.from_d;
    ELSIF rec.next_from_d = rec.until_d THEN
      v_total := v_total + rec.until_d - rec.from_d;
    ELSE
      v_total := v_total + rec.until_d - rec.from_d + 1;
    END IF;
  END LOOP;
  RETURN v_total;
END;
/

注意：所有日期字段计算前加TRUNC()是为了规避带时分秒的DATE类型计算出小数天数的问题，如果你的日期字段本身只存年月日，可以省略该函数。

内容的提问来源于stack exchange，提问作者BOMBERMAN