时间戳

首页 > 解决方案 > 如何使用数据列表更新数据库表

sql - 如何使用数据列表更新数据库表

问题描述

假设我有一个包含两列的数据库表 -vehicle和location.

每个vehicle都有很多locations。

当我的程序收到包含每辆车位置列表的消息时，我可以执行以下任何操作：

  1. Delete车辆的所有位置并循环通过消息以重新insert全部（低效）
  2. Select从数据库中获取车辆的位置，循环遍历消息并创建在数据库中但不在消息中的位置的列表，以及在消息中但不在数据库中的位置的列表。删除（DELETE WHERE IN...）那些在数据库中但不在消息中的，以及insert在消息中但不在数据库中的那些。

这就是我对 SQL 的了解让我失望的地方。是否有更有效的方法（即更少的 SQL 语句、更少的数据库时间、更少的锁定）来执行此操作（我不能执行存储过程）？

我的直觉告诉我，我必须能够做到DELETE WHERE NOT IN...，以便在选项 2 中从数据库中省去必须select- 但我仍然必须select知道要插入哪些额外的。有没有INSERT等价的 - INSERT {THIS LIST OF locations} ALL WITH {this vehicle}，也许？或者是否有一种标准的同步逻辑通常用于这种情况？

标签： sqloracleperformanceoracle12c

解决方案

假设您有带有位置的车辆V1，A以及B带有位置和的X车辆。该消息适用于具有位置、的车辆。V2BQV1AY

对于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 可读的数据结构中。也许消息已经存储在某个表中，也许您可​​以使用临时结构。你在你的问题中没有明确这一点。我给了你一个关于对偶的例子，因为我必须以某种方式构造语句。For V1(A, B, C)andV3(X, Y)你可以做union all5 次或使用更短的语法：

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);

推荐阅读