There is a table:

CREATE TABLE my_table ( name VARCHAR2(20 BYTE) NOT NULL, car VARCHAR2(40 BYTE) NOT NULL, rank NUMBER, PRIMARY KEY (name, car) );

In Java, I have a List - objects that store information name, car, rank = null.

You need to write a stored function (pl / sql) that implements the following:

  • If there are objects in the List that are not in the table, add them to the table.

  • If there are no records in the List that are in the table, remove them from the table.

  • In these operations, do not take into account the rank field (ie, when comparing without a difference in its value).

I only thought of this:

 DECLARE type my_table_array IS TABLE OF my_table%ROWTYPE; v_Numbers my_table_array := my_table_array(); rec my_table%ROWTYPE; idx number; BEGIN v_Numbers.extend(1); idx := v_Numbers.count; rec.name := 'John'; rec.car := 'Toyota'; rec.rank := 5; v_Numbers(idx) := rec; END;

How is it nice to check in BEGIN now? If there are no such records in the table, add. And run through all v_Numbers.

  • one
    look towards the merge - Viktorov
  • one
    Or is it better to perform these comparisons on the client side? It is better to perform works with data arrays on the server side - it’s as if it’s sharpened ... especially since the data must finally be corrected exactly on the server, so why pull first from there, and then go there? In general, unload data from List-objects into a temporary table and with one query everything and correct it. - Akina
  • one
    You can pass your List to a procedure using UDT, and in the procedure make a merge - Primus Singularis
  • one
    The merge operation can not only add new, but also remove the missing ones. and do it simultaneously within the same request. so the procedure is one. the main thing is, as @Akina said, to make a temporary table and load data from the List into it, because transferring the rowset to the procedure, so that it would be convenient to work with it later, otherwise it’s impossible to do it anyway - Mike
  • one
    in the select query in merge, you need to make a full outer join table with new rows and tables with existing data. after which, in the update part, it will be possible to write a suitable condition for delete (NULL to the columns with new data). Well, at least it seems to me that it should work, it will be necessary to try, it may be a little harder to look, but probably solved - Mike

1 answer 1

To synchronize the two sets of data is best to use MERGE . A set with new data can be any way you feel comfortable (and possibly). I found an example of transferring an object type array from java to oracle, which is convenient when used in a procedure. But if there are any problems with it, you can instead use a temporary temporary global table or send data as text inside the merge (as a subquery of the form select 'x','y' from DUAL union select 'z','z' ... ) or as text as a procedure parameter ( call my_procedure( my_rec_list( my_rec('x','y',NULL), my_rec('z','z',NULL))) ).

If you decide to transfer as an array, you will need to create an object record type and an array type of such objects:

 CREATE OR REPLACE TYPE my_rec AS OBJECT( name VARCHAR2(20 BYTE), car VARCHAR2(40 BYTE), rank NUMBER -- Поле rank можно не включать и не передавать, если не нужно ); CREATE TYPE my_rec_list AS TABLE OF my_rec;

In this case, the stored procedure that updates the table will look like this:

 create or replace procedure my_procedure(LIST my_rec_list) is begin merge into my_table O using ( select nvl(X.name,T.name) name, nvl(X.car,T.car) car, X.name as x_name from (select * from TABLE(LIST)) X full outer join my_table T on T.name=X.name and T.car=X.car ) N on(O.name=N.name and O.car=N.car) when not matched then insert(name,car) values(N.name,N.car) when matched then update set O.rank=O.rank delete where x_name is null; end my_procedure;

The update line in merge does not change anything in the database, since it tries to change the column to itself, but it is necessary so that you can specify the delete line in the query.

  • Thank. I implemented the transfer following the example from this link coderanch.com/t/589556/framework/… . The principle is the same. Yes, I created the same types. Tomorrow I will try your example. - ks_on_v
  • "an INTO clause is expected in this SELECT statement" - shows on the line select * from TABLE (LIST). You can't just select to do it - ks_on_v
  • @ks_on_v This is something incredible. all that is written here is one request, and part of the request cannot be demanded into anything. To cause such an error, the given select * from TABLE subquery should be a separate statement in the procedure, but then it loses all meaning. PS Before publishing the post, I checked the performance - Mike
  • @ks_on_v And yes, if this is how you are trying to debug, then keep in mind, the procedure in Oracle cannot return the result of a select (except through the out parameter in the form of some kind of composite type). So you need to debug everything without a procedure, for example, directly performing a request - Mike
  • update set old_data.rank = old_data.rank; This line deletes all records except new ones. Because in x_name there are only new entries from the new list. It turns out, when we reach it, all the records that coincide in the new and old data are erased. - ks_on_v