Oracle 使用多个forall dml语句维护事务中的数据交互

以下是使用2个forall dml (即插入和更新) 的过程的代码的一部分。
如果批量插入中的任何txn在bulk_exceptions中失败，则不应提交或不进行forall中的核心响应更新txn。
Pl有助于实现数据的完整性。

CREATE OR REPLACE PROCEDURE SP_XXX AS

BEGIN
 FORALL i in INDICES OF T1_MAP SAVE EXCEPTIONS
 INSERT INTO TABLE1
 (
col_list.. )
 VALUES
 (
values list );
 EXCEPTION
   WHEN dml_errors THEN
    error_cnt := SQL%BULK_EXCEPTIONS.COUNT;
   
    FOR i IN 1..error_cnt LOOP
     error_cd  :=  SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
     error_msg := sqlerrm(-error_cd);
     --dbms_output.put_line('error : '||replace(to_char(sqlerrm(-error_cd)),chr(10),' ')); 
     INSERT INTO my_log
     (
      TABLE_ID,
      SQL_CODE,
      SQL_ERROR,
      REMARKS,
      TIME_STAMP
      )
      VALUES
     (
      9002,
      error_cd,
      error_msg,
      'SP_XXX',
      systimestamp
     );
     END LOOP;
  END;   
 
BEGIN
FORALL i in INDICES OF T1_MAP SAVE EXCEPTIONS
UPDATE T2
SET GROUP_TXN_NO=T1_MAP(i).MAP_SEQ_NO
WHERE condition..
EXCEPTION
 WHEN dml_errors THEN
    error_cnt := SQL%BULK_EXCEPTIONS.COUNT;
    FOR i IN 1..error_cnt 
    LOOP 
      error_cd  :=  SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
      error_msg := sqlerrm(-error_cd);
     INSERT INTO my_log
    (
      TABLE_ID,
      SQL_CODE,
      SQL_ERROR,
      REMARKS,
      TIME_STAMP
      )
      VALUES
     (
      9003,
      -error_cd,
      error_msg,
      'SP_XXX',
      systimestamp
     );
     END LOOP;
  END;   
 
COMMIT;
END SP_XXX;

所以你不想为插入到t1时引发异常的所有数组元素更新t2？

只需删除DML错误的第一个异常处理程序中的这些数组元素。

create table t1 (
  c1 int, c2 int check ( c2 > 0)
);
create table t2 (
  c1 int, c2 int
);

insert into t2 values ( 1, null );
insert into t2 values ( 2, null );
insert into t2 values ( 3, null );
commit;

set serveroutput on
declare
  dml_errors exception;
  pragma exception_init ( dml_errors, -24381 );
  type trec is table of t1%rowtype
    index by pls_integer;
  vals trec;
begin
  vals(1).c1 := 1;
  vals(1).c2 := 1;
  vals(2).c1 := 2;
  vals(2).c2 := -99;
  vals(3).c1 := 3;
  vals(3).c2 := 42;
  
  begin 
    forall i in 1 .. vals.count save exceptions
      insert into t1 values ( vals(i).c1, vals(i).c2 );
  exception
    when dml_errors then
      for err in 1 .. sql%bulk_exceptions.count loop
        vals.delete ( sql%bulk_exceptions(err).error_index );
      end loop;
  end;
    
  forall i in indices of vals 
    update t2 
    set    c2 = vals(i).c2
    where  c1 = vals(i).c1;
    
  dbms_output.put_line ( ' Rows ' || sql%rowcount );

end;
/

Rows 2

select * from t2;

C1   C2       
    1         1 
    2     
    3        42