批量绑定（2.3）— FORALL

5. FORALL 的自动回滚

5.1 默认回滚机制

 

当批量发送的 DML 中，某个 DML 执行失败了，Oracle 会怎样处理呢？

 

首先我们要知道，当一个事务执行失败，并由于没有相应的异常捕获而由 Oracle 自行终止该事务后，该事务自动回滚：

 

SQL> truncate table test_forall;

 

Table truncated.

 

SQL> desc test_forall;
 Name    Null?    Type
 ------- -------- -------------
 ID               NUMBER(10)
 NAME             VARCHAR2(20)

SQL> declare
  2    type record_test_forall is record(
  3      id test_forall.id%type,
  4      name varchar2(100)
  5    );
  6    type taa_test_forall is table of record_test_forall index by pls_integer;
  7    aa_test_forall taa_test_forall;
  8  begin
  9    for n_pointer in 1..10 loop
 10      aa_test_forall(n_pointer).id := n_pointer;
 11      aa_test_forall(n_pointer).name := 'yuechaotian' || n_pointer;
 12    end loop;
 13
 14    -- change the 5th element, it'll be too long to be inserted.
 15    aa_test_forall(5).name := 'http://yuechaotian.cublog.cn';
 16    -- before execute forall, send a insert transaction
 17    insert into test_forall values(99, 'yuechaotian99');
 18
 19    forall n_pointer in 1..10
 20      insert into test_forall values aa_test_forall( n_pointer );
 21
 22    dbms_output.put_line( 'Insert rows: ' || sql%rowcount );
 23    for n_pointer in 1..10 loop
 24      dbms_output.put_line( 'Row: ' || n_pointer
 25        || ' | Result: ' ||sql%bulk_rowcount(n_pointer) );
 26    end loop;
 27  end;
 28  /
declare
*
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at line 19

 

SQL> select * from test_forall;

 

no rows selected

 

同样是上面的过程，我增加一个异常捕获，执行结果就完全不同了：

 

SQL> rollback;

 

Rollback complete.

 

SQL> declare
  2    type record_test_forall is record(
  3      id test_forall.id%type,
  4      name varchar2(100)
  5    );
  6    type taa_test_forall is table of record_test_forall index by pls_integer;
  7    aa_test_forall taa_test_forall;
  8  begin
  9    for n_pointer in 1..10 loop
 10      aa_test_forall(n_pointer).id := n_pointer;
 11      aa_test_forall(n_pointer).name := 'yuechaotian' || n_pointer;
 12    end loop;
 13
 14    -- Change the 5th element, it'll be too long to be inserted.
 15    aa_test_forall(5).name := 'http://yuechaotian.cublog.cn';
 16    -- Before execute forall, execute a insert first.
 17    insert into test_forall values(99, 'yuechaotian99');
 18
 19    forall n_pointer in 1..10
 20      insert into test_forall values aa_test_forall( n_pointer );
 21
 22    dbms_output.put_line( 'Insert rows: ' || sql%rowcount );
 23    for n_pointer in 1..10 loop
 24      dbms_output.put_line( 'Row: ' || n_pointer
 25        || ' | Result: ' ||sql%bulk_rowcount(n_pointer) );
 26    end loop;
 27  exception
 28    when others then
 29      dbms_output.put_line( 'error: ' || sqlerrm );
 30  end;
 31  /
error: ORA-01401: inserted value too large for column

 

PL/SQL procedure successfully completed.

 

SQL> select * from test_forall;

 

        ID NAME
---------- --------------------
        99 yuechaotian99
         1 yuechaotian1
         2 yuechaotian2
         3 yuechaotian3
         4 yuechaotian4

 

根据这个执行结果，我们可以总结一下 FORALL 中默认的回滚处理过程：

 

（1）当 FORALL 中的某条 DML 语句执行失败后，FORALL 终止执行，而不会跳过这个 DML 语句而执行后继的 DML 语句。就像上面的例子，执行第五个 DML 语句失败，则后继的 6-10 个可以执行成功的 DML 语句也不会被执行。

 

（2）执行失败的 DML 语句被回滚到一个隐含的 savepoint，以前批量执行的 DML 保持不变。可以这样猜测，Oracle 会在每个 DML 之间设置一个隐含的 savepoint，当某 DML 语句执行失败后，自动回滚到它上面的 savepoint 状态。就像上面的例子，1-4 个 INSERT 操作并没有被回滚。

 

（3）FORALL 执行失败后，不会回滚掉它前面的 DML 语句。所以在上面的例子中，出现了记录“99 yuechaotian99”。

 

5.2 SAVE EXCEPTIONS

 

从 Oracle9i 开始，提供了一个新的子句 SAVE EXCEPTIONS，可以保存 FORALL 执行中产生的错误，并且跳过这些执行出错的 SQL，继续执行后继的 DML 语句。

看看下面的代码，和原来相比，只是增加了 SAVE EXCEPTIONS 子句：

 

SQL> declare
  2    type record_test_forall is record(
  3      id test_forall.id%type,
  4      name varchar2(100)
  5    );
  6    type taa_test_forall is table of record_test_forall index by pls_integer;
  7    aa_test_forall taa_test_forall;
  8  begin
  9    for n_pointer in 1..10 loop
 10      aa_test_forall(n_pointer).id := n_pointer;
 11      aa_test_forall(n_pointer).name := 'yuechaotian' || n_pointer;
 12    end loop;
 13
 14    -- change the 5th element, it'll be too long to be inserted.
 15    aa_test_forall(5).name := 'http://yuechaotian.cublog.cn';
 16    -- before execute forall, send a insert transaction
 17    insert into test_forall values(99, 'yuechaotian99');
 18
 19    forall n_pointer in 1..10 save exceptions
 20      insert into test_forall values aa_test_forall( n_pointer );
 21
 22    dbms_output.put_line( 'Insert rows: ' || sql%rowcount );
 23    for n_pointer in 1..10 loop
 24      dbms_output.put_line( 'Row: ' || n_pointer
 25        || ' | Result: ' ||sql%bulk_rowcount(n_pointer) );
 26    end loop;
 27
 28  exception
 29    when others then
 30      dbms_output.put_line( 'error: ' || sqlerrm );
 31  end;
 32  /
error: ORA-24381: error(s) in array DML

 

PL/SQL procedure successfully completed.

 

SQL> select * from test_forall;

 

        ID NAME
---------- --------------------
        99 yuechaotian99
         1 yuechaotian1
         2 yuechaotian2
         3 yuechaotian3
         4 yuechaotian4
         6 yuechaotian6
         7 yuechaotian7
         8 yuechaotian8
         9 yuechaotian9
        10 yuechaotian10

 

10 rows selected.

 

这和原来的执行结果又不同了。跳过了发生错误的第 5 个 DML，继续执行后面的 6-10 个 DML。但同样没有输出 SQL%ROWCOUNT 和 SQL%BULK_ROWCOUNT 的值，可见 SAVE EXCEPTIONS 可以跳过 FORALL 中的错误 DML，但不能再执行 FORALL 后面的语句了。而在异常捕获中，得到的异常是：“ORA-24381”。

 

5.3 %BULK_EXCEPTIONS

 

而如何捕获第 5 个 DML的错误呢？通过属性 %BULK_EXCEPTIONS。和 %BULK_ROWCOUNT 类似，%BULK_EXCEPTIONS 也是一个伪集合。我们再完善一下上面的 PL/SQL 块：

 

SQL> rollback;

 

Rollback complete.

 

SQL> declare
  2    type record_test_forall is record(
  3      id test_forall.id%type,
  4      name varchar2(100)
  5    );
  6    type taa_test_forall is table of record_test_forall index by pls_integer;
  7    aa_test_forall taa_test_forall;
  8
  9    bulk_errors exception;
 10    pragma exception_init(bulk_errors, -24381);
 11  begin
 12    for n_pointer in 1..10 loop
 13      aa_test_forall(n_pointer).id := n_pointer;
 14      aa_test_forall(n_pointer).name := 'yuechaotian' || n_pointer;
 15    end loop;
 16
 17    -- change the 5th element, it'll be too long to be inserted.
 18    aa_test_forall(5).name := 'http://yuechaotian.cublog.cn';
 19    -- before execute forall, send a insert transaction
 20    insert into test_forall values(99, 'yuechaotian99');
 21
 22    begin
 23       forall n_pointer in 1..10 save exceptions
 24         insert into test_forall values aa_test_forall( n_pointer );
 25    exception
 26      when bulk_errors then
 27        for n_error_pointer in 1..sql%bulk_exceptions.count loop
 28          dbms_output.put_line( 'error ' || n_error_pointer || ': The error DML number is '
 29            || sql%bulk_exceptions(n_error_pointer).error_index );
 30          dbms_output.put_line( 'The Oracle error is '
 31            || sqlerrm( -1 * sql%bulk_exceptions(n_error_pointer).error_code ) );
 32        end loop;
 33    end;
 34    dbms_output.put_line( 'Insert rows: ' || sql%rowcount );
 35    for n_pointer in 1..10 loop
 36      dbms_output.put_line( 'Row: ' || n_pointer
 37        || ' | Result: ' ||sql%bulk_rowcount(n_pointer) );
 38    end loop;
 39
 40  exception
 41    when others then
 42      dbms_output.put_line( 'error: ' || sqlerrm );
 43  end;
 44  /
error 1: The error DML number is 5
The Oracle error is ORA-01401: inserted value too large for column
Insert rows: 9
Row: 1 | Result: 1
Row: 2 | Result: 1
Row: 3 | Result: 1
Row: 4 | Result: 1
Row: 5 | Result: 0
Row: 6 | Result: 1
Row: 7 | Result: 1
Row: 8 | Result: 1
Row: 9 | Result: 1
Row: 10 | Result: 1

 

PL/SQL procedure successfully completed.

 

SQL> select * from test_forall;

 

        ID NAME
---------- --------------------
        99 yuechaotian99
         1 yuechaotian1
         2 yuechaotian2
         3 yuechaotian3
         4 yuechaotian4
         6 yuechaotian6
         7 yuechaotian7
         8 yuechaotian8
         9 yuechaotian9
        10 yuechaotian10

 

10 rows selected.

 

这个例子中，FORALL 在执行第 5 个 DML 时遇到错误，于是将错误信息保存到 %BULK_EXCEPTIONS 中，继续执行后面的 DML 语句。执行完这 10 个 DML 后，抛出 ORA-24381 异常，并在代码块中被捕获。

 

在 9-10 行中，我们自定义了一个异常，并在 26 行捕获，其实也可以直接使用错误代码来判断。比如 25-33 行可以修改为这样：

 

 23    exception
 24      when others then
 25         if sqlcode = 24381 then
 26           for n_error_pointer in 1..sql%bulk_exceptions.count loop
 27             dbms_output.put_line( 'error ' || n_error_pointer || ': The error DML number is '
 28               || sql%bulk_exceptions(n_error_pointer).error_index );
 29             dbms_output.put_line( 'The Oracle error is '
 30               || sqlerrm( -1 * sql%bulk_exceptions(n_error_pointer).error_code ) );
 31           end loop;
 32         end if;
 33    end;