Блог

Допустим, у меня есть таблица базы данных с двумя столбцами — vehicle и location .

У каждого vehicle их много locations .

Когда моя программа получает сообщение, содержащее список местоположений для каждого транспортного средства, я мог бы выполнить любое из следующих действий:

1. Delete все местоположения для транспортного средства и перебирать сообщение, чтобы повторно insert их все (неэффективно)
2. Select местоположения транспортного средства из базы данных, просмотрите сообщение и создайте список тех местоположений, которые есть в базе данных, но не в сообщении, и тех, которые есть в сообщении, но не в базе данных. Удалите ( DELETE WHERE IN... ) те, которые есть в базе данных, но не в сообщении, и insert те, которые есть в сообщении, но не в базе данных.

Здесь мои знания SQL меня подводят. Есть ли более эффективный способ (т. Е. меньше инструкций SQL, меньше времени работы с базой данных, меньше блокировок) для этого (я не могу выполнять хранимые процедуры)?

Мой инстинкт подсказывает мне, что я должен быть в состоянии сделать DELETE WHERE NOT IN... , чтобы избежать необходимости select использования базы данных в варианте 2 — но тогда мне все еще нужно select знать, какие дополнительные вставить. Возможно, существует какой-либо INSERT эквивалент — INSERT {THIS LIST OF locations} ALL WITH {this vehicle} ? Или существует стандартная часть логики синхронизации, которая обычно используется в подобных ситуациях?

Допустим, у вас есть транспортное средство V1 с местоположениями A , B и X и транспортное средство V2 с местоположениями B и Q . Сообщение предназначено для транспортного средства V1 с указанием местоположений A , Y .

delete Частично вы могли бы использовать это:

 delete from t where veh = 'V1' and loc not in ('A', 'Y');
  

и для insert этого merge :

 merge into t 
using (select 'V1' veh, 'A' loc from dual union all
       select 'V1' veh, 'Y' loc from dual) s
on (t.veh = s.veh and t.loc = s.loc)
when not matched then insert values (s.veh, s.loc);
  

Местоположение A осталось нетронутым, B и X было удалено delete , Y было добавлено merge . Если у вас в таблице больше столбцов, вы можете выполнить всю операцию за один merge , но я не знаю, будет ли это быстрее, потому что для этого потребуется объединение в исходном запросе.

Редактировать: Вы должны каким-то образом встроить свое «сообщение» в структуру данных, читаемую Oracle. Возможно, сообщения уже хранятся в какой-то таблице, возможно, вы можете использовать временную структуру. Вы не уточнили это в своем вопросе. Я привел вам пример работы с dual, потому что мне нужно было как-то сконструировать операторы. Для V1(A, B, C) и V3(X, Y) вы можете выполнить union all 5 раз или использовать более короткий синтаксис:

 select 'V1' veh, column_value loc from table(sys.odcivarchar2list('A', 'B', 'C')) union all
select 'V3' veh, column_value loc from table(sys.odcivarchar2list('X', 'Y'))
  

sys.odcivarchar2list является предопределенным типом Oracle, вы также можете определить свой собственный ( create type locations as table of varchar2(100) ) и использовать его.

В любом случае, запустите delete дважды для каждого транспортного средства:

 delete from t where veh = `V1` and loc not in ('A', 'B', 'C');
delete from t where veh = `V3` and loc not in ('X', 'Y');
  

и merge один раз:

 merge into t 
using (
    select 'V1' veh, column_value loc from table(sys.odcivarchar2list('A', 'B', 'C')) 
    union all
    select 'V3' veh, column_value loc from table(sys.odcivarchar2list('X', 'Y'))) s
on (t.veh = s.veh and t.loc = s.loc)
when not matched then insert values (s.veh, s.loc);