Hello,
I have written a LOOP that calls a second Stored Procedure.
In the second Stored Procedure I have 3 INSERTS each SELECT's values limited with a WHERE clause.
Example:
begin
dynsql:=INSERT INTO destinationtable (ID,DATE,NAME)
SELECT src.ID,src.DATE, dest.NAME FROM sourcetable src INNER JOIN destinationtable dest ON dest.ID=src.ID WHERE dest.date between '01.JAN.2012 and '03.JAN.2012' --if not condition not met, no insert
execute immediate dynsql;
dynsql:=UPDATE destinationtable SET DATE = dest.DATE FROM sourcetable src INNER JOIN destinationtable dest ON dest.ID=src.ID WHERE dest.date between '01.JAN.2012 and '03.JAN.2012'
execute immediate dynsql;
dynsql:=INSERT INTO destinationtable (ID,DATE,NAME)
SELECT src.ID,src.DATE, dest.NAME FROM sourcetable src INNER JOIN destinationtable dest ON dest.ID=src.ID WHERE dest.date between '01.APR.2012 and '01.MAY.2012' --if not condition not met, no insert
execute immediate dynsql
dynsql:=UPDATE destinationtable SET DATE = dest.DATE FROM sourcetable src INNER JOIN destinationtable dest ON dest.ID=src.ID WHERE dest.date between '01.APR.2012 and '01.MAY.2012'
execute immediate dynsql;
dynsql:=INSERT INTO destinationtable (ID,DATE,NAME)
SELECT src.ID,src.DATE, dest.NAME FROM sourcetable src INNER JOIN destinationtable dest ON dest.ID=src.ID WHERE dest.date between '01.JUN.2012 and '01.JUL.2012' --if not condition not met, no insert
execute immediate dynsql;
dynsql:=UPDATE destinationtable SET DATE = dest.DATE FROM sourcetable src INNER JOIN destinationtable dest ON dest.ID=src.ID WHERE dest.date between '01.JUN.2012 and '01.JUL.2012'
execute immediate dynsql;
end;
commit;
What do I do?
I update the existing "old" row with a new date and then I am basically duplicating that row but adding it with a new DATE, the ID and NAME is "copied" as it is.
The big problem is that the rows result in recursive updates. if the first statement updates a date it might make the conditions for the second and first update become "true".
Which is not what I want. Initially, only one of the statements is true, ever. The problem with my code is that after the first update and date setting, another "might" become true.
This should stop. After one of the updates occured, I need to stop the stored procedure.
How do I stop it after upate?