| 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
