oracle中insert数据报错,Oracle 批量insert报错处理

你可能会碰到这样的业务，大批量插入数据，但可能有一两条数据有问题，导致插入失败，回滚就太不划算了。可以使用insert的一个特殊属性，如下面的例子。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> drop table test;

SQL> create table test(id number primary key);

SQL> insert into test values(1);

SQL> insert into test values(2);

SQL> insert into test values(3);

SQL> commit;

SQL> select * from test;

ID

----------

1

2

3

SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(dml_table_name => 'test',err_log_table_name=> 'test_insert_errlog') ;

dml_table_name是表名

err_log_table_name 是错误日志表，每条数据生成一条

存储过程执行后会生成一张日志表

create table TEST_INSERT_ERRLOG

(

ORA_ERR_NUMBER$ NUMBER,

ORA_ERR_MESG$   VARCHAR2(2000),

ORA_ERR_ROWID$  UROWID(4000),

ORA_ERR_OPTYP$  VARCHAR2(2),

ORA_ERR_TAG$    VARCHAR2(2000),

ID              VARCHAR2(4000)

);

comment on table TEST_INSERT_ERRLOG

is 'DML Error Logging table for "TEST"';

SQL> select rownum from dual connect by level <=4;

ROWNUM

----------

1

2

3

4

SQL> insert into test select rownum from dual connect by level <=4;

insert into test select rownum from dual connect by level <=4

*

第 1 行出现错误:

ORA-00001: 违反唯一约束条件 (LCAMTEST.SYS_C0038392)

SQL> insert into test select rownum from dual connect by level <= 4

LOG ERRORS INTO TEST_INSERT_ERRLOG('test') --这里的test就是一个标签，可以随便取

REJECT LIMIT 100; --100是允许错误100条，超过的话整条sql就失败

已创建 1 行。

SQL> commit;

SQL> select * from test;

ID

----------

1

2

3

4

SQL> col ORA_ERR_NUMBER$ format a30;

SQL> col ORA_ERR_MESG$ format a60;

SQL> col ORA_ERR_ROWID$ format a30;

SQL> col ORA_ERR_OPTYP$ format a30;

SQL> col ORA_ERR_TAG$ format a30;

SQL> col id format a10;

SQL> select * from TEST_INSERT_ERRLOG;

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                         ORA_ERR_ROWID$ ORA_ERR_OPTYP$  ORA_ ERR_TAG$  ID

--------------- ----------------------------------------------------- ------------- --------------- -------------  ----

########## ORA-00001: 违反唯一约束条件 (LCAMTEST.SYS_C0038392)                          I        test          3

########## ORA-00001: 违反唯一约束条件 (LCAMTEST.SYS_C0038392)                          I        test          1

########## ORA-00001: 违反唯一约束条件 (LCAMTEST.SYS_C0038392)                          I        test          2

update的例子：

SQL> delete from TEST_INSERT_ERRLOG;   SQL> commit;   SQL> select * from test;         ID    ----------          1          2          3          4   SQL> update test set id = 'a' where id =3     LOG ERRORS INTO TEST_INSERT_ERRLOG('test')       REJECT LIMIT 100;   SQL> select ORA_ERR_MESG$,ORA_ERR_OPTYP$ from TEST_INSERT_ERRLOG;   ORA_ERR_MESG$                       ORA_ERR_OPTYP$   ---------------------------------- ------------------   ORA-01722: 无效数字                       U