Oracle中的FORALL语句

转自：http://ears.iteye.com/blog/1485116

当要在 Oracle 中之心批量 INSERT、UPDATE 和 DELETE 操作时，可以使用 FORALL 语句。

 

--语法
--语法1：  
FORALL 下标变量(只能当作下标被引用) IN 下限..上限  
  sql 语句;    --只允许一条 sql 语句  
  
  
--语法2：  
FORALL 下标变量 IN INDICES OF(跳过没有赋值的元素，例如被 DELETE 的元素，NULL 也算值) 集合  
  [BETWEEN 下限 AND 上限]  
  sql 语句;  
  
  
--语法3：  
FORALL 下标变量 IN VALUES OF 集合(把该集合中的值当作下标，且该集合值的类型只能是 PLS_INTEGER BINARY_INTEGER)  
  sql 语句;

create table tb1(  
  id number(5),  
  name varchar2(50)  
); 

--语法1演示：
--批量插入演示  
declare  
  type tb_table_type is table of tb1%rowtype  
    index by binary_integer;  
  tb_table tb_table_type;  
begin  
  for i in 1..10 loop  
    tb_table(i).id:=i;  
    tb_table(i).name:='NAME'||i;  
  end loop;  
  forall i in 1..tb_table.count  
    insert into tb1 values tb_table(i);  
end;  
  
  
--批量修改演示  
declare  
  type tb_table_type is table of tb1%rowtype  
  index by binary_integer;  
  tb_table tb_table_type;  
begin  
  for i in 1..10 loop  
    tb_table(i).id:=i;  
    tb_table(i).name:='NAMES'||i;  
  end loop;  
  forall i in 1..tb_table.count  
    update tb1 t set row = tb_table(i) where t.id = tb_table(i).id;  
end;  
  
  
--批量删除演示  
declare  
  type tb_table_type is table of tb1%rowtype  
  index by binary_integer;  
  tb_table tb_table_type;  
begin  
  for i in 1..10 loop  
    tb_table(i).id:=i;  
    tb_table(i).name:='NAMES'||i;  
  end loop;  
  forall i in 1..tb_table.count  
    delete tb1 where id = tb_table(i).id;  
end;

--语法2演示：
select * from tb1;  
declare  
  type demo_table_type is table of demo%rowtype  
    index by binary_integer;  
  demo_table demo_table_type;  
begin  
  for i in 1..10 loop  
    demo_table(i).id:=i;  
    demo_table(i).name:='NAME'||i;  
  end loop;  
  demo_table.delete(3);  
  demo_table.delete(6);  
  demo_table.delete(9);  
  forall i in indices of demo_table  
    insert into demo values demo_table(i);  
end;  
  
select * from demo;  
1   1   NAME1  
2   2   NAME2  
3   4   NAME4  
4   5   NAME5  
5   7   NAME7  
6   8   NAME8  
7   10  NAME10 

--语法3演示：
declare  
  type index_poniter_type is table of pls_integer;  
  index_poniter index_poniter_type;  
  type demo_table_type is table of demo%rowtype  
    index by binary_integer;  
  demo_table demo_table_type;  
begin  
  index_poniter:=index_poniter_type(1,3,5,7);  
  for i in 1..10 loop  
    demo_table(i).id:=i;  
    demo_table(i).name:='NAME'||i;  
  end loop;  
  forall i in values of index_poniter  
    insert into demo values demo_table(i);  
end;  
  
  
select * from demo;  
1   1   NAME1  
2   3   NAME3  
3   5   NAME5  
4   7   NAME7