8.pl/sql集合
处理单行单列数据,可以使用标量变量,处理单行多列的数据,可以使用PL/SQL记录
处理单列多行数据,可以使用PL/SQL集合
PL/SQL集合类型类似于高级语言数组的一种复合数据类型
包括:索引表(PL/SQL表),嵌套表(NESTED TABLE),变长数组(VARRAY)三种
8.1 索引表:PL/SQL表 元素个数没有限制,并且下标可以是负值
定义索引表:
- type type_name is table of element_type [not null] index by key_type;
- identifier type_name;
在索引表中使用BINARY_INTEGER和PLS_INTEGER
- set serveroutput on
- declare
- type ename_table_type is table of emp.ename%type index by binary_integer;
- ename_table ename_table_type;
- begin
- select ename into ename_table(-1) from emp where empno=&no;
- dbms_output.put_line('雇员名:'||ename_table(-1));
- end;
在索引表中使用VARCHAR
- set serveroutput on
- declare
- type area_table_type is table of number index by varchar2(10);
- area_table area_table_type;
- begin
- area_table('北京'):=1;
- area_table('上海'):=2;
- area_table('广州'):=3;
- dbms_output.put_line('第一个元素:'||area_table.first);
- dbms_output.put_line('最后一个元素:'||area_table.last);
- end;
8.2 嵌套表:元素个数从1开始,并且元素个数没有限制
定义嵌套表:
- type type_name is table of element_type;
- identifier type_name;
- declare
- type ename_table_type is table of emp.ename%type;
- ename_table ename_table_typeename_table_type:=ename_table_type('A','A');
在PL/SQL块中使用嵌套表:使用嵌套表变量时,必须首先使用构造方法初始化嵌套表变量,然后才能在块内引用嵌套表元素
- declare
- type ename_table_type is table of emp.ename%type;
- ename_table ename_table_type;
- begin
- ename_table:=ename_table_type('mary','mary','mary');
- dbms_output.put_line('雇员名:'|| ename_table(2));
- select ename into ename_table(2) from emp where empno=&no;
- dbms_output.put_line('雇员名:'||ename_table(2));
- end;
在表列中使用嵌套表:
在表列中使用嵌套表类型,必须首先使用CREATE TYPE命令建立嵌套表类型.
当使用嵌套表类型作为表列的数据类型时,必须要为嵌套表列指定专门的存储表
- create type phone_type is table of varchar2(20);
- /
- create table employee(
- id number(4),name varchar2(10),sal number(6,2),
- phone phone_type
- )nested table phone store as phone_table;
8.3 在PL/SQL块中为嵌套表列插入数据
当定义嵌套表类型时,ORACLE自动为该类型生成相应的构造方法.当为嵌套表列插入数据时,需要使用嵌套表的构造方法
- begin
- insert into employee values(1,'scott',800,phone_type('0471-3456788','13804711111'));
- end;
在PL/SQL块中检索嵌套表列的数据
当在PL/SQL块中检索嵌套表列的数据时,需要定义嵌套表类型的变量接受其数据.
- set serveroutput on
- declare
- phone_table phone_type;
- begin
- select ename into phone_table from emp where empno=&no;
- for i in 1..phone_table.count loop
- dbms_output.put_line('电话:'||phone_table(i));
- end loop;
- end;
8.4 在pl/sql块中更新嵌套表列的数据
更新嵌套表列的数据时,首先需要定义嵌套表变量,并使用构造方法初始化变量,然后才可在执行部分使用UPDATE语句更新数据
- declare
- phone_table phone_typephone_type:=phone_type('0471-3456788','13804711111','0471-2233066','13056278568');
- begin
- update employee set phone=phone_talbe where id=1;
- end;
8.5变长数组(varray)
VARRAY也是一种用于处理PL/SQL数组的数据类型, 它也可以做为表列的数据类型使用.
元素下标以1开始,并且元素的最大个数是有限制的
定义VARRAY的语法:
- type type_name is varray(size_limite) of element_type [not mull];
- identifier type_name;
当使用VARRAY元素时,必须要使用其构造方法初始化VARRAY元素.
- declare
- type ename_table_type is varray(20) of emp.ename%type;
- ename_talbe ename_table_typeename_table_type:=ename_table_type('A','A');
8.6 在PL/SQL块中使用VARRAY
必须首先使用其构造方法来初始化VARRAY变量,然后才能在PL/SQL块内引用VARRAY元素
- declare
- type ename_table_type is varray(20) of emp.ename%type;
- ename_table ename_table_typeename_table_type:=ename_table_type('mary');
- begin
- select ename into ename_table1) from emp where empno=&no;
- dbms_output.put_line('雇员名:'||ename_table(1));
- end;
在表列中使用varray
要在表列中引用该数据类型,则必须使用CREATE TYPE命令建立VARRAY类型
- create type phone_type is varray(20) of varchar2(20);
- /
- create table employee(
- id number(4),name varchar2(10),
- sal number(6,2),phone phone_type);
在PL/SQL块中操纵VARRAY列的方法与操纵嵌套表列的方法完全相同.嵌套表列元素个数没有限制,而VARRAY列的元素个数是有限制的.
PL/SQL记录表
PL/SQL变量处理单行单列数据
PL/SQL记录处理单行多列数据
PL/SQL集合处理多行单列数据
PL/SQL记录表处理多行多列数据
8.7 PL/SQL记录表结合了PL/SQL记录和PL/SQL集合的优点
- declare
- type emp_table_type is table of emp%rowtype index by binary_integer;
- emp_table emp_table_type;
- begin
- select * into emp_table(1) from emp where empno=&no;
- dbms_output.put_line('雇员姓名:'||emp_table(1).ename);
- dbms_output.put_line('雇员姓名:'||emp_table(1).sal);
- end;
8.8 多级集合
多级集合是指嵌套了集合类型的集合类型
在PL/SQL块中使用多级VARRAY:实现多维数组功能
定义二维VARRAY(10,10):
- declare
- type a1_varray_type is varray(10) of int;--定义一维VARRAY
- type na1_varray_type is varray(10) of a1_varray_type;--定义二维VARRAY集合
- --初始化二维集合变量
- nv1 nal_varray_typenal_varray_type:=nal_varray_type(
- a1_varray_type(58,100,102),
- a1_varray_type(55,6,73),
- a1_varray_type(2,4);
- )
- begin
- dbms_output.put_line('显示二维数组所有元素');
- for i in 1..nv1.count loop
- for j in 1..nv1(i).count loop
- dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));
- end loop;
- end loop;
- end;
在PL/SQL块中使用多级嵌套表
如果多维数组的元素个数没有限制,那么可以在嵌套表类型中嵌套另一个嵌套表类型
8.9 二维嵌套表
- declare
- type a1_table_type is table of int;--定义一维嵌套表
- type nal_table_type is table of a1_table_type;--定义二维嵌套表集合
- --初始化二维集合变量
- nvl nal_table_typenal_table_type:=nal_table_type(
- a1_table_type(2,4),
- a1_table_type(5,73));
- begin
- dbms_output.put_line('显示二维数组所有元素');
- for i in 1..nvl.count loop
- for j in 1..nvl(i).count loop
- dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));
- end loop;
- end loop;
- end;
在PL/SQL块中使用多级索引表
二维索引表:
- declare
- type a1_table_type is table of int index by binary_integer;
- type nal_table_type is table of al_table_type index by binary_integer;
- nvl nal_table_type;
- begin
- nvl(1)(1):=10;
- nvl(1)(2):=5;
- nvl(2)(1):=100;
- nvl(2)(2):=50;
- dbms_output.put_line('显示二维数组所有元素');
- for i in 1..nvl.count loop
- for j in 1..nvl(i).count loop
- dbms_output.put_line('nvl('||i||','||j||')='||nvl(i)(j));
- end loop;
- end loop;
- end;
8.10集合方法:ORACLE提供的用于操纵集合变量的内置函数或过程,其中EXISTS,COUNT,LIMIT,FIRST,NEXT,FRIOR和NEXT是函数
而EXTEND,TRIM和DELETE则是过程
集合方法只能在PL/SQL语句中使用,不能在SQL语句中使用.
集合方法EXTEND和TRIM只适用于嵌套表和VARRAY,而不适合于索引表
1.EXISTS:用于确定集合元素是否存在
- declare
- type ename_table_type is table of emp.ename%type;
- ename_table ename_table_typeename_table_type:=ename_table_type('A','A');
- begin
- if ename_table.exists(1) then
- ename_table(1):='SCOTT';
- dbms_output.put_line(ename_table(1) );
- else
- dbms_output.put_line('必须初始化集合元素');
- end if;
- end;
2.COUNT:用于返回当前集合变量中的元素总个数.
- declare
- type ename_table_type is table of emp.ename%type index by binary_integer;
- ename_table ename_table_type;
- begin
- ename_table(-5):='scott';
- ename_table(1):='smith';
- ename_table(5):='mary';
- ename_table(10):='blake';
- dbms_output.put_line('集合元素总个数:'||ename_table.count);
- end;
3.LIMIT:用于返回集合元素的最大个数.因为嵌套表和索引表的余数个数没有限制,返回NULL
对于VARRAY来说,该方法返回VARRAY所允许的最大元素个数
- declare
- type ename_table_type is varray(20) of emp.ename%type;
- ename_table ename_table_typeename_table_type:=ename_table_type('mary');
- begin
- dbms_output.put_line('集合元素的最大个数:'||ename_table.limit);
- end;
4.FIRST和LAST:FIRST用于返回集合变量第一个元素的下标,而LAST方法则用于返回集合变量最后一个元素的下
- declare
- type ename_table_type is table of emp.ename%type index by binary_integer;
- ename_table ename_table_type;
- begin
- ename_table(-5):='scott';
- ename_table(1):='smith';
- ename_table(5):='mary';
- ename_table(10):='blake';
- dbms_output.put_line('第一个元素:'||ename_table.first);
- dbms_output.put_line('第一个元素:'||ename_table(-5));
- dbms_output.put_line('最后一个元素:'||ename_table.last);
- end;
5.FRIOR和NEXT:PRIOR返回当前集合元素的前一个元素的下标,而NEXT方法则用于返回当前集合元素的后一个元素的下标
- declare
- type ename_table_type is table of emp.ename%type index by binary_integer;
- ename_table ename_table_type;
- begin
- ename_table(-5):='scott';
- ename_table(1):='smith';
- ename_table(5):='mary';
- ename_table(10):='blake';
- dbms_output.put_line('元素5的前一个元素:'||ename_table.prior(5));
- dbms_output.put_line('元素5的后一个元素:'||ename_table.next(5));
- end;
6.EXTEND:用于扩展集合变量的尺寸,并为它们增加元素.只适用于嵌套表和VARRAY.
三种调用格式:EXTEND,EXTEND(N),EXTEND(N,I):添加N个元素,值与第I个元素相同
- declare
- type ename_table_type is varray(20) of varchar2(10);
- ename_table ename_table_type;
- begin
- ename_table:=ename_table_type('mary');
- ename_table.extend(5,1);
- dbms_output.put_line('元素个数:'||ename_table.count);
- end;
7.TRIM:用于从集合尾部删除元素,有TRIM和TRIM(N)两种调用格式.
只适用于嵌套表和VARRAY
- declare
- type ename_table_type is table of varchar2(10);
- ename_table ename_table_type;
- begin
- ename_table:=ename_table_type('a','a','a','a','a');
- ename_table.trim(5);
- dbms_output.put_line('元素个数:'||ename_table.count);
- end;
8.DELETE:删除结合元素,但该方法只适用于嵌套表和索引表,不适用于VARRAY.
有DELETE,DELETE(N),DELETE(M,N)三种调用方式.
DETELE(M,N)删除集合变量从M到N之间的所有元素
- declare
- type ename_table_type is table of emp.ename%type index by binary_integer;
- ename_table ename_table_type;
- begin
- ename_table(-5):='scott';
- ename_table(1):='smith';
- ename_table(5):='mary';
- ename_table(10):='blake';
- ename_table.delete(1);
- if ename_table. exists(5) then dbms_output.put_line('success');
- else
- dbms_output.put_line('元素总个数:'||ename_table.count);
- end if;
- end;
8.11集合赋值
使用嵌套表和VARRAY时,通过执行INSERT,UPDATE,FETCH,SELECT赋值语句,可以将一个集合的数据赋值给另一个集合.
当给嵌套表赋值时,还可以使用SET,MULTISET UNION,MULTISET INTERSECT,MULTISET EXCEPT等集合操作符
SET:用于取消嵌套表中的重复值.
MULTISET UNION:取得两个嵌套表的并集(DISTINCT)
MULTISET INTERSECT:用于取得两个嵌套表的交集.
NULTISET EXCEPT:用于取得两个嵌套表的差集
1.将一个集合的数据赋值个另一个集合
源集合和目标集合的数据类型必须完全一致.
- declare
- type name_array_type is varray(4) of varchar2(10);
- name_array1 name_array_type;
- name_array2 name_array_type;
- begin
- name_array1:=name_array_type('scott','smith');
- name_array2:=name_array_type('a','a','a','a');
- dbms_output.put_line('name_array2的原数据:');
- for i in 1..name_array2.count loop
- dbms_output.put_line(' '||name_array2(i));
- end loop;
- dbms_output.new_line;
- name_array2:=name_array1;
- dbms_output.put('name_array2的新数据:');
- for i in 1..name_array2.count loop
- dbms_output.put(' '||name_array2(i));
- end loop;
- dbms_output.new_line;
- end;
2.给集合赋NULL值:清空集合变量的所有数据(集合方法DETELE,TRIM也可以)
- declare
- type name_varray_type is varray(4) of varchar2(10);
- name_array name_varray_type;
- name_empty name_varray_type;
- begin
- name_array:=name_varray_type('scott','smith');
- dbms_output.put_line('name_array的原有元素个数:'||name_array.count);
- name_array:=name_empty;
- if name_array is null then
- dbms_output.put_line('name_array的现有元素个数:0');
- end if;
- end;
3.使用集合操作符给嵌套表赋值
1.使用SET操作符:用于取消特定嵌套表中的重复值.
- declare
- type nt_table_type is table of number;
- nt_table nt_table_typent_table_type:=nt_table_type(2,4,3,1,2);
- result nt_table_type;
- begin
- result:=set(nt_table);
- dbms_output.put('result:');
- for i in 1..result.count loop
- dbms_output.put(' '||result(i));
- end loop;
- dbms_output.new_line;
- end;
2.使用MULTISET UNION操作符:取得两个嵌套表的并集.结果集中会包含重复值
- 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(3,4,5);
- result nt_table_type;
- begin
- result:=nt1 multiset union nt2;
- dbms_output.put('result:');
- for i in 1..result.count loop
- dbms_output.put(' '||result(i));
- end loop;
- dbms_output.new_line;
- end;
3.使用MULTISET UNION DISTINCT操作符:用于取得两个嵌套表的并集,并取消重复结果.
- 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(3,4,5);
- result nt_table_type;
- begin
- result:=nt1 multiset union distinct nt2;
- dbms_output.put('result:');
- for i in 1..result.count loop
- dbms_output.put(' '||result(i));
- end loop;
- dbms_output.new_line;
- end;
4.使用MULTISET INTERSECT操作符:用于取得两个嵌套表的交集
- 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(3,4,5);
- result nt_table_type;
- begin
- result:=nt1 multiset intersect nt2;
- dbms_output.put('result:');
- for i in 1..result.count loop
- dbms_output.put(' '||result(i));
- end loop;
- dbms_output.new_line;
- end;
5.使用MULTISET EXCEPT操作符:取得两个嵌套表的差集.在NT1中存在,但在NT2中不存在
- 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(3,4,5);
- result nt_table_type;
- begin
- result:=nt1 multiset except nt2;
- dbms_output.put('result:');
- for i in 1..result.count loop
- dbms_output.put(' '||result(i));
- end loop;
- dbms_output.new_line;
- end;
转载于:https://blog.51cto.com/4155621/949920