批量绑定（2.3）— FORALL - ORACLE PROGRAMING

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;

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;