插入游标循环和异常处理-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. 当异常发生时，我也想知道哪个记录未能插入。我希望在我的异常中捕捉到这一点，并在异常处理程序中调用一个函数，该函数将把这些信息插入到Error表中以进行进一步的调查。

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

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