PL/SQL日期处理存储过程需求及代码问题咨询

需求说明 #

我编写了一个接收起始日期和结束日期两个参数（示例：2022-03-01 至 2022-03-12）的存储过程，需求如下：

• 若尝试插入已存在的日期，不写入数据表，仅通过DBMS_OUTPUT.PUT_LINE输出错误提示；
• 当输入的日期范围包含表中未存在的日期时（示例：2022-01-01 至 2022-01-15），需根据输入日期与现有日期的大小关系，将表填充至已有最早或最晚日期。

原尝试代码 #

CREATE OR replace PROCEDURE P_dates (p_start  DATE,
                                     p_finish DATE)
IS
  V_DATES DATE := p_start;
  v_exists   NUMBER;
  v_first    DATE;
  v_last     DATE;
BEGIN
  SELECT Min(v_dates)
  INTO   v_first
  FROM   table1;
  
  SELECT Max(v_dates)
  INTO   v_last
  FROM   table1;
  
  SELECT Count(*)
  INTO   v_exists
  FROM   table1
  WHERE  v_dates = v_dates;
  
  IF p_finish < v_dates THEN
    dbms_output.Put_line('You can not do this.');
  ELSE
    WHILE v_dates <= p_finish
    LOOP
      
      IF v_exists 0 THEN
        dbms_output.put_line('Date '
        || to_char(p_start ,'YYYY-MM-DD')
        ||' is already in the table.');
      ELSE
        INSERT INTO table1
                    (
                                id_dates,
                                dates,
                                year,
                                quartal ,
                                month
                    )
                    VALUES
                    (
                                to_char(v_dates, 'yyyymmdd'),
                                v_dates ,
                                extract(year FROM v_dates) ,
                                CASE
                                            WHEN extract(month FROM v_dates) IN (1,2,3) THEN 'Q1'
                                            WHEN extract(month FROM v_dates) IN (4,5,6) THEN 'Q2'
                                            WHEN extract(month FROM v_dates) IN (7,8,9) THEN 'Q3'
                                            WHEN extract(month FROM v_dates) IN (10,11,12) THEN 'Q4'
                                END ,
                                extract(month FROM v_dates) ,
                                to_char(v_datum, 'Month')
                    );
        
        v_dates := v_dates + 1;
        IF p_start < v_first p_dates(p_start, v_first - 1);
        ELSIF p_finish v_last THEN
          p_dates(v_last + 1, p_finish);
        END IF;
      END IF;
    END LOOP;
  END IF;
END; 

原代码问题分析 #

1. 变量混淆：查询表中最小/最大日期时错误使用局部变量v_dates作为字段名，应使用表实际日期字段（如dates）；
2. 重复判断逻辑失效：WHERE v_dates = v_dates永远返回全表行数，无法准确判断当前日期是否存在；
3. 语法错误：存在IF v_exists 0 THEN（缺比较运算符）、IF p_start < v_first p_dates(...)（缺THEN）、ELSIF p_finish v_last（缺比较运算符）等语法问题；
4. 递归调用时机错误：递归放在插入逻辑内会导致循环混乱；
5. 字段不匹配：INSERT语句多写了未定义变量v_datum的处理，且表字段列表无对应项；
6. 空表未处理：表为空时SELECT Min/Max会抛出NO_DATA_FOUND异常。

修正后的存储过程 #

CREATE OR REPLACE PROCEDURE P_dates (p_start  DATE,
                                     p_finish DATE)
IS
  v_current_date DATE := p_start;
  v_exists       NUMBER;
  v_first_date   DATE;
  v_last_date    DATE;
BEGIN
  -- 处理空表情况：表为空时直接用输入范围作为初始边界
  BEGIN
    SELECT MIN(dates), MAX(dates)
    INTO   v_first_date, v_last_date
    FROM   table1;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_first_date := p_start;
      v_last_date := p_finish;
  END;

  -- 填充输入起始到表最早日期的空缺（如果输入起始更早）
  IF p_start < v_first_date THEN
    P_dates(p_start, v_first_date - 1);
  END IF;

  -- 填充表最晚日期到输入结束的空缺（如果输入结束更晚）
  IF p_finish > v_last_date THEN
    P_dates(v_last_date + 1, p_finish);
  END IF;

  -- 遍历输入范围，处理每个日期的插入逻辑
  WHILE v_current_date <= p_finish LOOP
    -- 检查当前日期是否已存在
    SELECT COUNT(*)
    INTO   v_exists
    FROM   table1
    WHERE  dates = v_current_date;

    IF v_exists > 0 THEN
      DBMS_OUTPUT.PUT_LINE('日期 ' || TO_CHAR(v_current_date, 'YYYY-MM-DD') || ' 已存在于表中，跳过插入。');
    ELSE
      -- 插入新日期，自动计算年、季度、月份
      INSERT INTO table1 (id_dates, dates, year, quartal, month)
      VALUES (
        TO_CHAR(v_current_date, 'YYYYMMDD'),
        v_current_date,
        EXTRACT(YEAR FROM v_current_date),
        CASE
          WHEN EXTRACT(MONTH FROM v_current_date) IN (1,2,3) THEN 'Q1'
          WHEN EXTRACT(MONTH FROM v_current_date) IN (4,5,6) THEN 'Q2'
          WHEN EXTRACT(MONTH FROM v_current_date) IN (7,8,9) THEN 'Q3'
          WHEN EXTRACT(MONTH FROM v_current_date) IN (10,11,12) THEN 'Q4'
        END,
        EXTRACT(MONTH FROM v_current_date)
      );
    END IF;

    v_current_date := v_current_date + 1;
  END LOOP;
END;
/

修正说明 #

• 新增空表异常处理，避免表为空时抛出错误；
• 调整递归调用时机，先完成边界填充再处理输入范围；
• 修复日期存在性判断逻辑，针对当前循环日期做精准查询；
• 修正所有语法错误，移除多余字段插入项；
• 错误提示明确指向当前处理日期，提升可读性。

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