使用forall语句的bulk dml操作

oracle 8i或更高版本的forall语句中,oracle提供了非常有意义的dml增强特性。Forall告诉PL/SQL运行期引擎批量绑定一个或多个集合的所有元素到sql语句中,这个动作是在发送信息到sql引擎之前发生。

 

为什么批量dml 是非常有用的呢?因为在oracle数据库中,plsqlsql引擎是紧密联系的,尽管我们可以在oracle数据库中使用java 语言,但是在oracle中,使用plsql语言还是最佳选择。虽然plsqlsql引擎紧密联系,但是,这种联系是非必要的联系,当plsql运行期引擎发送一段过程化代码,它执行这段代码是在plsql引擎中,而非将代码传送到sql引擎中去执行。在有需要的情况下,比如代码中有sql语句,在sql引擎中会执行sql代码,然后将信息返回给plsql引擎。

 

plsqlsql引擎之间的控制传输叫context switch(上下文转发)。在oracle 8i或更高版本,允许批量绑定多个context switch到一个context switch,这样可以提高性能。这个增强的功能是forall以及bulk collect的使用,也就是在同一时间只经过一个应用转发,批量将相同结构的sql 传输给sql引擎执行,减少了应用的转发,从而提高效率。

 

 

 

使用forall应该遵循的规则

1. forall体部必须是单个的dml语句(insert,update,delete),不能有多条dml语句或其他语句。

 

2. dml 语句必须引用集合元素(直接写个简单的没有引用集合元素的语句是错误的),按照在foall中的index_row变量划分索引。Index_rowscope仅在forall中有效,你不能在scope之外引用它,并且集合的内容范围必须在low_bound----àupper_bound范围之内。

 

3. 不可以申明一个变量来充当index_row的角色。它是plsql engine隐性申明的,类型是PLS_INTEGER类型。

 

4. lower and upper bounds针对被forall中的sql语句使用,必须是一个有效的连续的索引值。如果是稀疏性的,可能会导致下列错误:ORA-22160: element at index [3] does not exist

注意:oracle10g 之后提供了indices of 和values of来支持非连续性的集合。

 

5. DML 语句中,你不能引用集合的单个field,相反地,你仅能整体引用集合的行,不管集合的field 是标量还是复杂的对象类型,都是如此,否则报错. 

 

6. 集合的索引变量,在DML 语句中不能是表达式

 

下面是一些使用的例子和常见的错误:

--测试表1
drop table test1;
create table test1(id number(10),name varchar2(10));
insert into test1 values(1,'aa');
insert into test1 values(2,'bb');
commit;

--测试表2
drop table test2;
create table test2(id number(10),name varchar2(10));

--test1
declare
  type dr_type is table of test1%ROWTYPE index by binary_integer;
  dr_table dr_type;
begin
  select id, name BULK COLLECT into dr_table from test1;
  FORALL i IN dr_table.first .. dr_table.last
    insert into test2 values dr_table (i);
  --error statement
  --1.insert into test2 values(dr_table(i));报没有足够的值错误,此处外面不可以加括号,当有多个字段的时候,单个字段可以加括号
  --2.insert into test2 values(dr_table(i).id,dr_table(i).name);集合的field不可以在forall中使用,必须是整体使用
  --3.insert into test2 values dr_table(i+1);错误,不可以对索引变量进行运算
  --4.insert into test2 values dr_table(i);dbms_output.put_line(i);不正确,找不到i,因为forall中只能使用单条语句可以引用索引变量
end;
--非连续集合使用,采用indices of collection

drop table mynumtable;
create table mynumtable(id number(10));

--测试非连续的集合中使用forall
Declare
  type mylist_type is table of number index by binary_integer;
  mylist mylist_type;
begin
  mylist(1) := 2;
  mylist(3) := 3;
  mylist(4) := 6;
  --forall i in mylist.first .. mylist.last 错误:下标为[2]的元素不存在
  forall i in indices of mylist --i值集合的索引,和连续的区别只不过这里使用indices of
              collection,可以是非连续的集合, 10g以上
    insert into mynumtable (id) values (mylist(i));
end;
/

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值