批量绑定（2.2）— FORALL 举例

%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 语句所影响的行数。