PL/SQL入门--复合类型及集合(3)

 8.4比较集合

  函数cardinality用于返回嵌套表变量的元素个数   

  操作符SUBMULTISET OF用于确定一个嵌套表是否为另一个嵌套表的子集

  操作符MEMBER OF用于检测特定数据是否为嵌套表元素

  操作符IS A SET用于检测嵌套表是否包含重复的元素值

  操作符IS EMPTY用于检测嵌套表是否为NULL.

  

  1.检测集合是否为NULL

1. declare 
2.     type name_table_type is table of varchar2(10); 
3.     name_table name_table_type; 
4.   begin 
5.     if name_table is empty then 
6.        dbms_output.put_line('name_table未初始化'); 
7.     end if; 
8.   end; 

  2.比较嵌套表是否相同

  使用比较符=和!=检测两个嵌套表是否相同.不能比较VARRAY和索引表

 

1. declare 
2.    type name_table_type is table of varchar2(10); 
3.    name_table1 name_table_type; 
4.    name_table2 name_table_type; 
5.  begin 
6.    name_table1:=name_table_type('scott'); 
7.    name_table2:=name_table_type('smith'); 
8.    if name_table1=name_table2 then 
9.      dbms_output.put_line('两个嵌套表完全相同'); 
10.    else 
11.      dbms_output.put_line('两个嵌套表数值不同'); 
12.    end if; 
13.  end; 

  3.在嵌套表上使用集合操作符

    在嵌套表上使用ANSI集合操作符CARDINALITY,MEMBER OF, IS A SET.不适用于VARRAY和索引表

    使用函数CARDINALITY

1. declare 
2.     type nt_table_type is table of number; 
3.     nt1 nt_table_typent_table_type:=nt_table_type(1,2,3,1); 
4.   begin 
5.     dbms_output.put_line('元素个数:'||cardinality(nt1)); 
6.   end; 

    使用操作符SUBMULTISET OF:用于确定一个嵌套表是否为另一个嵌套表的子集.

1. declare 
2.    type nt_table_type is table of number; 
3.    nt1 nt_table_typent_table_type:=nt_table_type(1,2,3); 
4.    nt2 nt_table_typent_table_type:=nt_table_type(1,2,3,4); 
5.  begin 
6.    if nt1 submultiset of nt2 then 
7.       dbms_output.put_line('nt1是nt2的子集); 
8.    end if; 
9.  end; 

  使用操作符MEMBER OF :用于检测特定数据是否为嵌套表的元素.

1. declare 
2.     type nt_table_type is table of number; 
3.     nt1 nt_table_typent_table_type:=nt_table_type(1,2,3,5); 
4.     v1 number:=&v1; 
5.   begin 
6.     if v1 member of nt1 then 
7.       dbms_output.put_line('v1是nt1的元素'); 
8.     end if; 
9.   end; 

  使用操作符IS A SET:用于检测嵌套表是否包含重复的元素值  

1. declare 
2.    type nt_table_type is table of number; 
3.    nt1 nt_table_typent_table_type:=nt_table_type(1,2,3,5); 
4.  begin 
5.    if nt1 is a set then 
6.      dbms_output.put_line('嵌套表NT1无重复值'); 
7.    end if; 
8.  end; 

  

8.5批量绑定

  当在select,insert,update,delete语句上处理批量数据时,通过批量绑定,可以极大的加快数据处理速度,提高应用程序性能

  批量绑定是使用BULK COLLECT子句和FORALL语句来完成的

  BULK COLLECT 子句用于取得批量数据,该子句只能用于SELECT语句,FETCH语句和DML返回子句

  FORALL只适用于执行批量的DML操作

  1.不使用批量绑定

1. declare 
2.    type id_table_type is table of number(6) index by binary_integer; 
3.    type name_table_type is table of varchar2(10) index by binary_integer; 
4.    id_table id_table_type; 
5.    name_table name_table_type; 
6.    start_time number(10); 
7.    end_time number(10); 
8.  begin 
9.    for i in 1..5000 loop 
10.       id_table(i):=i; 
11.       name_table(i):='name'||to_char(i); 
12.    end loop; 
13.    start_time:=dbms_utility.get_time; 
14.    for i in 1..id_table.count loop 
15.        insert into demo values(id_table(i),name_table(i)); 
16.    end loop; 
17.    end_time:=dbms_utility.get_time; 
18.    dbms_output.put_line('总计时间(秒):'||to_char((end_time-start_time)/100)); 
19.  end; 

  2.使用批量绑定

1. declare 
2.    type id_table_type is table of number(6) index by binary_integer; 
3.    type name_table_type is table of varchar2(10) index by binary_integer; 
4.    id_table id_table_type; 
5.    name_table name_table_type; 
6.    start_time number(10); 
7.    end_time number(10); 
8.  begin 
9.    for i in 1..5000 loop 
10.       id_table(i):=i; 
11.       name_table(i);='name'||to_char(i); 
12.    end loop; 
13.    start_time:=dbms_utility.get_time; 
14.    forall i in 1..id_table.count 
15.       insert into demo values(id_table(i),name_table(i)); 
16.    end_time:=dbms_utility.get_time; 
17.    dbms_output.put_line('总计时间(秒):'||to_char((end_time-start_time)/100)); 
18.  end; 

  8.6 FORALL语句

  执行批量insert,update,delete操作时,使用forall语句,FORALL不是循环语句

  oracle9i当使用FORALL语句时,必须具有连续的元素

  oracle10g通过使用indices of和values of子句,可以使用不连续的集合元素.

  forall三种执行语法:

1. forall index in lower_bound..upper_bound sql_statement; 
2.  forall index in indices of collection [between lower_bond.and.upper_bound] sql_statement; 
3.  forall index in values of index_collection sql_statement; 

  1.在insert语句上使用批量绑定

1. declare 
2.     type id_table_type is table of number(6) index by binary_integer; 
3.     type name_table_type is table of varchar2(10) index by binary_integer; 
4.     id_table id_table_type; 
5.     name_table name_table_type; 
6.   begin 
7.     for i in 1..10 loop 
8.       id_table(i):=i; 
9.       name_table(i):='name'||to_char(i); 
10.     end loop; 
11.     forall i in 1..id_table.count 
12.         insert into demo values(id_table(i),name_table(i)); 
13.   end; 

  2.在update语句上使用批量绑定   

1. declare 
2.     type id_table_type is table of number(6) index by binary_integer; 
3.     type name_table_type is table of varchar2(10) index by binary_integer; 
4.     id_table id_table_type; 
5.     name_table name_table_type; 
6.   begin 
7.     for i in 1..5 loop 
8.       id_table(i):=i; 
9.       name_table(i):='n'||to_char(i); 
10.     end loop; 
11.     forall i in 1..id_table.count  
12.        update demo set name=name_table(i) where id=id_table(i); 
13.   end; 

  3.在DELETE语句上使用批量绑定

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

  4.在FORALL语句中使用部分集合元素

1. declare 
2.    type id_table_type is table of number(6) index by binary_integer; 
3.    id_table id_table_type; 
4.  begin 
5.    for i in 1..10 loop 
6.      id_table(i):=11-i; 
7.    end loop; 
8.    forall i in 8..10 
9.       insert into demo (id) values (id_table(i)); 
10.  end; 

  5.在FORALL语句上使用INDECES OF子句:用于跳过NULL集合元素

1. declare 
2.     type id_table_type is table of number(6); 
3.     id_table id_table_type; 
4.   begin 
5.     id_table:=id_table_type(1,null,3,null,5); 
6.     forall i in indices of id_table 
7.        delete from demo where id=id_table(i); 
8.   end; 

  6.在FORALL语句上使用VALUES OF子句

 

1. create table new_demo as select * from demo where 1=0 

1. declare 
2.     type id_table_type is table of demp.id%type; 
3.     type name_table_type is table of demo.name%type; 
4.     id_table id_table_type; 
5.     name_table name_table_type; 
6.     type index_pointer_type is table of pls_integer; 
7.     index_pointer index_pointer_type; 
8.   begin 
9.     select * bulk collect into id_table,name_table from demo; 
10.     index_pointer:=index_pointer_type(6,8,10); 
11.     forall i in values of index_pointer 
12.         insert into new_demo values(id_table(i),name_table(i)); 
13.   end; 

  7.使用SQL%BULK_ROWCOUNT属性:专门为FORALL语句提供,用于取得在执行批量绑定操作时第i个元素所作用的行数

1. declare 
2.     type dno_table_type is table of number(3); 
3.     dno_table dno_table_typedno_table_type:=dno_table_type(10,20); 
4.   begin 
5.     forall i in 1..dno_table.count 
6.        update emp set salsal=sal*1.1 where deptno=dno_table(i); 
7.        dbms_output.put_line('第2个元素更新的行数:'||sql%bulk_rowcount(2)); 
8.   end; 

  8.7 BULK COLLECT子句

  用于取得批量数据，只适用于select into 语句,fetch into 语句和DML返回子句

  可将批量数据存放到PL/SQL集合变量中

  1.在select into 语句中使用BULK COLLECT 子句:可以一次将SELECT语句的多行结果检索到集合变量中

1. declare 
2.     type emp_table_type is table of emp%rowtype index by binary_integer; 
3.     emp_table emp_table_type; 
4.   begin 
5.     select * bulk collect into emp_table from emp where deptno=&no; 
6.     for i in 1..emp_table.count loop 
7.        dbms_output.put_line('雇员姓名:'||emp_table(i).ename); 
8.     end loop; 
9.   end; 

  2.在DML的返回语句中使用BULK COLLECT子句

  为了取得DML操作所改变的数据,可以使用RETURNING子句.

1. declare 
2.    type ename_table_type is table of emp.ename%type; 
3.    ename_table ename_table_type; 
4.  begin 
5.    delete from emp where deptno=&no; 
6.      returning ename bulk collect into ename_table; 
7.    dbms_output.put('雇员名'); 
8.    for i in 1..ename_table.count loop 
9.       dbms_output.put(ename_table(i)|| ' '); 
10.    end loop; 
11.    dbms_output.new_line; 
12.  end; 

 

 

转载于:https://blog.51cto.com/4155621/950128