博客首页 注册 建议与交流 排行榜 加入友情链接
推荐 投诉 搜索： 帮助

CLEANER

如果你的工作，机器也可以做。那总有一天，你也要变成机器。
   yuechaotian.cublog.cn
关于作者   
name:yuechaotian
employment:coder
age:25
from:NEU
about me:a retardate
email:yuechaotian(at)gmail.com
msn:yuechaotian(at)hotmail.com

我的分类  




批量绑定（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

 发表于： 2008-06-02，修改于： 2008-06-02 14:17 已浏览406次，有评论0条 推荐 投诉

  网友评论

  发表评论



Copyright © 2001-2006 ChinaUnix.net All Rights Reserved

感谢所有关心和支持过ChinaUnix的朋友们
页面生成时间：0.03855

京ICP证041476号