PL/SQL Using cursor to loop thorough records to determine if records needs to be updated, inserted or deleted from multiple tables

I am not sure how to create a procedure using a cursor to create a loop where records are check to possibly be updated, deleted or inserted into multiple tables.  

I have a Daily updated table (A) that is truncated and recreate every day with the lastest data.   I have a parts pricing table (B) that contains a vehicle Identification number with every pricing record.  The vehicle identification number (VIN)  is not unique in this table as a VIN can have multiple records in this table.  I have another table where the VIN is unique in the table.  This table contains various information about the Vehicle and other attributes (table C) .  
So the relationship between table C and table B is one to many.   (only one record in C may return multiple rows in B).

If a record exists in A but does not exist in B then:
    I need to update the record(s) in B and the record in C.
If a record exists in A and exists in B then
   update the record(s) in B and update the record in C
If record does not exist in A then
    delete the record from B and C.

I hope this makes sense.  Please let me know if you need more details!!

Thanks much.
pattisp1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
I wouldn't use loops at all.

delete b where vin not in (select vin from a);
delete c where vin not in (select vin from a);

then use merge statements for B  anc C
to update/insert based on A.
pattisp1Author Commented:
Thank you.  Would you mind elaborating on the example.  I'm not sure how the cursor will be layed out.

prinprogCommented:
I'm also not convinced that looping would be the best answer for your need (without more information), but if you wanted to use PL/SQL with cursors, it could look like the attached code snipet.
DECLARE
   CURSOR anotb_exists
   IS
      SELECT x.vin AS vin
            ,x.COLUMN AS something
        FROM table_a x
             JOIN
             (SELECT y.vin AS inner_vin
                FROM table_a y
              MINUS
              SELECT vin
                FROM table_b) sub ON sub.inner_vin = x.vin
             ;
 
   CURSOR aandb_exists
   IS
      SELECT table_a.vin AS vin
            ,table_a.COLUMN AS something
        FROM table_a JOIN table_b ON table_b.vin = table_a.vin
             ;
 
   CURSOR cnota_exists
   IS
      -- assumes that record in B can't exist without C, so ignore B here
      SELECT x.vin AS vin
            ,x.COLUMN AS something
        FROM table_c x
             JOIN
             (SELECT y.vin AS inner_vin
                FROM table_c y
              MINUS
              SELECT vin
                FROM table_a) sub ON sub.inner_vin = x.vin
             ;
BEGIN
-- turn on output
   DBMS_OUTPUT.ENABLE (1000000);
 
-- Now just loop through each cursor
   FOR rec1 IN anotb_cursor
   LOOP
      INSERT INTO b
                  (b.vin, b.something)
           VALUES (rec1.vin, rec1.something);
 
      UPDATE c
         SET b.something = rec1.something
       WHERE b.vin = rec1.vin;
   END LOOP;
 
   FOR rec2 IN anotb_cursor
   LOOP
      UPDATE b
         SET b.something = rec1.something
       WHERE b.vin = rec2.vin;
 
      UPDATE c
         SET c.something = rec1.something
       WHERE c.vin = rec2.vin;
   END LOOP;
 
   FOR rec3 IN anotb_cursor
   LOOP
      DELETE FROM b
            WHERE b.vin = rec3.vin;
 
      DELETE FROM c
            WHERE c.vin = rec3.vin;
   END LOOP;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('**Error** ' || SQLERRM (SQLCODE));
END;

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
as I said, there would be no loop, so no cursor.

Just 4 sql statements.  

the two deletes as I illustrated  and 2 merge statements,  I can't give good examples of those as I have no idea what your columns might be.

But they would looks something like this...
MERGE INTO C
    USING (select a.vin,a.col1 a1,a.col2 a2,b.col1 b1,b.col2 b2 from a,b where a.vin = b.vin(+)) s
    ON (c.vin = s.vin)
    WHEN MATCHED THEN
        UPDATE
           SET c.col1 = s.a1, c.col2 = s.a2, c.col3 = s.b1, c.col3 = s.b2
    WHEN NOT MATCHED THEN
        INSERT(vin, col1, col2, col3, col4)
        VALUES(s.vin,s.a1,s.a2,s.b1,s.b2);
 
MERGE INTO B
    USING (select vin, col1, col2 from a) a
    ON (b.vin = a.vin)
    WHEN MATCHED THEN
        UPDATE
           SET b.col1 = a.col1, b.col2 = a.col2
    WHEN NOT MATCHED THEN
        INSERT(vin, col1, col2)
        VALUES(a.vin,a.col1,a.col2);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pattisp1Author Commented:
Thank you.  I really like the idea of using merge for the update/insert statement.  If you don't think a loop would be the best what information could I provide to possibly not use a loop at all.    I was thinking a loop because of the multple records involved but I may be way off base.

sdstuberCommented:
I'm not sure what you're asking for.  The 4 SQL statements already eliminate the need for a loop.

You would run the deletes first, then the two merges.

All that is needed to change my made up columns to whatever your real columns are.

If there is more to it than that, you'll need to provide more details as to what your processing requirements are.
awking00Information Technology SpecialistCommented:
I think you could even simplify sdstuber's merge statements to simply -
merge into tableB b
using tableA a
on (b.vin = a.vin)
when matched then update set b.columns = a.columns
when not matched then insert [(columns list)] values(a.vin, a.columns)
Substituting columns with the actual names of the attributes to be updated or inserted and repeat with tableC.
sdstuberCommented:
yes,  if all of tableA is needed, that does make is a little nicer.  
I always use the select syntax to specify what it is I'm looking for.
Functionally there is no difference when there is no where clause
awking00Information Technology SpecialistCommented:
I almost always use the select syntax, too. It's just that in this case, I suspect there may be different columns used from tableA to update/insert tableB than to update/insert to tableC. This just eliminates the need for separate select statements. :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.