Oracle：如何 UPSERT（更新或插入表？）

MERGE statement 合并两个表之间的数据。使用 DUAL 允许我们使用此命令。请注意，这不受并发访问的保护。

create or replace
procedure ups(xa number)
as
begin
    merge into mergetest m using dual on (a = xa)
         when not matched then insert (a,b) values (xa,1)
             when matched then update set b = b+1;
end ups;
/
drop table mergetest;
create table mergetest(a number, b number);
call ups(10);
call ups(10);
call ups(20);
select * from mergetest;

A                      B
---------------------- ----------------------
10                     2
20                     1

上面的 PL/SQL 中的双重示例很棒，因为我想做类似的事情，但我想要它在客户端......所以这是我用来直接从某些 C# 发送类似语句的 SQL

MERGE INTO Employee USING dual ON ( "id"=2097153 )
WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
WHEN NOT MATCHED THEN INSERT ("id","last","name") 
    VALUES ( 2097153,"smith", "john" )

但是，从 C# 的角度来看，这比进行更新并查看受影响的行是否为 0 以及如果是则执行插入要慢。

MERGE 的替代方法（“老式方式”）：

begin
   insert into t (mykey, mystuff) 
      values ('X', 123);
exception
   when dup_val_on_index then
      update t 
      set    mystuff = 123 
      where  mykey = 'X';
end;   

没有异常检查的另一种选择：

UPDATE tablename
    SET val1 = in_val1,
        val2 = in_val2
    WHERE val3 = in_val3;

IF ( sql%rowcount = 0 )
    THEN
    INSERT INTO tablename
        VALUES (in_val1, in_val2, in_val3);
END IF;

如果不存在则插入更新：

INSERT INTO mytable (id1, t1) 
  SELECT 11, 'x1' FROM DUAL 
  WHERE NOT EXISTS (SELECT id1 FROM mytble WHERE id1 = 11); 

UPDATE mytable SET t1 = 'x1' WHERE id1 = 11;

正如 Tim Sylvester 的评论所指出的那样，到目前为止，在面对并发访问时，没有一个答案是安全的，并且会在出现竞争时引发异常。为了解决这个问题，插入/更新组合必须包含在某种循环语句中，以便在出现异常时重试整个事情。

例如，下面是如何将 Grommit 的代码包装在一个循环中以使其在并发运行时安全：

PROCEDURE MyProc (
 ...
) IS
BEGIN
 LOOP
  BEGIN
    MERGE INTO Employee USING dual ON ( "id"=2097153 )
      WHEN MATCHED THEN UPDATE SET "last"="smith" , "name"="john"
      WHEN NOT MATCHED THEN INSERT ("id","last","name") 
        VALUES ( 2097153,"smith", "john" );
    EXIT; -- success? -> exit loop
  EXCEPTION
    WHEN NO_DATA_FOUND THEN -- the entry was concurrently deleted
      NULL; -- exception? -> no op, i.e. continue looping
    WHEN DUP_VAL_ON_INDEX THEN -- an entry was concurrently inserted
      NULL; -- exception? -> no op, i.e. continue looping
  END;
 END LOOP;
END; 

注意在事务模式 SERIALIZABLE（我不推荐顺便说一句）中，您可能会遇到 ORA-08177: can't serialize access for this transaction 异常。

我想要 Grommit 答案，但它需要欺骗值。我找到了可能出现一次的解决方案：http://forums.devshed.com/showpost.php?p=1182653&postcount=2

MERGE INTO KBS.NUFUS_MUHTARLIK B
USING (
    SELECT '028-01' CILT, '25' SAYFA, '6' KUTUK, '46603404838' MERNIS_NO
    FROM DUAL
) E
ON (B.MERNIS_NO = E.MERNIS_NO)
WHEN MATCHED THEN
    UPDATE SET B.CILT = E.CILT, B.SAYFA = E.SAYFA, B.KUTUK = E.KUTUK
WHEN NOT MATCHED THEN
    INSERT (  CILT,   SAYFA,   KUTUK,   MERNIS_NO)
    VALUES (E.CILT, E.SAYFA, E.KUTUK, E.MERNIS_NO); 

多年来我一直在使用第一个代码示例。注意未找到而不是计数。

UPDATE tablename SET val1 = in_val1, val2 = in_val2
    WHERE val3 = in_val3;
IF ( sql%notfound ) THEN
    INSERT INTO tablename
        VALUES (in_val1, in_val2, in_val3);
END IF;

下面的代码可能是新的和改进的代码

MERGE INTO tablename USING dual ON ( val3 = in_val3 )
WHEN MATCHED THEN UPDATE SET val1 = in_val1, val2 = in_val2
WHEN NOT MATCHED THEN INSERT 
    VALUES (in_val1, in_val2, in_val3)

在第一个示例中，更新执行索引查找。为了更新正确的行，它必须这样做。 Oracle 打开一个隐式游标，我们使用它来包装相应的插入，因此我们知道插入只会在键不存在时发生。但是插入是一个独立的命令，它必须进行第二次查找。我不知道合并命令的内部工作原理，但由于该命令是一个单元，Oracle 可以通过单个索引查找执行正确的插入或更新。

我认为当您确实需要完成一些处理时，合并会更好，这意味着从某些表中获取数据并更新表，可能会插入或删除行。但是对于单行情况，您可以考虑第一种情况，因为语法更常见。

关于建议的两种解决方案的说明：

1）插入，如果异常则更新，

或者

2) 更新，如果 sql%rowcount = 0 然后插入

是先插入还是先更新的问题也取决于应用程序。您是否期待更多插入或更多更新？最有可能成功的应该先走。

如果你选错了，你会得到一堆不必要的索引读取。没什么大不了的，但仍然需要考虑。

尝试这个，

insert into b_building_property (
  select
    'AREA_IN_COMMON_USE_DOUBLE','Area in Common Use','DOUBLE', null, 9000, 9
  from dual
)
minus
(
  select * from b_building_property where id = 9
)
;

从 http://www.praetoriate.com/oracle_tips_upserts.htm：

“在 Oracle9i 中，UPSERT 可以在一条语句中完成这项任务：”

INSERT
FIRST WHEN
   credit_limit >=100000
THEN INTO
   rich_customers
VALUES(cust_id,cust_credit_limit)
   INTO customers
ELSE
   INTO customers SELECT * FROM new_customers;