• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1978
  • Last Modified:

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.
0
pattisp1
Asked:
pattisp1
  • 4
  • 2
  • 2
  • +1
1 Solution
 
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.
0
 
pattisp1Author Commented:
Thank you.  Would you mind elaborating on the example.  I'm not sure how the cursor will be layed out.

0
 
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

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

0
 
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.

0
 
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.
0
 
awking00Commented:
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.
0
 
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
0
 
awking00Commented:
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. :-)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now