批量绑定（2.1）— FORALL 举例

前面我们粗略地介绍了一下 FORALL，这里我们会举例来说明一下它的用法：

 

1. INSERT

 

我们编写一个过程，使用 FORALL 将集合中的 ID 和 NAME 批量保存到表 TEST_FORALL 中：

 

SQL> select * from v$version;

 

BANNER
----------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

 

SQL> create table test_forall ( id number(10), name varchar2(20));

 

表已创建。

 

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
 10    forall n_pointer in 1..10
 11      insert into test_forall values(
 12        aa_test_forall(n_pointer).id, aa_test_forall(n_pointer).name );
 13  end;
 14  /
      aa_test_forall(n_pointer).id, aa_test_forall(n_pointer).name );
      *
ERROR 位于第 12 行:
ORA-06550: 第 12 行, 第 7 列:
PLS-00436: 实施限制: 不能引用记录的 BULK In-BIND 表的字段
ORA-06550: 第 12 行, 第 32 列:
PL/SQL: ORA-00904: : 无效的标识符
ORA-06550: 第 11 行, 第 5 列:
PL/SQL: SQL Statement ignored

 

看来不能使用记录类型作为批量绑定的集合参数，修改为两个类型：

 

SQL> declare
  2    type taa_test_forall_id is table of test_forall.id%type index by pls_integer;
  3    type taa_test_forall_name is table of test_forall.name%type index by pls_integer;
  4    aa_test_forall_id taa_test_forall_id;
  5    aa_test_forall_name taa_test_forall_name;
  6  begin
  7    for n_pointer in 1..10 loop
  8      aa_test_forall_id(n_pointer) := n_pointer;
  9      aa_test_forall_name(n_pointer) := 'yuechaotian'||n_pointer;
 10    end loop;
 11
 12    forall n_pointer in 1..10
 13      insert into test_forall values(
 14        aa_test_forall_id(n_pointer), aa_test_forall_name(n_pointer) );
 15  end;
 16  /

 

PL/SQL 过程已成功完成。

 

SQL> select * from test_forall;

 

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

 

已选择10行。

 

FORALL 中也支持动态SQL：

 

SQL> truncate table test_forall;

 

表已截掉。

 

SQL> declare
  2    type taa_test_forall_id is table of test_forall.id%type index by pls_integer;
  3    type taa_test_forall_name is table of test_forall.name%type index by pls_integer;
  4    aa_test_forall_id taa_test_forall_id;
  5    aa_test_forall_name taa_test_forall_name;
  6  begin
  7    for n_pointer in 1..10 loop
  8      aa_test_forall_id(n_pointer) := n_pointer;
  9      aa_test_forall_name(n_pointer) := 'yuechaotian'||n_pointer;
 10    end loop;
 11
 12    forall n_pointer in 1..10
 13      execute immediate 'insert into test_forall values( :1, :2 )'
 14        using aa_test_forall_id(n_pointer), aa_test_forall_name(n_pointer);
 15  end;
 16  /

 

PL/SQL 过程已成功完成。

 

SQL> select * from test_forall;

 

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

 

已选择10行。

 

或者你可以这样更简单地使用它：

 

SQL> rollback;

 

回退已完成。

 

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
 10    forall n_pointer in 1..10
 11      insert into test_forall values aa_test_forall(n_pointer);
 12  end;
 13  /

 

PL/SQL 过程已成功完成。

 

SQL> select * from test_forall;

 

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

 

已选择10行。

 

记得我们说过，FORALL 的集合下标是不能使用表达式的：

 

SQL> rollback;

 

回退已完成。

 

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
 10    forall n_pointer in 1..10
 11      insert into test_forall values aa_test_forall(n_pointer+1-1);
 12  end;
 13  /
    insert into test_forall values aa_test_forall(n_pointer+1-1);
                                                  *
ERROR 位于第 11 行:
ORA-06550: 第 11 行, 第 51 列:
PLS-00430: 该上下文中不允许使用 FORALL 循环变量 N_POINTER
ORA-06550: 第 11 行, 第 36 列:
PL/SQL: ORA-00904: : 标识符无效
ORA-06550: 第 11 行, 第 5 列:
PL/SQL: SQL Statement ignored

 

那么，如果是稀疏的数组呢？在 Oracle10g 之前，你可以调整你的程序：或者变为紧密的数组，或者分成两个 FORALL。

 

SQL> rollback;

 

回退已完成。

 

SQL> declare
  2    type taa_test_forall_id is table of test_forall.id%type index by pls_integer;
  3    type taa_test_forall_name is table of test_forall.name%type index by pls_integer;
  4    aa_test_forall_id taa_test_forall_id;
  5    aa_test_forall_name taa_test_forall_name;
  6  begin
  7    for n_pointer in 1..10 loop
  8      aa_test_forall_id(n_pointer) := n_pointer;
  9      aa_test_forall_name(n_pointer) := 'yuechaotian'||n_pointer;
 10    end loop;
 11
 12    aa_test_forall_id.delete(5);
 13    aa_test_forall_name.delete(5);
 14
 15    forall n_pointer in 1..10
 16      insert into test_forall values(
 17        aa_test_forall_id(n_pointer), aa_test_forall_name(n_pointer) );
 18  end;
 19  /
declare
*
ERROR 位于第 1 行:
ORA-22160: 索引 [5] 中的元素不存在
ORA-06512: 在line 15

 

15-17行可以拆分成两个 FORALL：

 15    forall n_pointer in 1..4
 16      insert into test_forall values(
 17        aa_test_forall_id(n_pointer), aa_test_forall_name(n_pointer) );
 18    forall n_pointer in 6..10
 19      insert into test_forall values(
 20        aa_test_forall_id(n_pointer), aa_test_forall_name(n_pointer) );

 

而在 Oracle10g 中，你可以使用 indices of 来实现稀疏数组的 FORALL 处理：

 

SQL> select * from v$version;

 

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table test_forall ( id number(10), name varchar2(20));

 

表已创建。

 

SQL> declare
  2    type taa_test_forall_id is table of test_forall.id%type index by pls_integer;
  3    type taa_test_forall_name is table of test_forall.name%type index by pls_integer;
  4    aa_test_forall_id taa_test_forall_id;
  5    aa_test_forall_name taa_test_forall_name;
  6  begin
  7    for n_pointer in 1..10 loop
  8      aa_test_forall_id(n_pointer) := n_pointer;
  9      aa_test_forall_name(n_pointer) := 'yuechaotian'||n_pointer;
 10    end loop;
 11
 12    aa_test_forall_id.delete(5);
 13    aa_test_forall_name.delete(5);
 14
 15    forall n_pointer in indices of aa_test_forall_id
 16      insert into test_forall values(
 17        aa_test_forall_id(n_pointer), aa_test_forall_name(n_pointer) );
 18  end;
 19  /

 

PL/SQL 过程已成功完成。

 

SQL> select * from test_forall;

 

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

 

已选择9行。

 

当然，indices of collection 中的这个集合，可以是其他集合，只要索引下标合适即可：

 

SQL> rollback;

 

回退已完成。

 

SQL> declare
  2    type taa_test_forall_id is table of test_forall.id%type index by pls_integer;
  3    type taa_test_forall_name is table of test_forall.name%type index by pls_integer;
  4    aa_test_forall_id taa_test_forall_id;
  5    aa_test_forall_name taa_test_forall_name;
  6
  7    type taa_index is table of number(2) index by pls_integer;
  8    aa_index taa_index;
  9  begin
 10    for n_pointer in 1..10 loop
 11      aa_test_forall_id(n_pointer) := n_pointer;
 12      aa_test_forall_name(n_pointer) := 'yuechaotian'||n_pointer;
 13    end loop;
 14
 15    aa_test_forall_id.delete(5);
 16    aa_test_forall_name.delete(5);
 17
 18    select rownum
 19      bulk collect into aa_index
 20      from dual
 21      connect by rownum < 11;
 22    aa_index.delete(5);
 23
 24    forall n_pointer in indices of aa_index
 25      insert into test_forall values(
 26        aa_test_forall_id(n_pointer), aa_test_forall_name(n_pointer) );
 27  end;
 28  /

 

PL/SQL 过程已成功完成。

 

SQL> select * from test_forall;

 

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

 

已选择9行。

 

在 Oracle10g 中，除了可以使用 indices of，还可以使用 values of，但这个索引所保存的数据类型只能是PLS_INTEGER 或者 BINARY_INTERGER：

 

SQL> rollback;

 

回退已完成。

 

SQL> declare
  2    type taa_test_forall_id is table of test_forall.id%type index by pls_integer;
  3    type taa_test_forall_name is table of test_forall.name%type index by pls_integer;
  4    aa_test_forall_id taa_test_forall_id;
  5    aa_test_forall_name taa_test_forall_name;
  6
  7    type taa_index is table of number(2) index by pls_integer;
  8    aa_index taa_index;
  9  begin
 10    for n_pointer in 1..10 loop
 11      aa_test_forall_id(n_pointer) := n_pointer;
 12      aa_test_forall_name(n_pointer) := 'yuechaotian'||n_pointer;
 13    end loop;
 14
 15    aa_test_forall_id.delete(5);
 16    aa_test_forall_name.delete(5);
 17
 18    select rownum
 19      bulk collect into aa_index
 20      from dual
 21      connect by rownum < 11;
 22    aa_index.delete(5);
 23
 24    forall n_pointer in values of aa_index
 25      insert into test_forall values(
 26        aa_test_forall_id(n_pointer), aa_test_forall_name(n_pointer) );
 27  end;
 28  /
  type taa_index is table of number(2) index by pls_integer;
                    *
ERROR 位于第 7 行:
ORA-06550: 第 7 行, 第 21 列:
PLS-00667: 关联性数组的元素类型必须为 pls_integer 或 binary_integer
ORA-06550: 第 24 行, 第 23 列:
PL/SQL: Statement ignored

 

SQL> declare
  2    type taa_test_forall_id is table of test_forall.id%type index by pls_integer;
  3    type taa_test_forall_name is table of test_forall.name%type index by pls_integer;
  4    aa_test_forall_id taa_test_forall_id;
  5    aa_test_forall_name taa_test_forall_name;
  6
  7    type taa_index is table of pls_integer index by pls_integer;
  8    aa_index taa_index;
  9  begin
 10    for n_pointer in 1..10 loop
 11      aa_test_forall_id(n_pointer) := n_pointer;
 12      aa_test_forall_name(n_pointer) := 'yuechaotian'||n_pointer;
 13    end loop;
 14
 15    aa_test_forall_id.delete(5);
 16    aa_test_forall_name.delete(5);
 17
 18    select rownum
 19      bulk collect into aa_index
 20      from dual
 21      connect by rownum < 11;
 22    aa_index.delete(5);
 23
 24    forall n_pointer in values of aa_index
 25      insert into test_forall values(
 26        aa_test_forall_id(n_pointer), aa_test_forall_name(n_pointer) );
 27  end;
 28  /

 

PL/SQL 过程已成功完成。

 

SQL> select * from test_forall;

 

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

 

已选择9行。

 

 

2. DELETE

 

FORALL 中使用 DELETE 时，可以带有返回值：

 

SQL> select * from test_forall order by id;

 

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

 

已选择10行。

 

SQL> set serveroutput on;
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    aa_test_forall_id_delete taa_test_forall_id;
  5
  6    type taa_test_forall_name is table of test_forall.name%type index by pls_integer;
  7    aa_test_forall_name_delete taa_test_forall_name;
  8  begin
  9    select rownum
 10      bulk collect into aa_test_forall_id
 11      from dual
 12      connect by rownum < 6;
 13
 14    forall n_pointer in 1..aa_test_forall_id.count
 15      delete test_forall where id = aa_test_forall_id(n_pointer)
 16        returning id, name bulk collect into aa_test_forall_id_delete, aa_test_forall_name_delete;
 17
 18    for n_pointer in aa_test_forall_id_delete.first..aa_test_forall_id_delete.last loop
 19      dbms_output.put_line( 'The deleted ID: ' || aa_test_forall_id_delete(n_pointer) );
 20      dbms_output.put_line( 'The deleted NAME: ' || aa_test_forall_name_delete(n_pointer) );
 21    end loop;
 22  end;
 23  /
The deleted ID: 1
The deleted NAME: yuechaotian1
The deleted ID: 2
The deleted NAME: yuechaotian2
The deleted ID: 3
The deleted NAME: yuechaotian3
The deleted ID: 4
The deleted NAME: yuechaotian4
The deleted ID: 5
The deleted NAME: yuechaotian5

 

PL/SQL 过程已成功完成。

SQL> select * from test_forall order by id;

 

        ID NAME
---------- --------------------
         6 yuechaotian6
         7 yuechaotian7
         8 yuechaotian8
         9 yuechaotian9
        10 yuechaotian10

 

FORALL 的 DELETE 语句中使用 RETURNING，必须将返回结果 BULK COLLECT 到一个或多个集合中。上面我们用两个集合保存了返回结果，其实也可以使用一个集合：

 

SQL> rollback;

 

回退已完成。

 

SQL> select * from test_forall;

 

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

 

已选择10行。

SQL> set serveroutput on
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
  5    type taa_test_forall is table of test_forall%rowtype index by pls_integer;
  6    aa_test_forall_delete taa_test_forall;
  7  begin
  8    select rownum
  9      bulk collect into aa_test_forall_id
 10      from dual
 11      connect by rownum < 6;
 12
 13    forall n_pointer in 1..aa_test_forall_id.count
 14      delete test_forall where id = aa_test_forall_id(n_pointer)
 15        returning id, name bulk collect into aa_test_forall_delete;
 16
 17    for n_pointer in aa_test_forall_delete.first..aa_test_forall_delete.last loop
 18      dbms_output.put_line( 'The deleted ID: ' || aa_test_forall_delete(n_pointer).ID );
 19      dbms_output.put_line( 'The deleted NAME: ' || aa_test_forall_delete(n_pointer).NAME );
 20    end loop;
 21  end;
 22  /
The deleted ID: 1
The deleted NAME: yuechaotian1
The deleted ID: 2
The deleted NAME: yuechaotian2
The deleted ID: 3
The deleted NAME: yuechaotian3
The deleted ID: 4
The deleted NAME: yuechaotian4
The deleted ID: 5
The deleted NAME: yuechaotian5

 

PL/SQL 过程已成功完成。

 

SQL> select * from test_forall;

 

        ID NAME
---------- --------------------
         6 yuechaotian6
         7 yuechaotian7
         8 yuechaotian8
         9 yuechaotian9
        10 yuechaotian10