当我们向一个表中load(insert)数据时,如果遇到错误(如,违反了PK),这时语句则会终止并rollback所有.这样既浪费了时间又占了系统资源.那么有没有办法,当遇到错误时不让语句终止,让其跳过错误继续执行并将其错误记录下来呢?

答案是肯定的,下面请看测试案例.


YXYUP@dbatest>desc t_test;
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ID                                                                       NOT NULL NUMBER


YXYUP@dbatest>alter table t_test drop column name;

Table altered.

YXYUP@dbatest>begin
  2  for i in 1..10
  3  loop
  4  execute immediate 'insert into t_test values(:x)' using i;
  5  end loop;
  6  insert into t_test values(1);
  7  commit;
  8  end;
  9  /
begin
*
ERROR at line 1:
ORA-00001: unique constraint (YXYUP.PK_ID) violated
ORA-06512: at line 6


YXYUP@dbatest>exec dbms_errlog.create_error_log('T_TEST') ;

PL/SQL procedure successfully completed.


---用dbms_errlog.create_error_log创建用于记录错误的表,表名默认是ERR$_加上要操作的表,如下.

YXYUP@dbatest>desc err$_t_test;
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ORA_ERR_NUMBER$                                                                   NUMBER
 ORA_ERR_MESG$                                                                     VARCHAR2(2000)
 ORA_ERR_ROWID$                                                                    ROWID
 ORA_ERR_OPTYP$                                                                    VARCHAR2(2)
 ORA_ERR_TAG$                                                                      VARCHAR2(2000)
 ID                                                                                VARCHAR2(4000)

YXYUP@dbatest>select * from err$_t_test;

no rows selected




YXYUP@dbatest>begin
  2  for i in 1..10
  3  loop
  4  execute immediate 'insert into t_test values(:x)' using i;
  5  end loop;
  6  insert into t_test values(1);
  7  commit;
  8  end;
  9  /
begin
*
ERROR at line 1:
ORA-00001: unique constraint (YXYUP.PK_ID) violated
ORA-06512: at line 6


YXYUP@dbatest>begin
  2  for i in 1..10
  3  loop
  4  execute immediate 'insert into t_test values(:x)' using i;
  5  end loop;
  6  insert into t_test values(1)
  7  LOG ERRORS
  8  REJECT LIMIT UNLIMITED;
  9  commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

YXYUP@dbatest>select * from t_test;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.



YXYUP@dbatest> select  ORA_ERR_NUMBER$,ORA_ERR_MESG$,ORA_ERR_OPTYP$ ,ID from  err$_t_test

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                ORA_ERR_OPTYP$       ID
--------------- ------------------------------------------------------------ -------------------- ----------
              1 ORA-00001: unique constraint (YXYUP.PK_ID) violated          I                    1