many to many crud

I'm currently implementing CRUD for a project. I have a lot of many-to-many relationships. Some of them are table1_id, table2_id, and some of them have additional columns (price, amount, etc).

What is the best way to update many-to-many table: as you can see, there can be some information already along with new data.

Example

t1_id t2_id amount
1     3     15
2     4     50

I'd like to update this info and send some data like : {1,3,15开发者_如何转开发}, {2,5, 25}. So i need to update the first line and insert a new line. I want to create a function (pl/sql) that will take table of records and check whether it's a new record or record for update.

Question: Are there any better ways to implement this? Are there any patterns to accomplish that goal?

The pattern you're looking for is usually called an "UPSERT": insert a row if one doesn't exist, otherwise update the existing row. You'll still need to loop through your rows, but you can execute a single statement for each row.

I'm not sure about pl/sql but this question might help: Oracle: how to UPSERT (update or insert into a table?)

I almost always do this lazily and just delete all the associated rows from the cross-reference table, then run an insert statement to link it back up. This would ideally all be done within a transaction. I haven't done much in terms of analyzing the performance, but it probably isn't too bad to do it that way, especially if you have a large number of updates to make.