Oracle 相当于 MySQL INSERT IGNORE?

2021-12-05 00:00:00 duplicates insert oracle

我需要更新一个查询，以便它在插入前检查重复条目不存在.在 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.

详见说明书

相关文章