oracle 复合数据类型（批量绑定）

兰蕙278

　　批量绑定是oracle9i新增加特性，是指执行单次SQL操作能传递所有集合元素的数据，通过批量绑定可以极大的加快数据处理速度，提高应用程序的性能，批量绑定是使用bulk collect子句和forall语法完成，其他bulk  collect子句用于取得批量数据，该子句只能用于select语句、fetch语句和DML返回子句，而forall语句只适用于批发批量的DML操作。
一、forall语句
当要在PL/SQL应用程序中执行批量insert、update、delete操作时，可以使用forall语句，在oracle9i之前，当使用forall语句时，必须具有连续的元素，而从oracle9i开始，通过使用新增的indices of子句和values of子句，可以使用不连续的集合元素，注意for语句是循环语句，而forall可不是循环语句，从oracle10g开始，forall语句有是三种执行方法，如下所示;
1、语法一：
   forall  index  in lower_bound..upper_bound
       sql_statement;
如上所示：index是隐含定义的整数变量(将作为集合元素下标被引用)；lower_bound和upper_bound分别是集合元素的上、下界。
2、语法二：
   forall index in indies of collection
     [between lower_bound and upper_bound]
如上所示：indices of子句用于指定只取得对应中的collection集合元素下标的index值。
3、语法三：
forall index in values of index_collection
    sql_statement;
如上所示：其中values of子句用于指定index值从集合变量index_collection中取得，注意Oracle9i只能使用第一种方法，以下通过示例说明：
(1)、在insert语句上使用批量绑定
   当使用批量绑定为数据库表插入数据时，首先需要给集合元素赋值，然后使用forall语句执行批量绑定插入操作，示例如下：
　　







declare
type id_table_type is  table of number(6)
index by binary_integer;
type name_table_type is  table of varchar2(10)
index by binary_integer;
id_table id_table_type;
name_table name_table_type;
begin
for i in 1..10 loop
id_table(i):=i;
name_table(i):='name'||to_char(i);
end loop;
forall i in 1..id_table.count
insert into demo values(id_table(i),name_table(i));
end;
　　
(2)、在update语句上使用批量绑定
   当使用批量绑定为更新数据时，首先需要给集合元素赋值，然后使用forall语句执行批量绑定修改操作，示例如下：







declare
type id_table_type is table of number(6) index by binary_integer;
type name_table_type is table of varchar2(10) index by binary_integer;
id_table id_table_type;
name_table name_table_type;
begin
for i in 1..10 loop
id_table(i):=i;
name_table(i):='yanglin'|| to_char(i);
end loop;
forall i in 1..id_table.count
update demo set name=name_table(i) where id=id_table(i);
end;
　　
(3)、在delete语句上使用批量绑定
   当使用批量绑定删除数据时，首先需要给集合元素赋值，然后使用forall语句执行批量绑定删除操作，示例如下：







declare
type id_table_type is table of number(6) index by binary_integer;
id_table id_table_type;
begin
for i in 1..10 loop
id_table(i):=i;
end loop;
forall i in 1..id_table.count
delete from  demo where id=id_table(i);
end;

　　
(4)、在forall语句上使用部分集合元素
   当使用forall语句进行批量操作时，即可以使用集合的所有元素，也可以使用集合的部分元素，示例如下：







declare
type id_table_type is table of number(6) index by binary_integer;
id_table id_table_type;
begin
for i in 11..20 loop
id_table(i):=i;
end loop;
forall i in 15..20
delete from  demo where id=id_table(i);
end;

　　
(4)、在forall语句上使用indices of子句
   indices of子句是oracle10g新增加的特征，该子句用于跳转null集合元素，示例如下：







declare
type id_table_type is table of number(6);
id_table id_table_type;
begin
id_table:=id_table_type(11,null,12,null,13,null);
forall i in indices of id_table
delete from  demo where id=id_table(i);
end;

　　
二、bulk collect子句
bulk collect用于取得批量数据，它只适用于select into语句、fetch into语句和DML返回子句，通过使用该子句可以将批量数据存放到PL/SQL集合变量中，其语法如下：
... bulk collect into collection_name[,collection_name].....
如上所示：collection_name用于指定集合变量名
1、在select into语句使用bulk collect语句，示例如下：







declare
type temp_table_type is table of cip_temps%rowtype index by binary_integer;
temp_table temp_table_type;
begin
select * bulk collect into temp_table from cip_temps where id=10;
for i in 1..temp_table.count loop
dbms_output.put_line(temp_table(i).name ||':'|| temp_table(i).address||':'||temp_table(i).age);
end loop;
end;

　　
2、在DML返回子句中使用bulk collect语句，示例如下：
   执行DML操作时会改变数据库数据，为了取得DML操作所改变的数据，可以使用returning子句，为了取得DML所作用的多行数据，需要使用bulk collect子句，示例如下：


declare   
type temp_table_type is table of cip_temps%rowtype index by binary_integer;   
temp_table temp_table_type;   
begin   
delete from cip_temps where id=1  
returning name,age,address,id bulk collect into temp_table;   
dbms_output.put_line('信息'|| temp_table.count);   
for i in 1..temp_table.count loop   
dbms_output.put_line(temp_table(i).name ||':'|| temp_table(i).age||':'||temp_table(i).address||':'||temp_table(i).id);   
end loop;   
end;