Oracle相当于MySQL INSERT IGNORE？ [英] Oracle Equivalent to MySQL INSERT IGNORE?

问题描述

我需要更新一个查询，以便它在插入之前检查一个重复的条目不存在。在MySQL中，我可以使用INSERT IGNORE，以便如果发现重复的记录，它只是跳过插入，但我似乎找不到一个等效的Oracle选项。任何建议？

I need to update a query so that it checks that a duplicate entry does not exist before insertion. In MySQL I can just use INSERT IGNORE so that if a duplicate record is found it just skips the insert, but I can't seem to find an equivalent option for Oracle. Any suggestions?

推荐答案

查看MERGE语句。这应该做你想要的 - 这是将执行此操作的 WHEN NOT MATCHED 子句。

Check out the MERGE statement. This should do what you want - it's the WHEN NOT MATCHED clause that will do this.

对于Oracle缺乏对真实VALUES（）子句的支持，具有固定值的单个记录的语法相当笨拙：

Do to Oracle's lack of support for a true VALUES() clause the syntax for a single record with fixed values is pretty clumsy though:

MERGE INTO your_table yt
USING (
   SELECT 42 as the_pk_value, 
          'some_value' as some_column
   FROM dual
) t on (yt.pk = t.the_pke_value) 
WHEN NOT MATCHED THEN 
   INSERT (pk, the_column)
   VALUES (t.the_pk_value, t.some_column);

另一种方法（如果你是从不同的表进行批量加载）是使用错误记录Oracle的设施。声明将如下所示：

A different approach (if you are e.g. doing bulk loading from a different table) is to use the "Error logging" facility of Oracle. The statement would look like this:

 INSERT INTO your_table (col1, col2, col3)
 SELECT c1, c2, c3
 FROM staging_table
 LOG ERRORS INTO errlog ('some comment') REJECT LIMIT UNLIMITED;

之后，所有抛出错误的行都可以在表 errlog 。在使用 DBMS_ERRLOG.CREATE_ERROR_LOG 运行插入之前，您需要手动创建 errlog 表（或任何您选择的名称）。

Afterwards all rows that would have thrown an error are available in the table errlog. You need to create that errlog table (or whatever name you choose) manually before running the insert using DBMS_ERRLOG.CREATE_ERROR_LOG.

有关详细信息，请参阅手册

See the manual for details

这篇关于Oracle相当于MySQL INSERT IGNORE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！