Oracle DB插入，对重复键不执行任何操作 [英] Oracle DB insert and do nothing on duplicate key

问题描述

我必须在oracle DB中插入一些数据，而无需事先检查它们是否已经存在.

I have to insert some data in oracle DB, without previously checking if it already exist.

是否存在，在oracle上进行转换以在查询中捕获异常并对其进行处理以不返回任何异常?

Does exist any way, transiction on oracle to catch the exception inside the query and handle it to don't return any exception?

这将是mysql风格的完美之选

It would be perfect something in mysql's style like

insert .... on duplicate key a=a

推荐答案

您可以使用 MERGE .语法与常规插入有些不同；

You can use MERGE. The syntax is a bit different from a regular insert though;

MERGE INTO test USING (
  SELECT 1 AS id, 'Test#1' AS value FROM DUAL    -- your row to insert here
) t ON (test.id = t.id)                          -- duplicate check
WHEN NOT MATCHED THEN 
   INSERT (id, value) VALUES (t.id, t.value);    -- insert if no duplicate

要测试的SQLfiddle .

这篇关于Oracle DB插入，对重复键不执行任何操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！