Oracle 批量insert报错处理

最新推荐文章于 2024-01-31 12:01:47 发布

原创最新推荐文章于 2024-01-31 12:01:47 发布 · 5.7k 阅读

1 ·

CC 4.0 BY-SA版权

SQL语句学习专栏收录该内容

48 篇文章

订阅专栏

本文介绍了一种在数据库中批量插入数据时，利用SQL特性处理潜在错误的方法，并通过创建错误日志表来记录失败的数据行，以便后续分析与修复。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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