在EXCEPTION之后继续MERGE [英] Continue MERGE after EXCEPTION

问题描述

可以在 EXCEPTION 之后继续执行 MERGE ？

  MERGE INTO copy_emp c 
使用员工e 
 ON（c.employee_id = e.employee_id）
当匹配
 UPDATE SET 
 c.first_name = e.first_name，
 c.last_name = e.last_name，
 c.email = e.email，
 c.phone_number = e.phone_number， 
 c.hire_date = e.hire_date，
 c.job_id = e.job_id，
 c.salary = e.salary，
 c.commission_pct = e.commission_pct，
 c.manager_id = e.manager_id，
 c.department_id = e.department_id 
当未匹配
插入值（e.employee_id，e.first_name，e.last_name，
 e.email，e.phone_number，e.hire_date，e.job_id，
 e.salary，e.commission_pct，e.manager_id，
 e.department_id）; 
 
 EXCEPTION 
其他其他
  - 
  

除了检索抛出异常并重新启动 MERGE的行的ID 忽略此ID我看不到如何做。

我正在使用Oracle数据库10。

解决方案

您可以使用 error_logging_clause 。 （链接用于插入，因为在 MERGE的文档中它表示它具有与插入相同的行为。

对于您的情况：

   - 创建您的日志表
 EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG（'copy_emp'，'TAB_ERR_COPY_EMP'）; 
 
 
 MERGE INTO copy_emp c 
使用员工e 
 ON（c.employee_id = e.employee_id）
当匹配
更新SET 
 c.first_name = e.first_name，
 c.last_name = e.last_name，
 c.email = e.email，
 c.phone_number = e.phone_number，
 c.hire_date = e.hire_date，
 c。 job_id = e.job_id，
 c.salary = e.salary，
 c.commission_pct = e.commission_pct，
 c.manager_id = e.manager_id，
 c.department_id = e.depa rtment_id 
当不符合
插入值（e.employee_id，e.first_name，e.last_name，
 e.email，e.phone_number，e.hire_date，e.job_id，
 e.salary，e.commission_pct，e.manager_id，
 e.department_id）
 LOG ERRORS INTO TAB_ERR_COPY_EMP（'TAG_STATEMENT'）REJECT LIMIT 100; 
  

请注意，error_logging_clause有一些限制。从文档中：

1. 以下条件导致语句失败并返回
   ，而不调用错误记录功能：

   
   
   
   
   

   • 违反延期约束。

   
   

   • -path INSERT或MERGE操作，引发唯一的
     约束或索引违例。

   
   

   • 任何更新操作UPDATE或MERGE引发唯一约束
     或索引违规）

   
   



2. 您无法跟踪LONG，LOB的错误记录表中的错误，或
   对象类型列。但是，作为
   DML操作的目标的表可以包含这些类型的列。

   
   
   
   
   

   • 如果你创建或修改相应的错误记录表，以便
     它包含不受支持的类型的列，如果
     列的名称对应于目标DML表中不受支持的列，则
     如果错误日志记录表不包含任何不支持的列
     类型，则所有DML错误都将被记录，直到达到
     错误的拒绝限制。对于发生错误的行，错误记录表中的
     对应列的列与控制信息沿
     记录。

   
   
   / li>
   

Is it possible to continue a MERGE after an EXCEPTION ?

MERGE INTO copy_emp c
     USING employees e
     ON (c.employee_id = e.employee_id)
   WHEN MATCHED THEN
     UPDATE SET
       c.first_name     = e.first_name,
       c.last_name      = e.last_name,
       c.email          = e.email,
       c.phone_number   = e.phone_number,
       c.hire_date      = e.hire_date,
       c.job_id         = e.job_id,
       c.salary         = e.salary,
       c.commission_pct = e.commission_pct,
       c.manager_id     = e.manager_id,
       c.department_id  = e.department_id
   WHEN NOT MATCHED THEN
     INSERT VALUES(e.employee_id, e.first_name, e.last_name,
          e.email, e.phone_number, e.hire_date, e.job_id,
          e.salary, e.commission_pct, e.manager_id,
          e.department_id);

EXCEPTION
    WHEN OTHERS THEN
        -- ?????

Apart retrieve the ID of the row who threw the exception and restart the MERGE ignoring this ID I do not see how to do.

I'm using Oracle Database 10.

解决方案

You can do this with the error_logging_clause. (The link is for the insert because in the documentation of MERGE it says that it has the same behavior as an insert.

For your case :

-- You create your Log Table
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('copy_emp', 'TAB_ERR_COPY_EMP');


    MERGE INTO copy_emp c
     USING employees e
     ON (c.employee_id = e.employee_id)
   WHEN MATCHED THEN
     UPDATE SET
       c.first_name     = e.first_name,
       c.last_name      = e.last_name,
       c.email          = e.email,
       c.phone_number   = e.phone_number,
       c.hire_date      = e.hire_date,
       c.job_id         = e.job_id,
       c.salary         = e.salary,
       c.commission_pct = e.commission_pct,
       c.manager_id     = e.manager_id,
       c.department_id  = e.department_id
   WHEN NOT MATCHED THEN
     INSERT VALUES(e.employee_id, e.first_name, e.last_name,
          e.email, e.phone_number, e.hire_date, e.job_id,
          e.salary, e.commission_pct, e.manager_id,
          e.department_id)
    LOG ERRORS INTO TAB_ERR_COPY_EMP('TAG_STATEMENT') REJECT LIMIT 100;

Please note that there are some limitations for the error_logging_clause. From the documentation :

1. The following conditions cause the statement to fail and roll back without invoking the error logging capability:

   • Violated deferred constraints.

   • Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.

   • Any update operation UPDATE or MERGE that raises a unique constraint or index violation).

2. You cannot track errors in the error logging table for LONG, LOB, or object type columns. However, the table that is the target of the DML operation can contain these types of columns.

   • If you create or modify the corresponding error logging table so that it contains a column of an unsupported type, and if the name of that column corresponds to an unsupported column in the target DML table, then the DML statement fails at parse time.

   • If the error logging table does not contain any unsupported column types, then all DML errors are logged until the reject limit of errors is reached. For rows on which errors occur, column values with corresponding columns in the error logging table are logged along with the control information.

这篇关于在EXCEPTION之后继续MERGE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！