Move date range and insert when no data found

I have a table called a calendar with one column Date_I (type Date). Now we need to create a plsql insert_data function that accepts start_date and end_date. This function now checks to see if the table has every date between start_date and end_date. If not, then insert this date into the table.

I know this requirement is completely incompatible, but it should be the same way. Below is the code I wrote, but I'm 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 iterates over each record from the table's calendar and selects over it with that date. If no data is found, then they insert it into the table. This code works, but it does select no. and then insert (if necessary). I'm worried about performance right now. Any suggestion would be very helpful.

Thank!

+3


source to share


4 answers


As a more specific example of an already suggested approach 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;

      

It doesn't have to be a procedure, but it appears to be a requirement itself. You can just run the merge as plain SQL using the date range directly rather than through variables. (Or as bind variables, depending on how / where you use it).

A proposal USING

is a generated table that creates all dates in the supplied range using a common method CONNECT BY

. A pseudo-columnLEVEL

is like the loop you are trying to do; a general inner query generates all dates in your range as an inline view, which can then be used to validate the actual table. The rest of the statement inserts new records from this range if they do not already leave.

You can also do the same manually and less efficiently with validation 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 .


You have a few more problems with your procedure.

This is redundant due to the shape of the cursor-for loop you are using:

  cal_v        calendar%rowtype;

      

Here you have an unnecessary nested block; it doesn't hurt, I suppose, but it doesn't add anything. The first BEGIN, DECLARE and first END can be removed (and bit alignment off):



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

      

The outer loop and entire cursor are unnecessary; this actually means that you are repeating the inner loop which actually does the job (or tries for the first time anyway) as many times as there are existing entries 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 will not compile as you cannot use dates for the range loop, only integers (giving PLS-00382):

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

      

The innermost choice has no INTO; this will not compile either:

          SELECT * FROM calendar WHERE calc_v.calc_date = date_v;

      

Insertion requires a value, which must be enclosed in parentheses:

            INSERT INTO calendar VALUES date_v;

      

So, if you really wanted to do this, you would 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 actually use merge.

+2


source


Try to insert values ​​into 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....

      



Much of the performance is demonstrated here

You can also wrap a merge statement in a procedure.

+1


source


Why are you using PL / SQL when you can do the same thing in SQL much more efficiently?

Just use the operator MERGE INTO

with just one sentence 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);

      

0


source


This should give you all the dates in the range that are not 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

0


source







All Articles