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!
source to share
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;
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.
source to share
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.
source to share
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);
source to share
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
source to share