Loop through date range and insert when no data found

Question

I have a table called calendar with only one column Date_I (type Date). Now the requirement is to create a plsql function insert_data which takes start_date and end_date. This function will now check if each and every date between start_date and end_date is presented in the table calendar. If not then insert that date into the table.

I know the requirement is quite unsual but it has to be like this way only. Following is the code I have written but I am wondering if there is a better way to do the same:

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;

The above code loops through each record from the table calendar and executes a select on it with that date..if no data found then it inserts the same in the table. This code is working but it is executing select no. of times and then insert(when required). I am now worried about the performance. Any suggestion would be of great help.

Thanks!

Answer 1

Try inserting values in to your table using MERGE

MERGE INTO calendar ca
     USING (SELECT *
              FROM calendar
             WHERE < your sql condition>
)qry ON (ca.<your_primary_key> = qry.<your_primary_key>)
WHEN NOT MATCHED THEN
  INSERT INTO calendar VALUES....

A great deal about performance is elucidated here

You could wrap merge statement in a procedure too.

Answer 2

As a more concrete example of the MERGE approach already suggested:

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;

It doesn't need to be a procedure at all, but that seems to be a requirement on its own. You can just run the merge as plain SQL, using your date range directly instead of through variables. (Or as bind variables, depending on how/where you're running this).

The USING clause is a generated table that creates all of the dates in the supplied range, using a common CONNECT BY method. The LEVEL pseudocolumn is similar to the loop you're trying to do; overall the inner query generates all dates in your range as an inline view, which you can then use to check against the actual table. The rest of the statement only inserts new records from that range if they don't already exit.

You could also do the same thing manually, and less efficiently, with a NOT EXISTS check:

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.

---

You have a few other issues in your procedure.

This is redundant because of the form of cursor-for loop you're using:

  cal_v        calendar%rowtype;

You have an unnecessary nested block here; it doesn't hurt I suppose but it isn't adding anything either. The first BEGIN, DECLARE and the first END can be removed (and the alignment is a bit off):

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

The outer loop, and the entire cursor, isn't needed; it actually means you're repeating the inner loop which actually does the work (or tries to, the first time anyway) as many times as there are existing records in the calendar table, which is pointless and slow:

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

The inner loop won't compile as you can't use dates for a range loop, only integers (giving PLS-00382):

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

The innermost select doesn't have an INTO; this won't compile either:

          SELECT * FROM calendar WHERE calc_v.calc_date = date_v;

The insert needs the value to be enclose in parentheses:

            INSERT INTO calendar VALUES date_v;

So if you really did want to do it this way you'd do something like:

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;

... but really, use merge.

Answer 3

Why are you using PL/SQL when you could do the same in SQL much efficiently?

Simply use MERGE INTO statement with only one clause, WHEN NOT MATCHED THEN INSERT.

For example,

MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

Answer 4

This should give you all dates in range that don't exist in your calendar table:

SELECT Date_I FROM (
  SELECT
    Start_Date + NUMTODSINTERVAL(n,'day') AS Date_I
  FROM (
    select level n
    from dual
    connect by level <= EXTRACT(DAY FROM Insert_Data.End_Date - Insert_Data.Start_Date)
  )) d 
WHERE NOT EXISTS (SELECT * FROM calendar WHERE Date_I = d.Date_I);

https://community.oracle.com/thread/2158102?start=0&tstart=0