Oracle 批量insert报错处理

﹏ヽ暗。殇╰゛Y 2022-08-20 10:26 617阅读 0赞

你可能会碰到这样的业务，大批量插入数据，但可能有一两条数据有问题，导致插入失败，回滚就太不划算了。可以使用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