PL/SQL Collection [message #671004] |
Thu, 09 August 2018 04:33 |
|
RaShi.Raj
Messages: 84 Registered: May 2014 Location: Bangalore
|
Member |
|
|
Hi ,
I have some records in table tab_A and a PL/SQL table collection in a package as below.
ID FID PID SDATE
01 F01 P01 01/01/2017
02 F01 P02 01/02/2017
03 F01 P03 01/02/2017
And data in below PL/SQL table is
F01 P02 01/01/2017
F01 P05 01/01/2018
F01 P06 01/01/2017
When I loop through PL/SQL table, I need to delete from DB where PID is not present in PL/SQL table i.e. P01, P03 and insert P05 and P06 records into the table, P02 should remain as it is.
DECLARE
CURSOR c_tabA (x IN VARCHAR2) IS
SELECT *
FROM tab_A
WHERE FID = 'F01'
AND PID = x;
BEGIN
FOR i IN 1..v_tabA.count LOOP
FOR j in c_tabA (v_TabA(i).PID) LOOP
/*Record found..just do some updates */
END LOOP;
IF NOT RECORD FOUND THEN
DELETE FROM TABLE WHERE FID = 'F01' AND PID <> v_TabA(i).PID; --Delete any old records.
INSERT INTO TABLE tab_A;
END IF;
END LOOP;
END;
I have code similar to above. I can insert and update but not sure how to delete the records from tab_A that are not in PL/SQL collection. If I delete before inserting, in the 2nd loop previously inserted record is also getting deleted. Could you guys pls help?
Thanks,
Rashi
|
|
|
Re: PL/SQL Collection [message #671005 is a reply to message #671004] |
Thu, 09 August 2018 04:41 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
If you put the rows in your collection into a table or a subquery then can you use MERGE? Match the rows on PID, and use the INSERT and DELETE clauses of MERGE accordingly.
(By the way, I wish you would not say "record" when you mean "row").
|
|
|
|
|
|