Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Collection (Oracle 11G)
PL/SQL Collection [message #671004] Thu, 09 August 2018 04:33 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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").
Re: PL/SQL Collection [message #671007 is a reply to message #671004] Thu, 09 August 2018 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Align the columns in result.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And thank people who spent hours to (try to) help you (see your previous topics).

Re: PL/SQL Collection [message #671010 is a reply to message #671007] Thu, 09 August 2018 05:55 Go to previous messageGo to next message
RaShi.Raj
Messages: 84
Registered: May 2014
Location: Bangalore
Member
Thanks Michel, will follow what you said from now on.

John, thanks for the reply. I will explore your option too. As of now I'm constructing sql statement to list all PID's that should be not be deleted from table and using this in DELETE statement NOT IN parameter. Seems to be working fine.
Re: PL/SQL Collection [message #671013 is a reply to message #671010] Thu, 09 August 2018 06:43 Go to previous message
poojagarg
Messages: 1
Registered: August 2018
Junior Member
I agree with Michel discussion.
Previous Topic: Get Record Count
Next Topic: Spool Japanese Characters
Goto Forum:
  


Current Time: Thu Mar 28 04:54:31 CDT 2024