首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >【DB笔试面试498】当DML语句中有一条数据报错时,如何让该DML语句继续执行?

【DB笔试面试498】当DML语句中有一条数据报错时,如何让该DML语句继续执行?

作者头像
小麦苗DBA宝典
发布2019-09-30 16:09:40
8270
发布2019-09-30 16:09:40
举报
题目部分

在Oracle中,当DML语句中有一条数据报错时,如何让该DML语句继续执行?

答案部分

当一个DML语句运行的时候,如果遇到了错误,那么这条语句会进行回滚,就好像没有执行过。对于一个大的DML语句而言,如果个别数据错误而导致整个语句的回滚,那么会浪费很多的资源和运行时间。所以,从Oracle 10g开始Oracle支持记录DML语句的错误,而允许语句自动继续执行。这个功能可以使用DBMS_ERRLOG包实现。

LHR@orclasm > CREATE TABLE T1 AS SELECT ROWNUM A,ROWNUM B FROM DBA_SEGMENTS WHERE ROWNUM <=10;

Table created.

LHR@orclasm > CREATE TABLE T2 AS SELECT ROWNUM A,ROWNUM B FROM DBA_SEGMENTS WHERE ROWNUM <=20;

Table created.

LHR@orclasm > ALTER TABLE T1 ADD CONSTRAINT PK_T1_A PRIMARY KEY(A);

Table altered.

LHR@orclasm >  INSERT INTO T1 SELECT * FROM T2;
 INSERT INTO T1 SELECT * FROM T2
*
ERROR at line 1:
ORA-00001: unique constraint (LHR.PK_T1_A) violated


LHR@orclasm >  SELECT COUNT(1) FROM T1;

  COUNT(1)
----------
        10

LHR@orclasm >  SELECT COUNT(1) FROM T2;

  COUNT(1)
----------
        20

可以看到,由于插入的数据违反了唯一性约束,导致了Oracle报错。下面创建记录DML错误信息的记录表,通过DBMS_ERRLOG包来进行创建,而这个包目前只包括这一个过程:

LHR@orclasm > DESC DBMS_ERRLOG
PROCEDURE CREATE_ERROR_LOG
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DML_TABLE_NAME                 VARCHAR2                IN
 ERR_LOG_TABLE_NAME             VARCHAR2                IN     DEFAULT
 ERR_LOG_TABLE_OWNER            VARCHAR2                IN     DEFAULT
 ERR_LOG_TABLE_SPACE            VARCHAR2                IN     DEFAULT
 SKIP_UNSUPPORTED               BOOLEAN                 IN     DEFAULT

若不指定ERR_LOG_TABLE_NAME参数,则创建的记录错误日志的表名为:ERR$_原表名的前25个字符。利用CREATE_ERROR_LOG来创建T1表的DML错误记录表:

SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T1','T1_ERRLOG','LHR');

PL/SQL procedure successfully completed
LHR@orclasm > DESC T1_ERRLOG;
 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)
 A                                                  VARCHAR2(4000)
 B                                                  VARCHAR2(4000)

可以看到Oracle创建的错误记录表包括错误号码ORA_ERR_NUMBER$,错误信息ORA_ERR_MESG$,记录的ROWID信息ORA_ERR_ROWID$,错误操作类型ORA_ERR_OPTYP$,错误标签ORA_ERR_TAG$,以及表中对应的列。

下面利用包含LOG ERROR语句的INSERT语句再次插入数据:

LHR@orclasm > INSERT INTO T1 SELECT * FROM T2 LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG_LHR')REJECT LIMIT UNLIMITED;

10 rows created.

LHR@orclasm >  SELECT COUNT(1) FROM T1;

  COUNT(1)
----------
        20

SELECT * FROM T1_ERRLOG;

可以看到,插入成功执行,但是插入记录为10条。从对应的错误信息表中已经包含了插入的信息。而且从错误信息表中还可以看到对应的错误号和详细错误信息,ORA_ERR_OPTYP$为错误操作类型,I表示为INSERT。

关于LOG ERRORS的语法为,INTO语句后面跟随的就是指定的错误记录表的表名。在INTO语句后面,可以跟随一个表达式“('T1_ERRLOG_LHR')”即是ORA_ERR_TAG$中存储的信息,用来设置本次语句执行的错误在错误记录表中对应的TAG。有了这个语句,就可以很轻易的在错误记录表中找到某次操作所对应的所有的错误,这对于错误记录表中包含了大量数据,且本次语句产生了多条错误信息的情况十分有帮助。只要这个表达式的值可以转化为字符串类型就可以。REJECT LIMIT则限制语句出错的数量。

LHR@orclasm > INSERT INTO T1 SELECT * FROM T2 LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT 1;
INSERT INTO T1 SELECT * FROM T2 LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT 1
*
ERROR at line 1:
ORA-00001: unique constraint (LHR.PK_T1_A) violated

可以看到,当设置的REJECT LIMIT的值小于出错记录数时,语句会报错,这时LOG ERRORS语句没有起到应有的作用,插入语句仍然以报错结束。而如果将REJECT LIMIT的限制设置大于等于出错的记录数,则插入语句就会执行成功,而所有出错的信息都会存储到LOG ERROR对应的表中。只要指定了LOG ERRORS语句,不管最终插入语句十分成功的执行完成,在错误记录表中都会记录语句执行过程中遇到的错误。比如第一个插入由于出错数目超过REJECT LIMIT的限制,这时在记录表中会存在REJECT LIMIT + 1条记录数,因此这条记录错误导致了整个SQL语句的报错。如果不管碰到多少错误,都希望语句能继续执行,那么可以设置REJECT LIMIT为UNLIMITED。需要注意的是,即使做了回滚操作,错误日志表中的记录并不会减少,因为Oracle是利用自治事务的方式插入错误记录表的。

LOG ERRORS可以用在INSERT、UPDATE、DELETE和MERGE后,但是,它有以下限制条件:

① 违反延迟约束。

② 直接路径的INSERT或MERGE语句违反了唯一约束或唯一索引(注意:从Oracle 11g开始,已经取消了该条限制)。

③ 更新操作违反了唯一约束或唯一索引。

④ 错误日志表的列不支持的数据类型包括:LONG、LONG RAW、BLOG、CLOB、NCLOB、BFILE以及各种对象类型。Oracle不支持这些类型的原因也很简单,这些特殊的类型不是包含了大量的记录,就是需要通过特殊的方法来读取,因此Oracle没有办法在SQL处理的时候将对应列的信息写到错误记录表中。

1.下面通过实验来验证不支持的操作

首先看一下违反延迟约束:

LHR@orclasm > ALTER TABLE T1 ADD CONSTRAINT PK_T1_B CHECK (B IS NOT NULL) DEFERRABLE INITIALLY DEFERRED;

Table altered.

LHR@orclasm > INSERT INTO T1 VALUES('21','') LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT UNLIMITED;

1 row created.

LHR@orclasm > commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (LHR.PK_T1_B) violated

由于延迟约束的检查在COMMIT时刻进行,而不是在DML发生的时刻,因此不会利用LOG ERRORS语句将违反结果的记录插入到记录表中,这也是很容易理解的。

下面看看直接路径违反唯一约束的情况:

LHR@orclasm > MERGE /*+append*/  INTO T1 T 
  2  USING T1   
  3  ON (T1.B=T.B)  
  4  WHEN  MATCHED THEN 
  5    UPDATE   SET T.A=1
  6  LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT UNLIMITED;

20 rows merged.
LHR@orclasm > MERGE /*+append*/  INTO T1 T 
  2  USING T1   
  3  ON (1<>1)  
  4  WHEN NOT MATCHED THEN 
  5   INSERT (a,b) VALUES (1,1)
  6  LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT UNLIMITED;

0 rows merged.

LHR@orclasm >    

可见,从Oracle 11g开始已经取消了该条限制。

最后来看看更新语句违反唯一约束的情况:

LHR@orclasm > UPDATE T1 SET A='1' WHERE A='2' LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT UNLIMITED;
UPDATE T1 SET A='1' WHERE A='2' LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT UNLIMITED
*
ERROR at line 1:
ORA-00001: unique constraint (LHR.PK_T1_A) violated

可以看到,如果更新操作导致了唯一约束或唯一索引冲突,是不会记录到错误记录表中的。

2.下面我们来看不支持的数据类型

LHR@orclasm > DROP TABLE T1_ERRLOG PURGE;

Table dropped.

LHR@orclasm >  alter table T1 add c clob;

Table altered.

LHR@orclasm >  EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T1','T1_ERRLOG');
BEGIN DBMS_ERRLOG.CREATE_ERROR_LOG('T1','T1_ERRLOG'); END;

*
ERROR at line 1:
ORA-20069: Unsupported column type(s) found: C
ORA-06512: at "SYS.DBMS_ERRLOG", line 237
ORA-06512: at line 1

可以看到,由于T1表拥有不支持的列,导致创建错误记录表的过程报错,错误提示就是T1表中包含了不支持的列。如果手工添加CLOB字段到错误记录表:

LHR@orclasm > alter table T1 DROP (c);

Table altered.

LHR@orclasm > EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('T1','T1_ERRLOG');

PL/SQL procedure successfully completed.

LHR@orclasm > alter table T1 add c clob;

Table altered.

LHR@orclasm > alter table T1_ERRLOG add c clob;

Table altered.

执行插入语句:

LHR@orclasm > INSERT INTO T1 VALUES('21','21','TEST') LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT UNLIMITED;
INSERT INTO T1 VALUES('21','21','TEST') LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT UNLIMITED
                                                        *
ERROR at line 1:
ORA-38904: DML error logging is not supported for LOB column "C"

LHR@orclasm >  UPDATE T1 SET A='22' WHERE A='2' LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT UNLIMITED;
 UPDATE T1 SET A='22' WHERE A='2' LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT UNLIMITED
                                                  *
ERROR at line 1:
ORA-38904: DML error logging is not supported for LOB column "C"

可以看到,Oracle会直接报错。

LHR@orclasm >  alter table T1_ERRLOG DROP (c);

Table altered.

LHR@orclasm > INSERT INTO T1 VALUES('1','1','TEST' ) LOG ERRORS INTO T1_ERRLOG('T1_ERRLOG')REJECT LIMIT UNLIMITED;

0 rows created.

可以看到,删除错误记录语句所不支持的列后,LOG ERRORS语句反而可以顺利执行,而且无论DML语句是否包括哪些不支持列的数据。

& 说明:

有关DBMS_ERRLOG包的更多内容介绍可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2144970/

本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2019-01-31,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DB宝 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档