如何创建一个html网页/科学新概念seo外链
你可能会碰到这样的业务，大批量插入数据，但可能有一两条数据有问题，导致插入失败，回滚就太不划算了。可以使用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