不允许时替换MERGE INTO Oracle语法的模式

Question

我有一个应用程序，它使用Oracle合并到...DML语句来更新表A，以对应于另一个表B中的一些更改(表A是表B的选定部分以及一些其他信息的摘要)。在典型的合并操作中，可能在表B中插入5-6行(上千行中的10行)，并更新2-3行。

事实证明，应用程序将部署在一个对目标表具有安全策略的环境中。合并成..。语句不能与这些表一起使用(ORA-28132: Merge into语法不支持安全策略)

所以我们必须把合并改为..。使用常规插入和更新的逻辑。这是其他人遇到的问题吗？有没有将merge语句中的WHEN MATCHED/WHEN NOT MATCHED逻辑转换为INSERT和UPDATE语句的最佳实践模式？合并是在存储过程中进行的，因此如果需要的话，除了DML之外，解决方案还可以使用PL/SQL。

Answer 1

另一种方法(不是合并)是使用两条sql语句，一条用于insert，一条用于update。"WHEN MATCHED“和"WHEN NOT MATCHED”可以使用joins或"in“子句来处理。

如果您决定采用下面的方法，最好先运行更新(因为它只针对匹配的记录运行)，然后再插入不匹配的记录。无论哪种方式，数据集都是相同的，它只是用下面的顺序更新了较少数量的记录。

此外，与Merge类似，即使Source和Target中的名称匹配，此update语句也会更新Name列。如果您不想这样，也可以将该条件添加到where中。

create table src_table(
   id number primary key,
   name varchar2(20) not null
);

create table tgt_table(
   id number primary key,
   name varchar2(20) not null
);

insert into src_table values (1, 'abc');
insert into src_table values (2, 'def');
insert into src_table values (3, 'ghi');

insert into tgt_table values (1, 'abc');
insert into tgt_table values (2,'xyz');

SQL> select * from Src_Table;

        ID NAME
---------- --------------------
         1 abc
         2 def
         3 ghi

SQL> select * from Tgt_Table;

        ID NAME
---------- --------------------
         2 xyz
         1 abc

Update tgt_Table tgt
   set Tgt.Name = 
      (select Src.Name
          from Src_Table Src
          where Src.id = Tgt.id
      );

2 rows updated. --Notice that ID 1 is updated even though value did not change

select * from Tgt_Table;

   ID NAME
----- --------------------
    2 def
    1 abc

insert into tgt_Table
select src.*
  from Src_Table src,
       tgt_Table tgt
  where src.id = tgt.id(+)
    and tgt.id is null;

1 row created.

SQL> select * from tgt_Table;

        ID NAME
---------- --------------------
         2 def
         1 abc
         3 ghi

commit;

有更好的方法可以做到这一点，但这看起来很简单，而且是面向SQL的。如果数据集很大，那么PL/SQL解决方案的性能就不会很好。

Answer 2

除了深入研究安全策略之外，我至少可以想到两种选择，我对安全策略了解不多。

处理记录以逐行合并。尝试执行更新，如果更新失败，则插入，反之亦然，这取决于您是否希望大多数记录需要更新或插入(即针对最常见的情况进行优化，这将减少触发的SQL语句的数量)，例如：

begin
  for row in (select ... from source_table) loop
    update table_to_be_merged
    if sql%rowcount = 0 then -- no row matched, so need to insert
      insert ...
    end if;
  end loop;
end;

另一种选择是大容量收集您想要合并到一个数组中的记录，然后尝试大容量插入它们，捕获所有主键异常(我现在记不起这种情况的语法了，但是您可以使用一个大容量插入将所有插入失败的行放到另一个数组中，然后对它们进行处理)。

从逻辑上讲，merge语句必须检查幕后是否存在每条记录，我认为它的处理方式与我上面发布的代码非常相似。然而，合并总是比用PLSQL编码效率更高，因为它只有一次SQL调用，而不是很多次。