Oracle INSERT INTO SELECT（...）DUP_VAL_ON_INDEX异常行为 [英] Oracle INSERT INTO SELECT(...) DUP_VAL_ON_INDEX exception behavior

问题描述

我有一个存储过程，如下所示：

I have a stored procedure which looks like this:

BEGIN
  INSERT INTO result_table
  (SELECT (...) FROM query_table);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    NULL;
END;

我正在循环中执行这个循环，该循环将多个参数传递给SELECT语句，在某些情况下，的值可能重复，这就是为什么我必须捕获DUP_VAL_ON_INDEX异常。

I'm doing it in a loop which passes multiple parameters to the SELECT statement and in some cases some of the values might duplicate that is why I have to catch the DUP_VAL_ON_INDEX exception.

我的问题是，如果SELECT语句返回更多的行，只有一个存在于* result_table *，f。例如

My question is that if the SELECT statement returns more rows and only one from them exists already in *result_table*, f. ex.

1 'A'
2 'B'
3 'C'

<第二和第三个从上面的情况）插入？还是没有一个插入？

And first row (1 'A') would already be in the table, would other rows which don't exist (second and third from case above) be inserted? Or none of them would be inserted at all?

我恐怕没有一个插入（我的测试用例部分地证实了这一点）...如果是这样，我需要什么选择才能实现所需的行为？有没有一个很好的方法来插入不违反主键的行使用上面的结构？

I'm afraid that none of them would be inserted (and my test case partially confirms that)... If so, what option do I have to achieve desired bahavior? Is there a good way to insert the rows that don't violate the primary key using the construction above?

推荐答案

你可以使用 MERGE 语句。如果记录不存在，请插入记录，如果它们已经存在，则不执行任何操作。

You can use the MERGE statement. Insert the records if they don't exist and do nothing if they already exist.

http://psoug.org/reference/merge.html

这篇关于Oracle INSERT INTO SELECT（...）DUP_VAL_ON_INDEX异常行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！