Oracle 中的 FORALL 语句

当要在 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