Oracle：如何用详细信息表中的最新行更新主数据？

Question

我们有两个表：

车辆：

• Id
• RegistrationNumber
• LastAllocationUserName
• LastAllocationDate
• LastAllocationId

Allocations：

• Id
• VehicleId
• UserName
• Date

用最新分配更新车辆表中的每一行的最有效（最简单）的方法是什么？在 SQL Server 中，我将使用 UPDATE FROM 并将每辆车加入最新的分配。 Oracle 没有 UPDATE FROM。在 Oracle 中如何做到这一点？

** 编辑 **

我要求最好的 SQL 查询进行更新。我将使用触发器来更新主表中的数据。我知道怎么写触发器。我所问的只是如何编写 SQL 查询来更新车辆表。例子会很好。谢谢。

Answer 1

正如大多数其他人所指出的：由于您的数据模型，您遇到了一个大问题。为该模型编写的大多数代码将比需要的困难得多。我已经通过赞成票和反对票以及一些评论来表达了这一点，但这还不够。

如果您继续您的道路，那么下面的代码将演示需要完成的操作。希望它吓到你了:-)

示例表：

SQL> create table vehicles (id,registrationnumber,lastallocationusername,lastallocationdate,lastallocationid)
  2  as
  3  select 1, 1, 'Me', sysdate-1, 2 from dual union all
  4  select 2, 2, 'Me', sysdate, 3 from dual
  5  /

Table created.

SQL> create table allocations (id,vehicleid,username,mydate)
  2  as
  3  select 1, 1, 'Me', sysdate-2 from dual union all
  4  select 2, 1, 'Me', sysdate-1 from dual union all
  5  select 3, 2, 'Me', sysdate-1 from dual
  6  /

Table created.

触发器必须查看它自己的表来确定最后的分配。 Oracle 通过引发变异表错误来防止这种类型的脏读。为了避免这种情况，我创建了一个 SQL 类型和一个包：

SQL> create type t_vehicle_ids is table of number;
  2  /

Type created.

SQL> create package allocations_mutating_table
  2  as
  3    procedure reset_vehicleids;
  4    procedure store_vehicleid (p_vehicle_id in vehicles.id%type);
  5    procedure adjust_vehicle_last_allocation;
  6  end allocations_mutating_table;
  7  /

Package created.

SQL> create package body allocations_mutating_table
  2  as
  3    g_vehicle_ids t_vehicle_ids := t_vehicle_ids()
  4    ;
  5    procedure reset_vehicleids
  6    is
  7    begin
  8      g_vehicle_ids.delete;
  9    end reset_vehicleids
 10    ;
 11    procedure store_vehicleid (p_vehicle_id in vehicles.id%type)
 12    is
 13    begin
 14      g_vehicle_ids.extend;
 15      g_vehicle_ids(g_vehicle_ids.count) := p_vehicle_id;
 16    end store_vehicleid
 17    ;
 18    procedure adjust_vehicle_last_allocation
 19    is
 20    begin
 21      update vehicles v
 22         set ( v.lastallocationusername
 23             , v.lastallocationdate
 24             , v.lastallocationid
 25             ) =
 26             ( select max(a.username) keep (dense_rank last order by a.mydate)
 27                    , max(a.mydate)
 28                    , max(a.id) keep (dense_rank last order by a.mydate)
 29                 from allocations a
 30                where a.vehicleid = v.id
 31             )
 32       where v.id in (select column_value from table(cast(g_vehicle_ids as t_vehicle_ids)))
 33      ;
 34    end adjust_vehicle_last_allocation
 35    ;
 36  end allocations_mutating_table;
 37  /

Package body created.

然后 3 个数据库触发器将更新代码从行级别移动到语句级别，从而避免了变异表错误：

SQL> create trigger allocations_bsiud
  2    before insert or update or delete on allocations
  3  begin
  4    allocations_mutating_table.reset_vehicleids;
  5  end;
  6  /

Trigger created.

SQL> create trigger allocations_ariud
  2    after insert or update or delete on allocations
  3    for each row
  4  begin
  5    allocations_mutating_table.store_vehicleid(nvl(:new.vehicleid,:old.vehicleid));
  6  end;
  7  /

Trigger created.

SQL> create trigger allocations_asiud
  2    after insert or update or delete on allocations
  3  begin
  4    allocations_mutating_table.adjust_vehicle_last_allocation;
  5  end;
  6  /

Trigger created.

并进行了一些测试来验证它是否在单用户环境中工作：

SQL> select * from vehicles
  2  /

        ID REGISTRATIONNUMBER LA LASTALLOCATIONDATE  LASTALLOCATIONID
---------- ------------------ -- ------------------- ----------------
         1                  1 Me 13-05-2010 14:03:43                2
         2                  2 Me 14-05-2010 14:03:43                3

2 rows selected.

SQL> insert into allocations values (4, 1, 'Me', sysdate)
  2  /

1 row created.

SQL> select * from vehicles
  2  /

        ID REGISTRATIONNUMBER LA LASTALLOCATIONDATE  LASTALLOCATIONID
---------- ------------------ -- ------------------- ----------------
         1                  1 Me 14-05-2010 14:03:43                4
         2                  2 Me 14-05-2010 14:03:43                3

2 rows selected.

SQL> update allocations
  2     set mydate = mydate - 2
  3   where id = 4
  4  /

1 row updated.

SQL> select * from vehicles
  2  /

        ID REGISTRATIONNUMBER LA LASTALLOCATIONDATE  LASTALLOCATIONID
---------- ------------------ -- ------------------- ----------------
         1                  1 Me 13-05-2010 14:03:43                2
         2                  2 Me 14-05-2010 14:03:43                3

2 rows selected.

SQL> delete allocations
  2   where id in (2,4)
  3  /

2 rows deleted.

SQL> select * from vehicles
  2  /

        ID REGISTRATIONNUMBER LA LASTALLOCATIONDATE  LASTALLOCATIONID
---------- ------------------ -- ------------------- ----------------
         1                  1 Me 12-05-2010 14:03:43                1
         2                  2 Me 14-05-2010 14:03:43                3

2 rows selected.

现在您所要做的就是添加一些序列化，使其在多用户环境中 100% 工作。但希望这个例子已经足够可怕了。

问候，
抢。

Answer 2

当前设置要求您在 ALLOCATIONS 表上使用触发器来维护错误的决策选择。也就是说，使用：

UPDATE VEHICLE
   SET (LastAllocationUserName, LastAllocationDate, LastAllocationId) =
       (SELECT a.username,
               a.date,
               a.id
          FROM ALLOCATIONS a
          JOIN (SELECT b.vehicleid, 
                       MAX(b.date) AS max_date
                  FROM ALLOCATIONS b
              GROUP BY b.vehicleid) x ON x.vehicleid = a.vehicleid
                                     AND x.max_date = a.date
         WHERE a.vehicleid = VEHICLE.id)

通过从 VEHICLE 表中删除有问题的列，并使用视图提供最新的分配信息，可以更好地实现这一点。

Answer 3

从设计的角度来看，我更愿意在车辆表上主动维护这三个字段，并将“分配”填充为历史表（可能通过触发器）。将父表上的更新推送到子表上的插入比相反要容易得多。

Answer 4

使用 Oracle 中的另一个表“更新”的最简单方法是使用 MERGE。

MERGE INTO vehicle v
USING (
  -- subquery to get info you need
) s ON (v.id = s.vehicleId)
WHEN MATCHED THEN UPDATE SET 
  username = s.username 
  ...

http://psoug.org/reference/merge.html

Answer 5

您是否在触发器内寻找更新？

CREATE TRIGGER ALLOCATION_I
AFTER INSERT ON ALLOCATION
REFERENCING NEW AS NEW
FOR EACH ROW
Begin

UPDATE Vehicle 
 set LastAllocationUserName = :NEW.Username 
 ,LastAllocationDate = :NEW.date 
 ,LastAllocationId = :NEW.id 
WHERE Id = :NEW.VehicleId;

END;

Answer 6

UPDATE VEHICLE V
   SET (LastAllocationId, LastAllocationDate, LastAllocationUserName) =
   (SELECT a.id
           ,a.date
           ,a.username
      FROM ALLOCATIONS a
      where a.VehicleId = V.id
        and a.date = ( select max(Last_a.date) from ALLOCATIONS Last_a
                       where Last_a.VehicleId = V.id )
    )

你说得对。带有历史表的视图速度很慢。不存在快速“连接到最后一条记录”这样的事情。触发器是最好的解决方案。
如果可以的话，请在第一次时使用 PL 进行填充。它更容易理解和维护。

DECLARE
   Last_date DATE;
   Last_User Varchar2(100);
   Last_ID number;
Begin
FOR V IN ( Select * from VEHICLE )
LOOP
   select max(date) into Last_date 
   from ALLOCATIONS Last_a
   where Last_a.VehicleId = V.id;

   IF Last_date is NULL then 
      Last_User := NULL;
      Last_ID := NULL;
   else
      select Id,UserName into Last_id, Last_user
      from ALLOCATIONS Last_a
      where Last_a.VehicleId = V.id
      and Last_a.date = Last_date;
   END IF;

   UPDATE Vehicle 
     set LastAllocationUserName = Last_User
         ,LastAllocationDate = Last_date
         ,LastAllocationId Last_id
   Where id = V.id;

END LOOP;
End;

警告：写在这里，未经测试。