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;   

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

没有异常检查的另一种方法:

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;

我想要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); 

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;   

关于两种解决方案的说明如下:

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

or

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

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

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