sql - 遍历日期范围并在找不到数据时插入

Question

我有一个名为日历的表，其中只有一列 Date_I(日期类型)。现在的要求是创建一个 plsql 函数 insert_data，它接受 start_date 和 end_date。此函数现在将检查 start_date 和 end_date 之间的每个日期是否都显示在表格日历中。如果不是，则将该日期插入表中。

我知道这个要求很不正常，但只能这样。以下是我编写的代码，但我想知道是否有更好的方法来执行相同的操作:

PROCEDURE Insert_Data(Start_Date DATE, End_Date DATE)
  IS
  cal_v        calendar%rowtype;
  BEGIN
  DECLARE 
      CURSOR cal_c IS
      SELECT * 
      FROM Calendar;
  BEGIN
      FOR cal_v IN calc_c LOOP
       FOR date_v IN Insert_Data.Start_Date..Insert_Data.End_Date LOOP
        BEGIN
          SELECT * FROM calendar WHERE calc_v.calc_date = date_v;
        EXCEPTION 
          WHEN NO_DATA_FOUND THEN
            INSERT INTO calendar VALUES date_v;
        END;
       END LOOP;
      END LOOP;
  END;
END Insert_Data;

上面的代码循环遍历表格日历中的每条记录，并使用该日期对其执行选择。如果未找到数据，则将其插入表格中。此代码有效，但它正在执行 select no。次然后插入(需要时)。我现在担心性能。任何建议都会有很大帮助。

谢谢!

Answer 1

作为已建议的 MERGE 方法的更具体示例:

PROCEDURE Insert_Data(Start_Date DATE, End_Date DATE)
IS
BEGIN
  MERGE INTO calendar ca
  USING (
    SELECT Insert_Data.Start_Date + level - 1 as cal_date
    FROM dual
    CONNECT BY level <= Insert_Data.End_Date - Insert_Data.Start_Date + 1
  ) t
  ON (t.cal_date = ca.cal_date)
  WHEN NOT MATCHED THEN INSERT VALUES (t.cal_date);
END Insert_Data;

它根本不需要是一个过程，但这似乎是它自己的一个要求。您可以将合并作为普通 SQL 运行，直接使用您的日期范围而不是通过变量。 (或作为绑定(bind)变量，具体取决于您运行它的方式/位置)。

USING 子句是一个生成的表，它使用通用的 CONNECT BY 方法创建所提供范围内的所有日期。 The LEVEL pseudocolumn类似于您要执行的循环；总的来说，内部查询将您范围内的所有日期生成为内联 View ，然后您可以使用它来检查实际表。语句的其余部分仅插入该范围内的新记录(如果它们尚未退出)。

您也可以通过 NOT EXISTS 检查手动执行相同的操作，但效率较低:

PROCEDURE Insert_Data(Start_Date DATE, End_Date DATE)
IS
BEGIN
  INSERT INTO calendar
  WITH t AS (
    SELECT Insert_Data.Start_Date + level - 1 as cal_date
    FROM dual
    CONNECT BY level <= Insert_Data.End_Date - Insert_Data.Start_Date + 1
  )
  SELECT cal_date
  FROM t
  WHERE NOT EXISTS (
    SELECT 1
    FROM Calendar
    WHERE Calendar.cal_date = t.cal_date
  );
END Insert_Data;

SQL Fiddle .

---

您的程序中还有其他一些问题。

这是多余的，因为您正在使用 cursor-for 循环的形式:

  cal_v        calendar%rowtype;

这里有一个不必要的嵌套 block ；我想它没有伤害，但它也没有添加任何东西。第一个 BEGIN、DECLARE 和第一个 END 可以去掉(对齐有点偏离):

  BEGIN  -- remove
  DECLARE -- remove
      CURSOR cal_c IS
      SELECT * 
      FROM Calendar;
  BEGIN
...
  END; -- remove
END Insert_Data;

不需要外层循环和整个游标；它实际上意味着你重复内部循环，它实际上完成了工作(或尝试，无论如何，第一次)与日历表中现有记录的次数一样多，这是毫无意义且缓慢的:

      FOR cal_v IN calc_c LOOP
       FOR date_v IN Insert_Data.Start_Date..Insert_Data.End_Date LOOP
...
       END LOOP;
      END LOOP;

内部循环无法编译，因为您不能将日期用于范围循环，只能使用整数(给出 PLS-00382):

       FOR date_v IN Insert_Data.Start_Date..Insert_Data.End_Date LOOP

最里面的 select 没有 INTO；这也不会编译:

          SELECT * FROM calendar WHERE calc_v.calc_date = date_v;

插入需要将值括在括号中:

            INSERT INTO calendar VALUES date_v;

所以如果你真的想这样做，你会做类似的事情:

PROCEDURE Insert_Data(Start_Date DATE, End_Date DATE)
IS
  tmp_date DATE;
BEGIN
  FOR i IN 0..(Insert_Data.End_Date - Insert_Data.Start_Date) LOOP
  BEGIN
  dbms_output.put_line(i);
    SELECT cal_date INTO tmp_date FROM calendar
    WHERE cal_date = Insert_Data.Start_Date + i;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
      INSERT INTO calendar VALUES (Insert_Data.Start_Date + i);
  END;
  END LOOP;
END Insert_Data;

...但实际上，使用合并。