用FORALL来增强DML的处理能力 Oracle8i中语句:BULK COLLECT和FORALL。这两个语句在PL/SQL内部进行一种数组处理
(1)BULK COLLECT提供对数据的高速检索
(2)FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。
Oracle数据库使用这些语句大大减少了PL/SQL与SQL语句执行引擎的环境切换次数,从而使其性能有了显著提高。
使用BULK COLLECT,你可以将多个行引入一个或多个集合中,而不是单独变量或记录中(一次循环获取多行)而传统的 open 打开游标 fetch 游标只得到(一次循环获取一行)
这里只讲 FORALL 下次再讲 BULK COLLECT
当要在 Oracle 中之心批量 INSERT、UPDATE 和 DELETE 操作时,可以使用 FORALL 语句。
- 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
当要在 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
- 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