forall与批量绑定

  背景：要往一个大表里面插入或者修改数据，这个表已经有几百万条数据了，想到了批量绑定，想到了forall。

 

在做此之前先写个测试的例子：

 

---这个例子的目的就是将e2表中的数据插入追加到e1里面去，
---如果e1表里面存在，就修改之。不存在，就添加。
--我还是用id来判断是否存在
create or replace procedure 
e_insert
as
----定义三个集合变量类型，oracle官方说法是nested table。

 TYPE type_no IS TABLE OF e2.e1no%type
         INDEX BY BINARY_INTEGER;        
  TYPE type_name IS TABLE OF e2.enname%type
         INDEX BY BINARY_INTEGER;
     TYPE type_e2 IS TABLE OF e2%rowtype
         INDEX BY BINARY_INTEGER;               
         insert_e2 type_e2;
update_name  type_name;
update_no  type_no;
begin
---查找e1表中不存在而e2表中存在的数据，这部分数据是要
---添加到e1表里面去的。这里用批量绑定，将查询到的数据
--赋给相应集合变量，注意变量类型一定要匹配。

  select e2.enname,e2.e1no bulk collect  into 
  update_name,update_no from e2 left join
e1 on e1.e1no=e2.e1no where e1.e1no is not null;

---查找e1表中不存在而e2表中存在的数据，这部分数据是要
---添加到e1表里面去的
select e2.* bulk collect  into 
insert_e2 from e2 left join
e1 on e1.e1no=e2.e1no where e1.e1no is null;


dbms_output.put_line('----'||insert_e2.count);---查询集合里面的个数

dbms_output.put_line('-------'||update_name.count||'  '||update_no.count);


if insert_e2.count>0 then 
forall j in insert_e2.FIRST..insert_e2.LAST  
insert into e1
values insert_e2(j);---注意这里values后面没有括号

elsif update_no.count>0  then---注意这里的elsif 
forall i in update_no.FIRST..update_no.LAST  
update e1 set e1.enname=update_name(i) where e1.e1no=update_no(i);   

end if;



commit;
end e_insert;

 

 

e1表的字段很随便，e2是e1的副本，用create table e2 as select * from e1 where rownum<500

创建的。

 

这里很多要值得注意的地方：

1.存储过程要是没有参数，过程名称后面是没有括号的。

2.forall 和for语法基本相同，但是没有loop   end loop.

  forall后面只能跟DML语句，动态sql也可以。

3.把批量绑定想象成sql 引擎到plsql 引擎的批处理。

  把forall 想象成plsql到sql引擎的批处理。

sql引擎专门处理sql语句呀，plsql引擎处理plsql程序块。

这样减少了上下文切换的时间，加快了sql的运行。

其实这很像java里面的executeBatch。

 

4.还有一点，也是非常重要的一点，forall 后面的DML语句里面不能有集合变量的元素的字段。

insert into e1
values insert_e2(j);
这里的insert_e2(j)就是集合元素，e1no是它的字段。

出现insert_e2(j).e1no是不行滴，是要报错误滴。

尽管它是行集变量类型。

这里我用列类型解决了这个问题。

 

ok。完了。