当要在 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
备注:本文转自网络,非原创
转载于:https://blog.51cto.com/shiyg/1631844