oracle 如何在 PL/SQL 块中捕获唯一约束错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/440135/
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
How to catch a unique constraint error in a PL/SQL block?
提问by Thiago Arrais
Say I have an Oracle PL/SQL block that inserts a record into a table and need to recover from a unique constraint error, like this:
假设我有一个 Oracle PL/SQL 块，它向表中插入一条记录，并且需要从唯一约束错误中恢复，如下所示：
begin
insert into some_table ('some', 'values');
exception
when ...
update some_table set value = 'values' where key = 'some';
end;
Is it possible to replace the ellipsis for something in order to catch an unique constraint error?
是否可以替换省略号以捕获唯一的约束错误？
回答by Ricardo Villamil
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE
回答by William
I'm sure you have your reasons, but just in case... you should also consider using a "merge" query instead:
我相信你有你的理由，但以防万一……你也应该考虑使用“合并”查询：
begin
merge into some_table st
using (select 'some' name, 'values' value from dual) v
on (st.name=v.name)
when matched then update set st.value=v.value
when not matched then insert (name, value) values (v.name, v.value);
end;
(modified the above to be in the begin/end block; obviously you can run it independantly of the procedure too).
（将上述修改为在开始/结束块中；显然您也可以独立于程序运行它）。
回答by EvilTeach
I suspect the condition you are looking for is DUP_VAL_ON_INDEX
我怀疑你正在寻找的条件是 DUP_VAL_ON_INDEX
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('OH DEAR. I THINK IT IS TIME TO PANIC!')
回答by Clay
As an alternative to explicitly catching and handling the exception you could tell Oracle to catch and automatically ignore the exception by including a /*+ hint */in the insert statement. This is a little faster than explicitly catching the exception and then articulating how it should be handled. It is also easier to setup. The downside is that you do not get any feedback from Oracle that an exception was caught.
作为显式捕获和处理异常的替代方法，您可以通过/*+ hint */在插入语句中包含 a 来告诉 Oracle 捕获并自动忽略异常。这比显式捕获异常然后阐明应该如何处理它要快一些。它也更容易设置。缺点是您不会从 Oracle 获得任何关于捕获异常的反馈。
Here is an example where we would be selecting from another table, or perhaps an inner query, and inserting the results into a table called TABLE_NAMEwhich has a unique constraint on a column called IDX_COL_NAME.
这是一个示例，我们将从另一个表或内部查询中进行选择，然后将结果插入到一个名为 的表中TABLE_NAME，该表对名为的列具有唯一约束IDX_COL_NAME。
INSERT /*+ ignore_row_on_dupkey_index(TABLE_NAME(IDX_COL_NAME)) */
INTO TABLE_NAME(
INDEX_COL_NAME
, col_1
, col_2
, col_3
, ...
, col_n)
SELECT
INDEX_COL_NAME
, col_1
, col_2
, col_3
, ...
, col_n);
This is not a great solution if your goal it to catch and handle (i.e. print out or update the row that is violating the constraint). But if you just wanted to catch it and ignore the violating row then then this should do the job.
如果您的目标是捕获和处理（即打印或更新违反约束的行），这不是一个很好的解决方案。但是，如果您只是想抓住它并忽略违规行，那么这应该可以完成工作。
