Oracle INSERT INTO SELECT(...) DUP_VAL_ON_INDEX 异常行为
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14649622/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle INSERT INTO SELECT(...) DUP_VAL_ON_INDEX exception behavior
提问by Ziouas
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;
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 语句，在某些情况下，某些值可能会重复，这就是我必须捕获 DUP_VAL_ON_INDEX 异常的原因。
My question is that if the SELECT statement returns more rows and only one from them exists already in *result_table*, f. ex.
我的问题是，如果 SELECT 语句返回更多行，而 *result_table* 中只存在其中一个行，f. 前任。
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?
并且第一行（1 'A'）已经在表中，是否会插入其他不存在的行（上述案例中的第二和第三行）？或者根本不会插入它们中的任何一个？
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?
我担心它们都不会被插入（我的测试用例部分证实了这一点）......如果是这样，我有什么选择来实现所需的行为？有没有一种好方法可以使用上面的构造插入不违反主键的行？
回答by a1ex07
You are right, if one record violates constraint, none will be inserted. I'd do
您是对的，如果一条记录违反约束，则不会插入任何记录。我会做
INSERT INTO result_table
(SELECT (...) FROM query_table a WHERE NOT EXISTS
(SELECT NULL FROM result_table b WHERE b.b_unique_key = a.b_unique_key)
)
Another option is to use error logging
另一种选择是使用错误日志
INSERT INTO result_table
SELECT ... FROM query_table
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;
Note: you have to create error table prior to run this query.
注意：您必须在运行此查询之前创建错误表。
回答by Alen Oblak
You can use the MERGEstatement. Insert the records if they don't exist and do nothing if they already exist.
您可以使用该MERGE语句。如果记录不存在，则插入记录，如果它们已经存在，则不执行任何操作。
回答by Chris Saxon
If you're using 11g, then you can use the ignore_row_on_dupkey_indexhint to suppress the errors:
如果您使用的是 11g，那么您可以使用ignore_row_on_dupkey_index提示来抑制错误：
create table tab (id integer);
alter table tab add constraint tab_pk primary key (id);
insert into tab
select rownum from dual connect by level <= 1;
1 rows inserted.
ID
----------
1
SELECT * FROM tab;
insert into tab
select rownum from dual connect by level <= 3;
SQL Error: ORA-00001: unique constraint (CAM_OWNER.TAB_PK) violated
insert /*+ ignore_row_on_dupkey_index(tab, tab_pk) */into tab
select rownum from dual connect by level <= 3;
SELECT * FROM tab;
2 rows inserted.
ID
----------
1
2
3
