插入游标循环和异常处理 - Oracle

Question

我有一个名为 DUD 的表，它几乎是静态的（这意味着一旦插入数据，它就不会改变）。我从 DUD 查询数据并填充暂存表 CAR，Webmethods 每天都会从中进行轮询。

通常每笔交易有 10 条记录。每天有两笔交易。

我已经编写了一个游标来执行此操作，并且我对逻辑感到满意。

输出将如下所示：

TRANSID   A    B    C   cnt
------   ---  ---   --  ---
A123     JIM  NY   ACT   1
A123     BOB  CA   ACT   2
A123     PIN  GA   ACT   3
--------------------------
A124     MIK  CA   ACT   1
A124     JON  MA   ACT   2
A124     CON  MY   ACT   3
A124     JIB  CA   ACT   4

真正让我担心的问题是：

1. 如果循环中的插入失败，它应该回滚此事务中所做的所有插入，并且不会以部分插入的记录或孤立的记录结束 交易。我仅在循环完成后提交，没有引发异常。

2. 当发生异常时，我也想知道哪条记录插入失败。我希望在异常中捕获这一点，并在异常处理程序中调用一个函数，将该信息插入到错误表中以供进一步调查。

3. 数据库中禁用自动提交。但是oracle会将通过循环进行的所有插入视为一个事务或独立事务并立即插入吗？

代码

  DECLARE  TYPE message_info 
  IS 
    RECORD 
    ( 
      message_code INTEGER, 
      message      VARCHAR2(500)); 
    msg MESSAGE_INFO; 
    tranid  NUMBER; 
    p_error EXCEPTION; 
    CURSOR b1 IS 
      SELECT * 
      FROM   dud 
      WHERE  dud.DATE = SYSDATE 
      AND    dud.status='ACTIVE'; 

  BEGIN 
    IF *CHECK SOME condition* 
      BEGIN 
        tranid = seq_transid.NEXTVAL; 
        --- Transaction id is unique per transaction. 
        --- All 10 records will have same transaction id. 
        FOR b1 IN c1 
        LOOP 
          i=b1%rowcount; 
          INSERT INTO car 
                      ( 
                                  transid, 
                                  a, 
                                  b, 
                                  c, 
                                  cnt 
                      ) 
                      VALUES 
                      ( 
                                  tranid, 
                                  b1.a, 
                                  b1.b, 
                                  b1.c, 
                                  i 
                      ); 

        END LOOP; 
      EXCEPTION 
      WHEN OTHERS THEN 
        ROLLBACK; 
        msg.message := 'Unable to insert into CAR Table'; 
        RAISE p_error; 
      END; 
      COMMIT; 
    EXCEPTION 
    WHEN p_error THEN 
      error.post_msg (msg.message, SQLCODE,SQLERRM,USER); 
    END IF; 
  END;

Answer 1

在这种情况下，您也可以使用 FORALL 语句...

您正在使用游标，并且在循环中您正在插入表中。
您可以直接一次性插入所有交易。这也会提高代码的性能，并且还可以确保所有事务都插入或没有插入......

Answer 2

基本上，在您描述的情况下，应该不会有问题，因为您仅在回滚后提交。
但也许最好使用 AUTONOMOUS_TRANSACTION 作为记录的函数错误。一般来说，应该避免使用它，但由于您需要执行一些原子事务（用于记录记录），因此它可能会更好，因此您将确保此提交不会提交循环中所做的插入。