DML Error Logging 特性

本文介绍了Oracle 10g R2中的DML Error Logging特性,该特性能够在遇到DML异常时继续执行有效的数据操作,避免整个操作因部分失败而全部回滚。文中详细解释了DML Error Logging的语法、使用限制,并提供了INSERT、UPDATE、MERGE和DELETE操作的具体示例。

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

最近的项目中发现处理DML Error 时,逐条逐条处理1千多条的数据从临时表 insert 到正式表需要差不多1分钟的时间,性能相当低下,
而Oracle 10g中的DML error logging对于DML异常处理性能卓著。原本打算写篇关于这个特性的文章,正好有经典篇章,于是乎,索性翻译供大
家参考,有不尽完美之处,请大家拍砖。
缺省情况下,一个DML命令失败的时候,在侦测到错误之前,不论成功处理了多少条记录,都将将使得整个语句回滚。在使用DML error log
之前,针对单行处理首选的办法是使用批量SQL FORALL 的SAVE EXCEPTIONS子句。而在Oracle 10g R2时,DML error log特性使得该问题得以解
决。通过为大多数INSERT,UPDATE,MERGE,DELETE语句添加适当的LOG ERRORS子句,不论处理过程中是否出现错误,都可以使整个语句成功执行。
这篇文章描述了DML ERROR LOGGING操作特性,并针对每一种情形给出示例。

一、语法
对于INSERT, UPDATE, MERGE 以及 DELETE 语句都使用相同的语法
LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED]

可选的INTO子句允许指定error logging table 的名字。如果省略它,则记录日志的表名的将以"ERR$_"前缀加上基表名来表示。

simple_expression表达式可以用于指定一个标记,更方便去判断错误。simple_expression能够为一个字符串或任意能转换成字符串的函数

REJECT LIMIT 通常用于判断当前语句所允许出现的最大错误数。缺省值是0,最大值则是使用UNLIMITED关键字。对于并行DML操作而言,REJECT LIMIT
会应用到每个并行服务器。

二、使用限制
下列情形使得DML error logging 特性失效
延迟约束特性
Direct-path INSERT 或MERGE 引起违反唯一约束或唯一索引
UPDATE 或 MERGE 引起违反唯一约束或唯一索引

除此之外,对于LONG,LOB,以及对象类型也不被支持。即使是一个包含这些列的表被作为错误日志记录目标表。

三、示例
下面的代码创建表并填充数据用于演示。
-- Create and populate a source table.                                                        
                                                                                              
CREATE TABLE source                                                                           
(                                                                                             
   id                  NUMBER( 10 ) NOT NULL                                                  
  ,code                VARCHAR2( 10 )                                                         
  ,description         VARCHAR2( 50 )                                                         
  ,CONSTRAINT source_pk PRIMARY KEY( id )                                                     
);                                                                                            
                                                                                              
DECLARE                                                                                       
   TYPE t_tab IS TABLE OF source%ROWTYPE;                                                     
                                                                                              
   l_tab               t_tab := t_tab( );                                                     
BEGIN                                                                                         
   FOR i IN 1 .. 100000                                                                       
   LOOP                                                                                       
      l_tab.EXTEND;                                                                           
      l_tab( l_tab.LAST ).id            := i;                                                 
      l_tab( l_tab.LAST ).code          := TO_CHAR( i );                                      
      l_tab( l_tab.LAST ).description   := 'Description for ' || TO_CHAR( i );                
   END LOOP;                                                                                  
                                                                                              
   -- For a possible error condition.                                                         
   l_tab( 1000 ).code    := NULL;                                                             
   l_tab( 10000 ).code   := NULL;                                                             
                                                                                              
   FORALL i IN l_tab.FIRST .. l_tab.LAST                                                      
      INSERT INTO source                                                                      
      VALUES l_tab( i );                                                                      
                                                                                              
   COMMIT;                                                                                    
END;                                                                                          
/                                                                                             
                                                                                              
EXEC DBMS_STATS.gather_table_stats(USER, 'source', cascade => TRUE);                          
-- Create a destination table.                                                                
                                                                                              
CREATE TABLE dest                                                                             
(                                                                                             
   id                  NUMBER( 10 ) NOT NULL                                                  
  ,code                VARCHAR2( 10 ) NOT NULL                                                
  ,description         VARCHAR2( 50 )                                                         
  ,CONSTRAINT dest_pk PRIMARY KEY( id )                                                       
);                                                                                            
                                                                                              
-- Create a dependant of the destination table.                                               
                                                                                              
CREATE TABLE dest_child                                                                       
(                                                                                             
   id                  NUMBER                                                                 
  ,dest_id             NUMBER                                                                 
  ,CONSTRAINT child_pk PRIMARY KEY( id )                                                      
  ,CONSTRAINT dest_child_dest_fk FOREIGN KEY( dest_id ) REFERENCES dest( id )                 
);                                                                                            
注意,code列在source 表中是可选,而在dest 表中是强制的

一旦基表创建之后,如果需要使用DML error logging 特性,则必须为该基表创建一个日志表用于记录基于该表上的DML错误。错误日志表能够
手动创建或者通过包中的CREATE_ERROR_LOG存储过程来创建。如下所示:
-- Create the error logging table.

BEGIN
   DBMS_ERRLOG.create_error_log( dml_table_name => 'dest' );
END;
/

pl/SQL procedure successfully completed.

--缺省情况下,创建的日志表基于当前schema。日志表的所有者以及日志名字,表空间名字也可以单独指定。缺省的日志表的名字基于基表并以
--"ERR$_"前缀开头。

SELECT owner, table_name, tablespace_name
FROM   all_tables
WHERE  owner = 'TEST';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST                           DEST                           USERS
TEST                           DEST_CHILD                     USERS
TEST                           ERR$_DEST                      USERS
TEST                           SOURCE                         USERS

4 rows selected.

--日志表的结构以及数据类型和所允许的最大长度依赖于基表,如下所示:
SQL> DESC err$_dest
 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)
 CODE                                       VARCHAR2(4000)
 DESCRIPTION                                VARCHAR2(4000)
1、INSERT 操作
在前面创建演示表时,对于source表来说,其code 列可以为NULL,而dest表的code则不允许为NULL。在填充source表时,设置了两行为NULL的记录。
如果我们尝试从source 表复制数据到dest条,将获得下列错误信息
INSERT INTO dest
SELECT *
FROM   source;

SELECT *
       *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

--source 表为NULL的两行将引起整个insert 语句回滚,无论在错误之间有多少条语句被成功插入。通过添加DML error logging 子句,则允许我们
--对那些有效数据实现成功插入。

INSERT INTO dest
SELECT *
FROM   source
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;

99998 rows created.


--那些未能成功插入的记录将被记录在ERR$_DEST中,并且也记录了错误的原因。
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM   err$_dest
WHERE  ora_err_tag$ = 'INSERT';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

2 rows selected.
2、UPDATE 操作
下面的代码将尝试去更新1-10行的code列,其中8行的code值设置为自身,而第9与第10行设置为NULL。
UPDATE dest
SET    code = DECODE(id, 9, NULL, 10, NULL, code)
WHERE  id BETWEEN 1 AND 10;
       *
ERROR at line 2:
ORA-01407: cannot update ("TEST"."DEST"."CODE") to NULL


--如我们所期待的那样,语句由于code列不允许为NULL而导致操作失败。同样,通过添加DML erorr logging子句允许我们完成有效记录的操作

UPDATE dest
SET    code = DECODE(id, 9, NULL, 10, NULL, code)
WHERE  id BETWEEN 1 AND 10
LOG ERRORS INTO err$_dest ('UPDATE') REJECT LIMIT UNLIMITED;

8 rows updated.

--同样地,update操作失败的行以及失败原因被记录在ERR$_DEST 表
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM   err$_dest
WHERE  ora_err_tag$ = 'UPDATE';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
3、MERGE 操作
下面的代码从dest表删除一些行,然后尝试从source 表合并数据到dest表
DELETE FROM dest
WHERE  id > 50000;

MERGE INTO dest a
    USING source b
    ON (a.id = b.id)
  WHEN MATCHED THEN
    UPDATE SET a.code        = b.code,
               a.description = b.description
  WHEN NOT MATCHED THEN
    INSERT (id, code, description)
    VALUES (b.id, b.code, b.description);
                  *
ERROR at line 9:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")


--merge操作同样由于not null约束导致导致操作失败并且回滚。
--下面为其添加DML error logging 允许merge操作完成
MERGE INTO dest a
    USING source b
    ON (a.id = b.id)
  WHEN MATCHED THEN
    UPDATE SET a.code        = b.code,
               a.description = b.description
  WHEN NOT MATCHED THEN
    INSERT (id, code, description)
    VALUES (b.id, b.code, b.description)
  LOG ERRORS INTO err$_dest ('MERGE') REJECT LIMIT UNLIMITED;

99998 rows merged.


--更新操作失败的行以及失败原因同样被记录在ERR$_DEST 表中
COLUMN ora_err_mesg$ FORMAT A70
SELECT ora_err_number$, ora_err_mesg$
FROM   err$_dest
WHERE  ora_err_tag$ = 'MERGE';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")
           1400 ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")

2 rows selected.
4、DELETE 操作
DEST_CHILD 表有一个到dest表的外键约束,因此如果我们基于DEST表添加一些数据到dest_child,然后从dest删除记录将产生错误。
INSERT INTO dest_child (id, dest_id) VALUES (1, 100);
INSERT INTO dest_child (id, dest_id) VALUES (2, 101);

DELETE FROM dest;
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated - child record found



--对于Delete操作,同样可以添加DML error logging子句来记录错误使得整个语句成功执行 。
DELETE FROM dest
LOG ERRORS INTO err$_dest ('DELETE') REJECT LIMIT UNLIMITED;

99996 rows deleted.


--下面是Delete操作失败的日志以及错误原因。
COLUMN ora_err_mesg$ FORMAT A69
SELECT ora_err_number$, ora_err_mesg$
FROM   err$_dest
WHERE  ora_err_tag$ = 'DELETE';

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- ---------------------------------------------------------------------
           2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated -
                child record found

           2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK) violated -
                child record found

2 rows selected.

四、后记
1、DML error logging特性使用了自治事务,因此不论当前的主事务是提交或回滚,其产生的错误信息都将记录在对应的日志表。
2、DML error logging使得错误处理得以高效实现,尽管如此,如果在操作中,很多表需要DML操作,尤其是数据迁移时,使得每一个表都
需要创建一个对应的日志表。做了一个测试,可以将日志表的一些基表列删除,保留主要列,日志依然可以成功记录以缩小日志大小。
3、能否将多张日志表合并到一张日志表,然后每一行数据中添加对应的表名以及主键等信息以鉴别错误,这样子的话,仅仅用少量的日志
表即可实现记录多张表上的DML error。这个还没有来得及测试,This is a question。

五、使用FORALL 的SAVE EXCEPTIONS子句示例

FORALL 之 SAVE EXCEPTIONS 子句应用一例

<think>好的,我现在需要分析用户提供的Python代码片段的功能。用户给出的代码是一个方法,名为`execute_on_db`,属于某个类的成员方法。首先,我会仔细阅读代码的每一部分,理解每个语句的作用,然后综合起来确定这个方法的主要功能。 首先,方法定义是`def execute_on_db(self, query):`,这表明这是一个实例方法,接受一个`query`参数,应该是SQL查询语句。接下来,第一行是`logging.debug(f"executing query: {query}")`,这里使用了日志记录,记录正在执行的查询,方便调试时查看执行的SQL语句。 接下来是`with self.db_engine.connect().execution_options(autocommit=True) as con:`,这部分使用了上下文管理器来管理数据库连接。`self.db_engine`应该是一个SQLAlchemy的数据库引擎实例,通过`connect()`方法建立连接。`execution_options(autocommit=True)`设置执行选项为自动提交,这可能意味着每个操作后自动提交事务,但需要注意是否与后面的`con.commit()`冲突,因为如果已经设置了自动提交,手动提交可能多余或者导致错误。 然后,`reply = con.execute(text(query))`,这里使用连接对象执行SQL语句。`text(query)`将字符串转换为SQLAlchemy的文本子句,防止SQL注入,并确保正确的参数绑定。执行后返回的结果赋值给`reply`,通常这会是一个结果代理对象,可以用于获取查询结果,比如获取行数或遍历结果集。 最后,`con.commit()`显式提交事务。不过,之前已经设置了`autocommit=True`,这里是否需要手动提交可能存在疑问。根据SQLAlchemy文档,当使用`autocommit=True`时,对于非DDL语句(如SELECT),可能不会自动提交,而像INSERT、UPDATE这样的DML语句可能需要显式提交。或者,可能这里的设置是为了确保无论是否自动提交,都进行显式提交,以避免事务未提交导致的数据不一致。 综合来看,这个方法的主要功能是执行传入的SQL查询,并处理数据库连接和事务提交。可能的用途包括执行数据查询、数据修改(插入、更新、删除)或者执行DDL语句(创建表等)。需要注意自动提交和手动提交的配置是否合理,避免重复提交或者未提交的问题。 另外,考虑可能存在的问题,比如SQL注入的风险。虽然使用了`text(query)`,但如果是直接拼接用户输入的查询,仍然存在风险。正确的做法应使用参数化查询,而不是直接执行原始字符串。此外,错误处理方面,代码中没有看到try-except块,可能导致异常未被捕获,影响程序稳定性。例如,如果查询语法错误,或者连接失败,程序可能会崩溃。 总结起来,这个方法负责安全地执行SQL语句,记录日志,并确保事务正确提交,但可能在错误处理和SQL注入防护上有改进空间。需要根据上下文和实际使用情况进一步评估。</think>以下是对代码段`def execute_on_db(self, query):`的功能解析: --- ### 一、核心功能 这段代码是**数据库操作封装方法**,主要实现: 1. **执行任意SQL语句**(增删改查均可) 2. **自动管理数据库连接** 3. **控制事务提交** 4. **记录操作日志** --- ### 二、逐行解析 #### 1. 日志记录 ```python logging.debug(f"executing query: {query}") ``` - **作用**:记录DEBUG级日志,格式示例: ```log DEBUG: executing query: UPDATE users SET name='John' WHERE id=1 ``` - **意义**:便于调试时追踪SQL执行历史 #### 2. 连接管理 ```python with self.db_engine.connect().execution_options(autocommit=True) as con: ``` - **关键点**: - `self.db_engine`:通常是SQLAlchemy的`create_engine()`对象 - `with`上下文:自动处理连接的**开启与关闭** - `autocommit=True`:设置自动提交模式(对DML语句立即生效) #### 3. 执行查询 ```python reply = con.execute(text(query)) ``` - **技术细节**: - `text(query)`:将字符串包装为SQLAlchemy可识别的文本子句 - 防止SQL注入的**正确做法**应使用参数化查询: ```python con.execute(text("SELECT * FROM users WHERE id=:id"), {"id": user_id}) ``` #### 4. 显式提交 ```python con.commit() ``` - **注意**:当`autocommit=True`时: - DDL语句(如`CREATE TABLE`)会自动提交 - DML语句(如`INSERT/UPDATE`)是否需要此语句取决于数据库配置 - 显式提交可确保**事务原子性** --- ### 三、潜在风险 ```mermaid graph TD A[直接执行原始查询] --> B{风险类型} B --> C[SQL注入攻击] B --> D[语法错误导致中断] B --> E[敏感数据泄露] C --> F[建议使用参数化查询] D --> G[添加异常捕获] E --> H[限制查询权限] ``` --- ### 四、改进建议 #### 1. 安全增强 ```python # 参数化查询示例 def safe_execute(self, sql, params=None): stmt = text(sql).bindparams(**params) con.execute(stmt) ``` #### 2. 异常处理 ```python try: reply = con.execute(text(query)) except SQLAlchemyError as e: logging.error(f"Database error: {str(e)}") raise ``` #### 3. 结果处理 ```python # 获取不同结果类型 if reply.returns_rows: return [dict(row) for row in reply] else: return reply.rowcount # 返回影响行数 ``` --- ### 五、典型使用场景 1. **数据迁移脚本**:批量执行SQL文件 2. **管理后台**:手动执行自定义查询 3. **单元测试**:初始化/清理测试数据 --- ### 六、技术关联 | 组件 | 作用 | 相关文档 | |-------------------|-----------------------------|--------------------------------| | SQLAlchemy Core | 提供数据库连接和SQL构造能力 | [官方文档](https://docs.sqlalchemy.org/) | | logging模块 | 实现操作审计追踪 | [Python日志指南](https://docs.python.org/3/howto/logging.html) | | 上下文管理器 | 确保资源自动释放 | [PEP 343](https://peps.python.org/pep-0343/) | 建议在实际使用中增加事务回滚(`con.rollback()`)和连接池配置,以提升健壮性和性能。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值