批量绑定（2.2）— FORALL 举例 - ORACLE PROGRAMING

%ROWCOUNT 与 %BULK_ROWCOUNT

%ROWCOUNT 记录了影响的总行数；而 %BULK_COLLECT 是一个伪集合，记录了每一行的变化：

SQL> declare
2    type taa_test_forall is table of test_forall%rowtype index by pls_integer;
3    aa_test_forall taa_test_forall;
4 begin
5    for n_pointer in 1..10 loop
6      aa_test_forall(n_pointer).id := n_pointer;
7      aa_test_forall(n_pointer).name := 'yuechaotian'||n_pointer;
8    end loop;
9    forall n_pointer in 1..10
10      insert into test_forall values aa_test_forall(n_pointer);
11    for n_pointer in 1..sql%rowcount loop
12      dbms_output.put_line( 'Row: ' || n_pointer
13        || ' | Result: ' ||sql%bulk_rowcount(n_pointer) );
14    end loop;
15 end;
16 /
Row: 1 | Result: 1
Row: 2 | Result: 1
Row: 3 | Result: 1
Row: 4 | Result: 1
Row: 5 | Result: 1
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.

伪集合 %BULK_COLLECT 的元素数量和下标与 FORALL 中的一致，而不一定从1开始：

SQL> rollback;

Rollback complete.

SQL> declare
2    type taa_test_forall is table of test_forall%rowtype index by pls_integer;
3    aa_test_forall taa_test_forall;
4 begin
5    for n_pointer in 3..7 loop
6      aa_test_forall(n_pointer).id := n_pointer;
7      aa_test_forall(n_pointer).name := 'yuechaotian'||n_pointer;
8    end loop;
9
10    forall n_pointer in 3..7
11      insert into test_forall values aa_test_forall(n_pointer);
12
13    dbms_output.put_line( 'Insert rows: ' || sql%rowcount );
14    for n_pointer in 3..7 loop
15      dbms_output.put_line( 'Row: ' || n_pointer
16        || ' | Result: ' ||sql%bulk_rowcount(n_pointer) );
17    end loop;
18 end;
19 /
Insert rows: 5
Row: 3 | Result: 1
Row: 4 | Result: 1
Row: 5 | Result: 1
Row: 6 | Result: 1
Row: 7 | Result: 1

PL/SQL procedure successfully completed.

SQL> select * from test_forall;

        ID NAME
---------- --------------------
         3 yuechaotian3
         4 yuechaotian4
         5 yuechaotian5
         6 yuechaotian6
         7 yuechaotian7

%ROWCOUNT 统计的是所影响的行的总数，而 %BULK_ROWCOUNT 记录了 FORALL 中每一个下标的执行结果。联合使用这两个属性，可以很清晰地看到 FORALL 的执行细节：

SQL> select * from test_forall;

        ID NAME
---------- --------------------
         3 yuechaotian3
         4 yuechaotian4
         5 yuechaotian5
         6 yuechaotian6
         7 yuechaotian7

SQL> declare
2    type taa_test_forall_id is table of test_forall.id%type index by pls_integer;
3    aa_test_forall_id taa_test_forall_id;
4 begin
5    for n_pointer in 1..10 loop
6      aa_test_forall_id(n_pointer) := n_pointer;
7    end loop;
8
9    forall n_pointer in 1..10
10      update test_forall
11         set name = 'x' || name
12       where id = aa_test_forall_id(n_pointer);
13
14    dbms_output.put_line( 'Update rows: ' || sql%rowcount );
15    for n_pointer in 1..10 loop
16      dbms_output.put_line( 'Row: ' || n_pointer
17        || ' | Result: ' ||sql%bulk_rowcount(n_pointer) );
18    end loop;
19 end;
20 /
Update rows: 5
Row: 1 | Result: 0
Row: 2 | Result: 0
Row: 3 | Result: 1
Row: 4 | Result: 1
Row: 5 | Result: 1
Row: 6 | Result: 1
Row: 7 | Result: 1
Row: 8 | Result: 0
Row: 9 | Result: 0
Row: 10 | Result: 0

PL/SQL procedure successfully completed.

也就是说，FORALL 批量产生了 n 个 DML 语句，那么伪集合 %BULK_ROWCOUNT 就会有 n 行，每行中的数值代码所对应的 DML 语句的执行结果；而 %ROWCOUNT记录了这 n 个 DML 语句所影响的行数。