PL/SQL开发人员如何获取使插入失败的行? [英] PL/SQL developer how to get the row that made the insert fail?

问题描述

我正在做一种方法，该方法插入具有唯一列的表中.我不知道是否可以访问导致插入失败的插入值.

I'm doing a method that inserts into the table which has a unique column. What I don't know is if I can access the insert value that made the insert fail.

例如:

table1(id,name, phone);

name is unique.

insert (1,a,123);

insert (2,a,1234);

我想要的是在第二次插入时返回ID值"1"而不必再次查询.

What I want is when I do the second insert I to return the id value '1' without having to recur to a query.

谢谢.

推荐答案

在oracle 10g r2中，您可以使用日志错误子句rel ="nofollow">插入命令以在单独的表中记录错误.这是一个示例:

From oracle 10g r2 you can use log errors clause of insert command to log errors in a separate table. Here is an example:

SQL> create table test_table(
  2    id   number primary key,
  3    col1 varchar2(7)
  4  )
  5  ;

Table created


-- creates a table for logging errors (table name will be prefaced with err$_)
SQL> begin dbms_errlog.create_error_log('TEST_TABLE'); end;
  2  /

PL/SQL procedure successfully completed

-- violates primary key constraint
SQL> insert into test_table(id, col1)
  2  (  select 1, level
  3      from dual
  4    connect by level <= 3)
  5    log errors reject limit unlimited;

1 row inserted

SQL> commit;

SQL> select * from test_table;

        ID COL1
---------- -------
         1 1


SQL> select * from err$_test_table;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                           ORA_ERR_ROWID$  ORA_ERR_OPTYP$ ORA_ERR_TAG$ ID  COL1
--------------- ------------------------------------------------------------------------------------------------------------
              1 ORA-00001: unique constraint (HR.SYS_C008315) violated  I                                            1     2
              1 ORA-00001: unique constraint (HR.SYS_C008315) violated  I                                            1     3

这篇关于PL/SQL开发人员如何获取使插入失败的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！