使用dbms_output输出数据或者消息时,必须要将sqlplus的环境变量设置为ON。
集合类型包括索引表,嵌套表和变长数组。
1、索引表,下标可为负值,长度没有限制。
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type;
type_name:用于指定用户自定义数据类型名称
element_type:用于指定索引标的数据类型
NOT NULL:表示不允许引用NULL元素
key_type:用于指定索引表元素下标的数据类型(BINARY_INTEGER,PLS_INTEGER OR VARCHAR2)
exp:
set serveroutput on
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
ename_tabme ename_table_type;
BEGIN
SELECT ename INTO ename_table(-1) FROM emp WHERE empno=&no;
dbms_output.put_line(ename_table(-1));
END;
----------------------------------------
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;
2、嵌套表
下标从1开始,没有极限值,没有顺序,值的存储可以是稀疏的。
TYPE table_name IS TABLE OF element_type ;
在使用时,必须先初始化。
emp_name table_name:=table_name('a','b');
3、变长数组:
类似数组。元素下标从1开始,并且长度是有限制的。
TYPE varray_type_name IS VARRAY(limit);-- OF element_typelimit is not null
在使用VARRAY 元素时,必须使用其构造方法初始化VARRAY元素。
……
varray_type varray_type_name;
begin
varray_type:=varray_type_name('a','a','a');
……
PL/SQL变量用于处理单行单列数据,PL/SQL记录用于处理单行多列,PL/SQL集合用于处理多行单列。
为了在PL/SQL中处理多行多列数据,可以使用PL/SQL记录表。如:
DECALARE
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 eno=&no;
dbms_output_put(emp_table(1).ename);
END;
集合方法:
EXISTS:确定集合元素是否存在 IF varray_type.EXISTS(1) THEN...
COUNT:返回当点集合元素的总个数 varray_type.COUNT
LIMIT:返回集合元素的最大个数 索引表和嵌套表没有最大个数,返回NULL
FIRST&LAST:返回集合第一个和最后一个元素的下标。非元素。
PRIOR&NEXT 返回当点集合元素的前一个和后一个的下标。非元素。ename_table.prior(5)
EXTEND:用于扩展集合变量的尺寸,并为它们增加元素。只适合用于嵌套表和VARRAY.有三种调用格式:EXTEND,EXTEND(n),EXTEND(n,i),其中EXTEND为集合变量添加一个NULL元素,EXTEND(n)为集合变量添加n个null元素。EXTEND(n,i)则为集合变量添加n个元素值和第i个元素相同的元素。
-- Created on 2008-4-28 by USER
declare
type enum_varray_type is varray(10) of varchar2(20);
enum_varray enum_varray_type;
begin
enum_varray:=enum_varray_type('a');
enum_varray.extend(3,1);
dbms_output.put_line(enum_varray.count);
end;
TRIM:从集合尾部删除元素,trim删除一个,trim(n)删除N个
enum_varray.trim(2);
dbms_output.put_line(enum_varray.count);
DELETE:删除集合元素,只能用与索引表和嵌套表。DELETE 删除所有记录,DELETE(n)删除几个第N个元素,DELETE(m,n)删除集合从m到n个记录。
declare
type enum_type_name is table of varchar2(20) index by binary_integer;
enum_name enum_type_name;
begin
enum_name(0):='a';
enum_name(9):='v';
enum_name(4):='s';
enum_name(-1):='g';
enum_name(8):='f';
dbms_output.put_line(enum_name.count);
enum_name.delete(-1,4);
dbms_output.put_line(enum_name.count);
end;
集合赋值:
1、将一个集合的数据赋值给另一个集合
当使用:=或SQL语句将源集合中的数据赋值给目标集合时,会自动清楚目标集合原有的数据,并将源集合中的数据赋值给该目标集合。
注意:当进行集合赋值时,源集合和目标集合的数据类型必须完全一致。如果集合元素数据类型一致,但集合类型不一致,那也不能进行赋值。
如下面就是错误的:
declare
type name_varray1_type is varray(4) of varchar2(10);
type name_varray2_type is varray(4) of varchar2(10);
name_array1 name_varray1_type ;
name_array2 name_varray2_type ;
begin
name_array1 :=name_varray1_type('ss','d');
name_array2:=name_array1;--这里是错误的
end;
给嵌套表赋值时可用 SET,MULTISET UNION,MULTISET INTERSECT,MULTISET EXCEPT等集合操作符。
1、使用SET操作符
用于取消嵌套表中的重复值。
declare
type nt_table_type IS table of number;
nt_table nt_table_type:=nt_table_type(1,2,3,4,2);
result nt_table_type;
begin
result:=SET(nt_table );
end;
result的结果就变成1,2,3,4
2、使用 MULTISET UNION 操作符
取得两个嵌套表的并集。结果集中包含重复值。
declare
type enum_type_name is table of number;
enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
enum_name2 enum_type_name:=enum_type_name(4,5,6,7,8);
enum_name3 enum_type_name;
i number;
results varchar2(100);
begin
enum_name3:=enum_name1 multiset union enum_name2;
for i in 1..enum_name3.count loop
results:=results||','||enum_name3(i);
end loop;
dbms_output.put_line(substr(results,2,length(results)));
end;
结果:1,2,3,4,1,4,5,6,7,8
3、使用MULTISET UNION DISTINCT操作符
和使用MULTISET UNION唯一不同是去掉了重复值
4、使用 MULTISET INTERSECT 操作符
取得两个嵌套表的交集
5、使用MULTISET EXCEPT 操作符
用于取得两个嵌套表的差集。下面的例子是取得 enum_name1中存在但是enum_name2中不存在的元素:
declare
type enum_type_name is table of number;
enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
enum_name2 enum_type_name:=enum_type_name(4,5,6,7,8);
enum_name3 enum_type_name;
i number;
results varchar2(100);
begin
enum_name3:=enum_name1 multiset except distinct enum_name2;
for i in 1..enum_name3.count loop
results:=results||','||enum_name3(i);
end loop;
dbms_output.put_line(substr(results,2,length(results)));
end;
结果:1,2,3
比较集合:
1、检测集合是否为NULL (嵌套表和VARRAY)
IS NULL
declare
type enum_type_name is table of number;
enum_name3 enum_type_name;
i number;
results varchar2(100);
begin
if enum_name3 is null then
dbms_output.put_line('必须初始化');
else
null;
end if;
end;
IS EMPTY --只适用于嵌套表
2、比较嵌套表是否相同 在oracle10后用!=或者=
declare
type enum_type_name is table of number;
enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
enum_name2 enum_type_name:=enum_type_name(4,5,6,7,8);
begin
if enum_name1=enum_name2 then
dbms_output.put_line('嵌套表相通');
else
dbms_output.put_line('嵌套表不同');
end if;
end;
结果:嵌套表不同
3、在嵌套表中使用集合操作符
(1)、使用函数CARDINALITY
此函数用于返回嵌套表变量的元素个数。和count应该差不多。不同的就是这个函数只能用在嵌套表中。
(2)、使用操作符SUBMULTISET OF
用于确定有一个嵌套表是否为另一个嵌套表的子集。
declare
type enum_type_name is table of number;
enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
enum_name2 enum_type_name:=enum_type_name(1,2);
begin
if enum_name2 submultiset of enum_name1 then
dbms_output.put_line('enum_name2 是 enum_name1 的子集');
else
dbms_output.put_line('enum_name2 不是 enum_name1 的子集');
end if;
end;
结果:enum_name2 是 enum_name1 的子集
(3)、使用 MEMBER OF 操作符
检测特定数据是否为嵌套表元素。
declare
type enum_type_name is table of number;
enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
i number:=&vl;
begin
if i member of enum_name1 then
dbms_output.put_line(i||' 是 enum_name1 的元素');
else
dbms_output.put_line(i||' 不是 enum_name1 的元素');
end if;
end;
输入:1
结果:1 是 enum_name1 的元素
(4)、IS A SET 操作符
用于检测嵌套表是不是没有重复元素
declare
type enum_type_name is table of number;
enum_name1 enum_type_name:=enum_type_name(1,2,3,4,1);
begin
if enum_name1 is a set then
dbms_output.put_line('enum_name1 没有重复元素');
else
dbms_output.put_line('enum_name1 有重复元素');
end if;
end;
结果:enum_name1 有重复元素
(5) for all
FOR ALL i INDICES OF 1..id_table.COUNT
insert into demo value(id_table(i));
............................
INDICES 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');
FOR ALL i INDICES OF id_table
delete from demo where id=id_table(i);
END;
BULK COLLECT:不要忘记我
适用于SELECT INTO 和 FECTH INTO 和DML 的返回子句。
在9i之前,当编写SELECT INTO 语句时,必须返回一行语句,并且最多只能返回一行语句。
从9i开始适用 BULK COLLECT 可以将SELECT的多行结果检索到集合当中。
DECLARE
TYPE emp_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
BEGIN
SELECT ename BULK COLLECT INTO emp_table FROM emp WHERE deptno:=&depno;
FOR i IN 1..emp_table.COUNT LOOP
dbms_out_put.put_line('雇员名:'||emp_table(i));
END LOOP;
END;
在DML返回语句中使用:
DECLARE
TYPE emp_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
BEGIN
DELETE FROM emp WHERE deptno:=&depno RETURN ename BULK COLLECT INTO emp_table ;
FOR i IN 1..emp_table.COUNT LOOP
dbms_out_put.put_line('雇员名:'||emp_table(i));
END LOOP;
END;