8.4比较集合
函数cardinality用于返回嵌套表变量的元素个数
操作符SUBMULTISET OF用于确定一个嵌套表是否为另一个嵌套表的子集
操作符MEMBER OF用于检测特定数据是否为嵌套表元素
操作符IS A SET用于检测嵌套表是否包含重复的元素值
操作符IS EMPTY用于检测嵌套表是否为NULL.
1.检测集合是否为NULL
- declare
- type name_table_type is table of varchar2(10);
- name_table name_table_type;
- begin
- if name_table is empty then
- dbms_output.put_line('name_table未初始化');
- end if;
- end;
2.比较嵌套表是否相同
使用比较符=和!=检测两个嵌套表是否相同.不能比较VARRAY和索引表
- declare
- type name_table_type is table of varchar2(10);
- name_table1 name_table_type;
- name_table2 name_table_type;
- begin
- name_table1:=name_table_type('scott');
- name_table2:=name_table_type('smith');
- if name_table1=name_table2 then
- dbms_output.put_line('两个嵌套表完全相同');
- else
- dbms_output.put_line('两个嵌套表数值不同');
- end if;
- end;
3.在嵌套表上使用集合操作符
在嵌套表上使用ANSI集合操作符CARDINALITY,MEMBER OF, IS A SET.不适用于VARRAY和索引表
使用函数CARDINALITY
- declare
- type nt_table_type is table of number;
- nt1 nt_table_typent_table_type:=nt_table_type(1,2,3,1);
- begin
- dbms_output.put_line('元素个数:'||cardinality(nt1));
- end;
使用操作符SUBMULTISET OF:用于确定一个嵌套表是否为另一个嵌套表的子集.
- declare
- type nt_table_type is table of number;
- nt1 nt_table_typent_table_type:=nt_table_type(1,2,3);
- nt2 nt_table_typent_table_type:=nt_table_type(1,2,3,4);
- begin
- if nt1 submultiset of nt2 then
- dbms_output.put_line('nt1是nt2的子集);
- end if;
- end;
使用操作符MEMBER OF :用于检测特定数据是否为嵌套表的元素.
- declare
- type nt_table_type is table of number;
- nt1 nt_table_typent_table_type:=nt_table_type(1,2,3,5);
- v1 number:=&v1;
- begin
- if v1 member of nt1 then
- dbms_output.put_line('v1是nt1的元素');
- end if;
- end;
使用操作符IS A SET:用于检测嵌套表是否包含重复的元素值
- declare
- type nt_table_type is table of number;
- nt1 nt_table_typent_table_type:=nt_table_type(1,2,3,5);
- begin
- if nt1 is a set then
- dbms_output.put_line('嵌套表NT1无重复值');
- end if;
- end;
8.5批量绑定
当在select,insert,update,delete语句上处理批量数据时,通过批量绑定,可以极大的加快数据处理速度,提高应用程序性能
批量绑定是使用BULK COLLECT子句和FORALL语句来完成的
BULK COLLECT 子句用于取得批量数据,该子句只能用于SELECT语句,FETCH语句和DML返回子句
FORALL只适用于执行批量的DML操作
1.不使用批量绑定
- 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;
- start_time number(10);
- end_time number(10);
- begin
- for i in 1..5000 loop
- id_table(i):=i;
- name_table(i):='name'||to_char(i);
- end loop;
- start_time:=dbms_utility.get_time;
- for i in 1..id_table.count loop
- insert into demo values(id_table(i),name_table(i));
- end loop;
- end_time:=dbms_utility.get_time;
- dbms_output.put_line('总计时间(秒):'||to_char((end_time-start_time)/100));
- end;
2.使用批量绑定
- 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;
- start_time number(10);
- end_time number(10);
- begin
- for i in 1..5000 loop
- id_table(i):=i;
- name_table(i);='name'||to_char(i);
- end loop;
- start_time:=dbms_utility.get_time;
- forall i in 1..id_table.count
- insert into demo values(id_table(i),name_table(i));
- end_time:=dbms_utility.get_time;
- dbms_output.put_line('总计时间(秒):'||to_char((end_time-start_time)/100));
- end;
8.6 FORALL语句
执行批量insert,update,delete操作时,使用forall语句,FORALL不是循环语句
oracle9i当使用FORALL语句时,必须具有连续的元素
oracle10g通过使用indices of和values of子句,可以使用不连续的集合元素.
forall三种执行语法:
- forall index in lower_bound..upper_bound sql_statement;
- forall index in indices of collection [between lower_bond.and.upper_bound] sql_statement;
- forall index in values of index_collection sql_statement;
1.在insert语句上使用批量绑定
- 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语句上使用批量绑定
- 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..5 loop
- id_table(i):=i;
- name_table(i):='n'||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语句上使用批量绑定
- declare
- type id_table_type is table of number(6) index by binary_integer;
- id_table id_table_type;
- begin
- for i in 1..3 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语句中使用部分集合元素
- 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):=11-i;
- end loop;
- forall i in 8..10
- insert into demo (id) values (id_table(i));
- end;
5.在FORALL语句上使用INDECES OF子句:用于跳过NULL集合元素
- declare
- type id_table_type is table of number(6);
- id_table id_table_type;
- begin
- id_table:=id_table_type(1,null,3,null,5);
- forall i in indices of id_table
- delete from demo where id=id_table(i);
- end;
6.在FORALL语句上使用VALUES OF子句
- create table new_demo as select * from demo where 1=0
- declare
- type id_table_type is table of demp.id%type;
- type name_table_type is table of demo.name%type;
- id_table id_table_type;
- name_table name_table_type;
- type index_pointer_type is table of pls_integer;
- index_pointer index_pointer_type;
- begin
- select * bulk collect into id_table,name_table from demo;
- index_pointer:=index_pointer_type(6,8,10);
- forall i in values of index_pointer
- insert into new_demo values(id_table(i),name_table(i));
- end;
7.使用SQL%BULK_ROWCOUNT属性:专门为FORALL语句提供,用于取得在执行批量绑定操作时第i个元素所作用的行数
- declare
- type dno_table_type is table of number(3);
- dno_table dno_table_typedno_table_type:=dno_table_type(10,20);
- begin
- forall i in 1..dno_table.count
- update emp set salsal=sal*1.1 where deptno=dno_table(i);
- dbms_output.put_line('第2个元素更新的行数:'||sql%bulk_rowcount(2));
- end;
8.7 BULK COLLECT子句
用于取得批量数据,只适用于select into 语句,fetch into 语句和DML返回子句
可将批量数据存放到PL/SQL集合变量中
1.在select into 语句中使用BULK COLLECT 子句:可以一次将SELECT语句的多行结果检索到集合变量中
- declare
- type emp_table_type is table of emp%rowtype index by binary_integer;
- emp_table emp_table_type;
- begin
- select * bulk collect into emp_table from emp where deptno=&no;
- for i in 1..emp_table.count loop
- dbms_output.put_line('雇员姓名:'||emp_table(i).ename);
- end loop;
- end;
2.在DML的返回语句中使用BULK COLLECT子句
为了取得DML操作所改变的数据,可以使用RETURNING子句.
- declare
- type ename_table_type is table of emp.ename%type;
- ename_table ename_table_type;
- begin
- delete from emp where deptno=&no;
- returning ename bulk collect into ename_table;
- dbms_output.put('雇员名');
- for i in 1..ename_table.count loop
- dbms_output.put(ename_table(i)|| ' ');
- end loop;
- dbms_output.new_line;
- end;
转载于:https://blog.51cto.com/4155621/950128