-- 作者:admin
-- 发布时间:2008/11/21 19:13:53
-- [推荐]Oracle数据库PL-SQL学习资料之六——集合
8 集合 记录和集合是PL/SQL中的两种复合类型。记录主要用于组合多种异质数据,而集合主要用于组合多个同质数据,类似于数组
集合主要包含下面几种子类型:index-by表,嵌套表和可变数组,其中还可以使用多层集合(集合的集合)。 其中,index-by表和嵌套表类似,但是前者只能存在于PL/SQL中,而后者可以存在于数据库表中。两者统称为PL/SQL表。 可变数组和PL/SQL表的区别主要在于具有元素数量的上限,而PL/SQL表没有元素数量的声明上限。
8、1 index-by表 8、1、1 简单使用 声明和使用如下: DECLARE TYPE NameTab IS TABLE OF stu.name%TYPE INDEX BY BINARY_INTEGER; TYPE DateTab IS TABLE OF DATE INDEX BY BINARY_INTEGER; v_Names NameTab; v_Dates DateTab; BEGIN v_Names(1) := \'LSQ\'; v_Dates(-4) := SYSDATE - 1; END; /
说明: 1)INDEX BY BINARY_INTEGER是必须的,和嵌套表相比,嵌套表没有 2)使用方法类似于名值对序列,任何index-by表元素都是由一个唯一的名(一个整数)和一个值(类型取决于index-by表的定义) 3)index-by表元素的名不必有序,也不必连续,只是一个唯一的标记,而且必须是BINARY_INTEGER类型 4)index-by表元素的个数不限,唯一的限制是BINARY_INTEGER类型的取值范围(4个字节长度,2G个数) 5)index-by表不能以下标访问。如果将index-by表传递给其他语言,相应序列的元素下标从1开始计数 6)给index-by表元素赋值,如果该名对应的元素不存在,会自动创建之,如果存在会自动覆盖之;访问的时候,如果该名对应的元素不存在,会报错。
8、1、2 非标量类型的index-by表 除了使用标量类型来创建index-by表,还可以使用记录、对象和集合本身(多层集合)
1)记录的index-by表 DECLARE TYPE StudentTab IS TABLE OF stu%ROWTYPE INDEX BY BINARY_INTEGER; /* Each element of v_Students is a record */ v_Students StudentTab; BEGIN /* Retrieve the record with sid = 1 and store it into v_Students(100). */ SELECT * INTO v_Students(100) FROM stu WHERE sid = 1;
/* Directly assign to v_Students(1). */ v_Students(1).sid := 88; v_Students(1).name := \'Lemon\'; END; /
2)对象的index-by表 CREATE OR REPLACE TYPE MyObject AS OBJECT ( field1 NUMBER, field2 VARCHAR2(20), field3 DATE ); /
DECLARE TYPE ObjectTab IS TABLE OF MyObject INDEX BY BINARY_INTEGER; /* Each element of v_Objects is an instance of the MyObject object type. */ v_Objects ObjectTab; BEGIN /* Directly assign to v_Objects(1). First we have to initialize the object type. */ v_Objects(1) := MyObject(1, NULL, NULL); v_Objects(1).field2 := \'Hello World!\'; v_Objects(1).field3 := SYSDATE; END; /
8、2 嵌套表 可以将其看成是具有两个列(key和value)的数据库表。 它和index-by表相似,区别在于: 1)在定义上,没有INDEX BY BINARY_INTEGER语句 2)嵌套表存在于数据库表中,而index-by表只能存在于PL/SQL中 3)嵌套表的名必须有序(事实上它是一个只读的从1开始的下标计数值,用户不能主动赋值于名),且不能为负数,而index-by表不要求 4)嵌套表需要初始化,并可以同时指定元素的值(不是个数),否则为null,此时添加元素将会出错,而index-by表无需初始化,如: set serveroutput on size 1000000; DECLARE TYPE MyNestedTab IS TABLE OF varchar(8); v_List MyNestedTab; BEGIN --v_List:=MyNestedTab(\'A203\',\'XX\'); v_list(1):=\'A023\'; DBMS_OUTPUT.PUT_LINE(v_List(1)); END; /
注意:初始化可以具有相同值,毕竟名不同,如: set serveroutput on size 1000000; DECLARE TYPE MyNestedTab IS TABLE OF varchar(8); v_List MyNestedTab; BEGIN v_List:=MyNestedTab(\'XX\',\'XX\'); v_list(1):=\'A023\'; DBMS_OUTPUT.PUT_LINE(v_List(1)); DBMS_OUTPUT.PUT_LINE(v_List(2)); END; /
初始化时没有指定初始值,不能直接赋值,因为此时的嵌套表没有元素,如: set serveroutput on size 1000000; DECLARE TYPE MyNestedTab IS TABLE OF varchar(8); v_List MyNestedTab; BEGIN v_List:=MyNestedTab(); v_list(1):=\'A023\'; DBMS_OUTPUT.PUT_LINE(v_List(1)); END; /
初始化时没有指定初始值,此时的嵌套表虽然没有元素,但是存在,不为null,如: set serveroutput on size 1000000; DECLARE TYPE MyNestedTab IS TABLE OF varchar(8); v_List1 MyNestedTab; v_List2 MyNestedTab; BEGIN v_List1:=MyNestedTab(); if v_List1 is null then DBMS_OUTPUT.PUT_LINE(\'null\'); else DBMS_OUTPUT.PUT_LINE(\'Not null\'); end if; if v_List2 is null then DBMS_OUTPUT.PUT_LINE(\'null\'); else DBMS_OUTPUT.PUT_LINE(\'Not null\'); end if; END; /
表是否为空和元素是否为空是不同的概念,使用not null可以表示不能元素为空,如: set serveroutput on size 1000000; DECLARE TYPE MyNestedTab IS TABLE OF varchar(8) not null; v_List1 MyNestedTab; BEGIN v_List1:=MyNestedTab(1); v_List1(1):=null; EXCEPTION WHEN others THEN DBMS_OUTPUT.PUT_LINE(SQLCODE()); DBMS_OUTPUT.PUT_LINE(substr(SQLERRM(),1,200)); END; /
对于初始化时没有指定初始值,如果要赋值,需要extend方法增加嵌套表的大小(没有参数的extend方法自动加一个元素),如: set serveroutput on size 1000000; DECLARE TYPE MyNestedTab IS TABLE OF varchar(8); v_List MyNestedTab; BEGIN v_List:=MyNestedTab(); v_List.extend(); v_list(1):=\'A023\'; DBMS_OUTPUT.PUT_LINE(v_List(1)); END; /
也可以利用嵌套表访问表内容,如: set serveroutput on size 1000000; DECLARE TYPE StudentArray IS TABLE OF stu%ROWTYPE; v_StudentArray StudentArray:=StudentArray(null,null,null); begin SELECT * INTO v_StudentArray(1) FROM stu where sid=1; SELECT * INTO v_StudentArray(2) FROM stu where sid=2; SELECT * INTO v_StudentArray(3) FROM stu where sid=3; DBMS_OUTPUT.PUT_LINE(v_StudentArray(1).name); DBMS_OUTPUT.PUT_LINE(v_StudentArray(2).name); DBMS_OUTPUT.PUT_LINE(v_StudentArray(3).name); END; /
甚至可以使用集合的集合来进行表记录操作,如: set serveroutput on size 1000000; DECLARE TYPE StudentArray IS TABLE OF stu%ROWTYPE; TYPE StudentArrayS IS TABLE OF StudentArray; v_StudentArray StudentArrayS:=StudentArrayS(null); begin v_StudentArray(1):=StudentArray(null); SELECT * INTO v_StudentArray(1)(1) FROM stu where sid=1; DBMS_OUTPUT.PUT_LINE(v_StudentArray(1)(1).name); END; /
5)访问嵌套表元素必须使用下标方式,而且从1开始,而index-by表使用名,如: set serveroutput on size 1000000; DECLARE TYPE MyNestedTab IS TABLE OF varchar(8); v_List MyNestedTab; BEGIN v_List:=MyNestedTab(\'A203\',\'XX\'); DBMS_OUTPUT.PUT_LINE(v_List(1)); DBMS_OUTPUT.PUT_LINE(v_List(2)); END; /
8、3 可变数组 可变数组具有元素数量的上限,可以在声明中指定,元素插入可变数组时以索引1开始,一直到声明的最大长度,极限为2G大小 使用时也要初始化,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS VARRAY(10) OF varchar(3) NOT NULL; v_num NumberList; BEGIN v_num:=NumberList(\'3\'); DBMS_OUTPUT.PUT_LINE(v_num(1)); END; /
注意: 1)此时初始化函数中的参数含义为数组元素的初始值,参数的个数不能大于可变数组类型中指定的最大长度,如果没有参数,则表明数组已经存在,但是没有元素,可以使用extend方法增添元素(但是不能将长度扩展超过可变数组类型中指定的最大长度),如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS VARRAY(10) OF NUMBER(3) NOT NULL; v_num NumberList; BEGIN v_num:=NumberList(); v_num.extend(); v_num(1):=2; DBMS_OUTPUT.PUT_LINE(v_num(1)); END; /
2)参数可以为null,表示第一个元素为null,此时不能在定义可变数组时使用not null,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS VARRAY(10) OF NUMBER(3); v_num NumberList; BEGIN v_num:=NumberList(null); v_num.extend(); v_num(1):=2; DBMS_OUTPUT.PUT_LINE(v_num(1)); END; /
8、4 多层集合 声明和一维集合相同,但是集合元素本身是集合类型,使用方法为: DECLARE TYPE t_Numbers IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE t_MultiNumbers IS TABLE OF t_Numbers INDEX BY BINARY_INTEGER; v_MultiNumbers t_MultiNumbers; BEGIN v_MultiNumbers(1)(1) := 12345; END;
注意: 1)如果第一维是嵌套表或者可变数组,需要初始化才能使用,如: DECLARE TYPE t_Numbers IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE t_MultiVarray IS VARRAY(10) OF t_Numbers; v_MultiNumbers t_MultiVarray; v_num t_Numbers; BEGIN v_MultiNumbers:=t_MultiVarray(v_num); v_MultiNumbers(1)(1) := 12345; v_MultiNumbers(1)(2) := 12345; --v_MultiNumbers(2)(2) := 12345; END;
或者直接利用extend生成新的元素,如: DECLARE TYPE t_Numbers IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE t_MultiVarray IS VARRAY(10) OF t_Numbers; v_MultiNumbers t_MultiVarray; BEGIN v_MultiNumbers:=t_MultiVarray(); v_MultiNumbers.extend; v_MultiNumbers(1)(1) := 12345; v_MultiNumbers(1)(2) := 12345; END;
2)如果第一维和第二维都是嵌套表或者可变数组,都需要初始化才能使用,如: DECLARE TYPE t_Numbers IS TABLE OF NUMBER; TYPE t_MultiVarray IS VARRAY(10) OF t_Numbers; v_MultiNumbers t_MultiVarray; v_num t_Numbers; BEGIN v_MultiNumbers:=t_MultiVarray(); v_MultiNumbers.extend; v_MultiNumbers(1):=t_Numbers(1,2); v_MultiNumbers(1)(1) := 12345; v_MultiNumbers(1)(2) := 12345; END;
或者直接利用extend生成新的元素,如: DECLARE TYPE t_Numbers IS TABLE OF NUMBER; TYPE t_MultiVarray IS VARRAY(10) OF t_Numbers; v_MultiNumbers t_MultiVarray; BEGIN v_MultiNumbers:=t_MultiVarray(); v_MultiNumbers.extend; v_MultiNumbers(1):=t_Numbers(); v_MultiNumbers(1).extend; v_MultiNumbers(1).extend; v_MultiNumbers(1)(1) := 12345; v_MultiNumbers(1)(2) := 12345; END;
8、5 数据库中的集合 8、5、1 创建数据库中的集合列 为了能够在数据库和PL/SQL间存储和检索同一个集合类型,该类型必须是模式层类型,需要使用DDL创建,如: CREATE OR REPLACE TYPE NameList AS VARRAY(10) OF VARCHAR2(10); /
此时可以在下面的语句块中使用该集合,如: DECLARE -- This type is local to this block. TYPE DateList IS VARRAY(10) OF DATE;
v_Dates DateList; v_Names NameList; BEGIN NULL; END; /
DECLARE v_Names2 NameList; BEGIN NULL; END; /
以上述可变数组的集合类型可以创建新的表结构,表示选修某课程的全部学生名单,如: CREATE OR REPLACE TYPE NameListVArray AS VARRAY(10) OF VARCHAR2(10); /
create table gradeStusVArray ( cid varchar2(4), title varchar2(20), stuname NameListVArray ) / 注意:Oracle为了管理灵活,将大小大于4K的可变数组集合列放入LOB中(甚至可以为LOB单独指定存储参数),和其他表列分开存储,
也可以使用嵌套表的集合类型创建新的表结构,表示选修某课程的全部学生名单,如: CREATE OR REPLACE TYPE NameListNestedTable AS TABLE OF VARCHAR2(10); /
create table gradeStusNestedTable ( cid varchar2(4), title varchar2(20), stuname NameListNestedTable) NESTED TABLE stuname STORE AS stu_name_tab;
注意: 1)此时的语句块后面不能加“/”,否则出错(原因不明) 2)NESTED TABLE子句用于指定嵌套表的名称,与可变数组不同,嵌套表总是与当前表是分离存储的,现有的列只有一个ref值指向嵌套表数据行 3)虽然嵌套表与当前表独立,但是不能直接访问嵌套表,如: select * from stu_name_tab; drop table stu_name_tab;
但是可以了解其结构,如: set linesize 100; desc stu_name_tab; 4)删除语句: drop table gradeStusVArray; drop table gradeStusNestedTable;
如果删除当前表,嵌套表随之删除
8、5、2 操纵数据库中的集合列 8、5、2、1 操纵整个集合 对可变数组和嵌套表的操作,从本质上看,区别不大
1)使用insert语句 对可变数组集合列的操纵: declare v_stu_name1 NameListVArray:=NameListVArray(\'Ben\',\'Lee\',\'Nina\'); v_stu_name2 NameListVArray:=NameListVArray(\'Jack\',\'Nina\'); begin insert into gradeStusVArray values(\'A011\',\'Java\',v_stu_name1); insert into gradeStusVArray values(\'B021\',\'SQL Server\',v_stu_name2); insert into gradeStusVArray values(\'A004\',\'C++\',NameListVArray(\'Jim\',\'Lee\')); end;
对嵌套表集合列的操纵: declare v_stu_name1 NameListNestedTable :=NameListNestedTable (\'Ben\',\'Lee\',\'Nina\'); v_stu_name2 NameListNestedTable :=NameListNestedTable (\'Jack\',\'Nina\'); begin insert into gradeStusNestedTable values(\'A011\',\'Java\',v_stu_name1); insert into gradeStusNestedTable values(\'B021\',\'SQL Server\',v_stu_name2); insert into gradeStusNestedTable values(\'A004\',\'C++\',NameListNestedTable(\'Jim\',\'Lee\')); end;
2)使用update语句 对可变数组集合列的操纵: declare v_stu_name1 NameListVArray:=NameListVArray(\'Alice\',\'Lee\',\'Nina\'); begin update gradeStusVArray set stuname=v_stu_name1 where cid=\'A011\'; end;
对嵌套表集合列的操纵: declare v_stu_name1 NameListNestedTable :=NameListNestedTable(\'Alice\',\'Lee\',\'Nina\'); begin update gradeStusNestedTable set stuname=v_stu_name1 where cid=\'A011\'; end;
3)使用delete语句 对可变数组集合列的操纵: begin delete from gradeStusVArray where cid=\'A004\'; end;
对嵌套表集合列的操纵: begin delete from gradeStusNestedTable where cid=\'A004\'; end;
4)使用select语句 对可变数组集合列的操纵: set serveroutput on size 1000000;
declare v_stu_name NameListVArray; begin select stuname into v_stu_name from gradeStusVArray where cid=\'A011\'; for v_index in 1..v_stu_name.count loop DBMS_OUTPUT.PUT_LINE(v_stu_name(v_index)); end loop; end;
浏览全部记录: set serveroutput on size 1000000;
declare v_stu_name NameListVArray; Cursor c_gradestu is select cid from gradeStusVArray order by cid; begin for v_rec in c_gradestu loop select stuname into v_stu_name from gradeStusVArray where cid=v_rec.cid; DBMS_OUTPUT.PUT_LINE(v_rec.cid); for v_index in 1..v_stu_name.count loop DBMS_OUTPUT.PUT_LINE(\'stu: \' || v_stu_name(v_index)); end loop; end loop; end;
也可以直接查询表:select * from gradeStusVArray;
对嵌套表集合列的操纵: set serveroutput on size 1000000;
declare v_stu_name NameListNestedTable; begin select stuname into v_stu_name from gradeStusNestedTable where cid=\'A011\'; for v_index in 1..v_stu_name.count loop DBMS_OUTPUT.PUT_LINE(v_stu_name(v_index)); end loop; end;
浏览全部记录: set serveroutput on size 1000000;
declare v_stu_name NameListNestedTable; Cursor c_gradestu is select cid from gradeStusNestedTable order by cid; begin for v_rec in c_gradestu loop select stuname into v_stu_name from gradeStusNestedTable where cid=v_rec.cid; DBMS_OUTPUT.PUT_LINE(v_rec.cid); for v_index in 1..v_stu_name.count loop DBMS_OUTPUT.PUT_LINE(\'stu: \' || v_stu_name(v_index)); end loop; end loop; end;
也可以直接查询表:select * from gradeStusNestedTable;
注意: 嵌套表存在delete方法,可以删除元素,如果存储在数据库表列的集合中发生删除元素或者插入元素操作,原有的集合元素名可能会发生改变,如: set serveroutput on size 1000000;
declare v_stu_name1 NameListNestedTable:=NameListNestedTable(\'Ben\',\'Lee\',\'James\',\'Eric\'); v_stu_name2 NameListNestedTable; v_Index BINARY_INTEGER; begin v_stu_name1.delete(2);
v_Index:= v_stu_name1.FIRST; WHILE v_Index <= v_stu_name1.LAST LOOP DBMS_OUTPUT.PUT(\' \' || v_Index || \': \'); DBMS_OUTPUT.PUT_LINE(v_stu_name1(v_Index)); v_Index := v_stu_name1.NEXT(v_Index); END LOOP; insert into gradeStusNestedTable values(\'D502\',\'English\',v_stu_name1); select stuname into v_stu_name2 from gradeStusNestedTable where cid=\'D502\'; v_Index:= v_stu_name2.FIRST; WHILE v_Index <= v_stu_name2.LAST LOOP DBMS_OUTPUT.PUT(\' \' || v_Index || \': \'); DBMS_OUTPUT.PUT_LINE(v_stu_name2(v_Index)); v_Index := v_stu_name2.NEXT(v_Index); END LOOP; end;
输出为: 1: Ben 3: James 4: Eric 1: Ben 2: James 3: Eric
如果有问题,需要检查是否存在相同课程号记录,必要时还要删除之,如: select * from gradeStusNestedTable; delete from gradeStusNestedTable where cid=\'D502\';
8、5、2、2 操纵单个集合元素 1)使用PL/SQL的操作 如果要处理单个集合元素,必须使用集合变量将集合数据从数据库表中得到,操作完后更新数据库即可,如: 对可变数组集合列的操纵: set serveroutput on size 1000000;
declare v_stu_name NameListVArray; begin select stuname into v_stu_name from gradeStusVArray where cid=\'A011\'; for v_index in 1..v_stu_name.count loop DBMS_OUTPUT.PUT_LINE(v_stu_name(v_index)); end loop; v_stu_name.extend(); v_stu_name(v_stu_name.count):=\'Lisa\'; update gradeStusVArray set stuname=v_stu_name where cid=\'A011\'; select stuname into v_stu_name from gradeStusVArray where cid=\'A011\'; for v_index in 1..v_stu_name.count loop DBMS_OUTPUT.PUT_LINE(v_stu_name(v_index)); end loop; end;
对嵌套表集合列的操纵: set serveroutput on size 1000000;
declare v_stu_name NameListNestedTable; begin select stuname into v_stu_name from gradeStusNestedTable where cid=\'A011\'; for v_index in 1..v_stu_name.count loop DBMS_OUTPUT.PUT_LINE(v_stu_name(v_index)); end loop; v_stu_name.extend(); v_stu_name(v_stu_name.count):=\'Lisa\'; update gradeStusNestedTable set stuname=v_stu_name where cid=\'A011\'; select stuname into v_stu_name from gradeStusNestedTable where cid=\'A011\'; for v_index in 1..v_stu_name.count loop DBMS_OUTPUT.PUT_LINE(v_stu_name(v_index)); end loop; end;
2)SQL表的操作符 直接操纵数据库表中的集合数据列,如查询为: select * from table(select stuname from gradeStusVArray where cid=\'A011\') select * from table(select stuname from gradeStusNestedTable where cid=\'A011\')
注意:默认的嵌套表列名称为COLUMN_VALUE,上述语句等价于: select COLUMN_VALUE from table(select stuname from gradeStusVArray where cid=\'A011\') select COLUMN_VALUE from table(select stuname from gradeStusNestedTable where cid=\'A011\')
另外,使用table函数还可以直接查询集合列信息,如: select cid,title,stuname from gradeStusVArray; select cid,title,COLUMN_VALUE from gradeStusVArray,table(stuname);
select cid,title,stuname from gradeStusNestedTable; select cid,title,COLUMN_VALUE from gradeStusNestedTable,table(stuname);
但是不能在可变数组集合列中直接使用DML语句,嵌套表集合列可以(注意默认的嵌套表列名称),如: insert into table(select stuname from gradeStusNestedTable where cid=\'A011\') values(\'Jack\'); select * from table(select stuname from gradeStusNestedTable where cid=\'A011\')
update table(select stuname from gradeStusNestedTable where cid=\'A011\') set COLUMN_VALUE=\'Jim\' where COLUMN_VALUE=\'Jack\'; select * from table(select stuname from gradeStusNestedTable where cid=\'A011\')
delete from table(select stuname from gradeStusNestedTable where cid=\'A011\') where COLUMN_VALUE=\'Jim\'; select * from table(select stuname from gradeStusNestedTable where cid=\'A011\')
8、6 集合的方法 index-by表,嵌套表、可变数组都有属性,嵌套表、可变数组是对象类型,还具有方法
8、6、1 exists方法 适用于index-by表,嵌套表、可变数组 判断指定名(下标)的元素是否存在,如: set serveroutput on size 1000000;
declare TYPE MyNestedTab IS TABLE OF varchar(8); v_count BINARY_INTEGER :=1; v_list MyNestedTab; begin v_list:=MyNestedTab(\'A\',\'B\',\'D\'); if v_list.exists(1) then DBMS_OUTPUT.PUT_LINE(v_list.count); end if; end; /
注意:即使是null值,也会返回为true,如: set serveroutput on size 1000000;
declare TYPE MyNestedTab IS TABLE OF varchar(8); v_count BINARY_INTEGER :=1; v_list MyNestedTab; begin v_list:=MyNestedTab(null); if v_list.exists(1) then DBMS_OUTPUT.PUT_LINE(v_list.count); end if; end; /
8、6、2 count属性 适用于index-by表,嵌套表、可变数组 返回集合元素数量,如: set serveroutput on size 1000000;
declare TYPE MyNestedTab IS TABLE OF varchar(8); v_count BINARY_INTEGER :=1; v_list MyNestedTab; begin v_list:=MyNestedTab(\'A\',\'B\',\'D\',null); DBMS_OUTPUT.PUT_LINE(v_list.count); end; /
输出为:4
注意:对于index-by表和可变数组,count与last值总是一样,因为可变数组不能删除元素,而嵌套表可以删除元素,所以count属性用途较大
8、6、3 limit属性 适用于嵌套表、可变数组,其中因为嵌套表没有上限,总是返回null 返回集合中目前最大的元素数量,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS VARRAY(10) OF varchar(3) NOT NULL; v_num NumberList; BEGIN v_num:=NumberList(\'3\'); DBMS_OUTPUT.PUT_LINE(v_num.limit); END; /
输出为:10
8、6、4 first和last属性 适用于index-by表,嵌套表、可变数组 返回集合中第一个元素和最后一个元素的索引,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS VARRAY(10) OF varchar(6); v_num NumberList; BEGIN v_num:=NumberList(\'3\',\'F\',\'Jack\',null,\'*\'); DBMS_OUTPUT.PUT_LINE(v_num.first); DBMS_OUTPUT.PUT_LINE(v_num.last); END; /
输出为: 1 5
注意: 1)对于可变数组,first总为1,last总是等于count,如: 2)对于嵌套表,由于可以删除元素,如果存储在数据库表列的集合中发生删除元素或者插入元素操作,原有的集合元素名可能会发生改变,如: set serveroutput on size 1000000;
declare v_stu_name1 NameListNestedTable:=NameListNestedTable(\'Ben\',\'Lee\',\'James\',\'Eric\'); v_stu_name2 NameListNestedTable; v_Index BINARY_INTEGER; begin DBMS_OUTPUT.PUT_LINE(v_stu_name1.first); DBMS_OUTPUT.PUT_LINE(v_stu_name1.last); DBMS_OUTPUT.PUT_LINE(v_stu_name1.count); v_stu_name1.delete(2); DBMS_OUTPUT.PUT_LINE(v_stu_name1.first); DBMS_OUTPUT.PUT_LINE(v_stu_name1.last); DBMS_OUTPUT.PUT_LINE(v_stu_name1.count);
v_Index:= v_stu_name1.FIRST; WHILE v_Index <= v_stu_name1.LAST LOOP DBMS_OUTPUT.PUT(\' \' || v_Index || \': \'); DBMS_OUTPUT.PUT_LINE(v_stu_name1(v_Index)); v_Index := v_stu_name1.NEXT(v_Index); END LOOP; insert into gradeStusNestedTable values(\'E502\',\'English\',v_stu_name1); select stuname into v_stu_name2 from gradeStusNestedTable where cid=\'E502\'; DBMS_OUTPUT.PUT_LINE(v_stu_name2.first); DBMS_OUTPUT.PUT_LINE(v_stu_name2.last); DBMS_OUTPUT.PUT_LINE(v_stu_name2.count);
v_Index:= v_stu_name2.FIRST; WHILE v_Index <= v_stu_name2.LAST LOOP DBMS_OUTPUT.PUT(\' \' || v_Index || \': \'); DBMS_OUTPUT.PUT_LINE(v_stu_name2(v_Index)); v_Index := v_stu_name2.NEXT(v_Index); END LOOP; end;
输出为: 1 4 4 1 4 3 1: Ben 3: James 4: Eric 1 3 3 1: Ben 2: James 3: Eric
如果有问题,需要检查是否存在相同课程号记录,必要时还要删除之,如: select * from gradeStusNestedTable; delete from gradeStusNestedTable where cid=\'E502\';
8、6、5 next和prior属性 适用于index-by表,嵌套表、可变数组 返回集合中上个元素和下个元素的索引,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS VARRAY(10) OF varchar(6); v_num NumberList; v_char varchar(6); BEGIN v_num:=NumberList(\'3\',\'F\',\'Jack\',null,\'*\'); v_char:=v_num(v_num.first); DBMS_OUTPUT.PUT_LINE(v_char); v_char:=v_num(v_num.next(1)); DBMS_OUTPUT.PUT_LINE(v_char); v_char:=v_num(v_num.last); DBMS_OUTPUT.PUT_LINE(v_char); v_char:=v_num(v_num.prior(3)); DBMS_OUTPUT.PUT_LINE(v_char); END; /
输出为: 3 F * F
注意:必须要有整数参数,如果没有元素将返回null值
8、6、6 extend方法 适用于嵌套表、可变数组 返回集合中上个元素和下个元素的索引,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS VARRAY(10) OF varchar(6); v_num NumberList; v_char varchar(6); BEGIN v_num:=NumberList(\'3\'); v_num.extend(); v_num(2):=\'F\'; v_num.extend(2); v_num(3):=\'Jim\'; v_num(4):=\'*\'; v_num.extend(2,3); for v_count in 1..v_num.count loop DBMS_OUTPUT.PUT_LINE(v_num(v_count)); end loop; END; /
输出为: 3 F Jim * Jim Jim
说明 1)不带参数的extend方法只是扩展一个元素,可以通过参数来指定,没有赋值的元素值为null 2)带有两个参数m和n的extend方法表示赋值将第n个现有元素复制m次追加到集合中 3)对于not null约束的集合,只能使用带有两个参数的extend方法 4)对于嵌套表没有扩展的上限,而可变数组只能扩展到大小上限,最大的扩展元素个数是limit-count 5)由于嵌套表可以删除元素,所以扩展的嵌套表元素下标(名)不一定和当前元素总数相同,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS TABLE OF number; v_Numbers NumberList := NumberList(-2, -1, 0, 1, 2);
-- Local procedure to print out a table. -- Note the use of FIRST, LAST, and NEXT. PROCEDURE Print(p_Table IN NumberList) IS v_Index INTEGER; BEGIN v_Index := p_Table.FIRST; WHILE v_Index <= p_Table.LAST LOOP DBMS_OUTPUT.PUT(\'Element \' || v_Index || \': \'); DBMS_OUTPUT.PUT_LINE(p_Table(v_Index)); v_Index := p_Table.NEXT(v_Index); END LOOP; END Print;
BEGIN Print(v_Numbers); v_Numbers.DELETE(3);
Print(v_Numbers); v_Numbers.EXTEND(2, 1);
Print(v_Numbers); DBMS_OUTPUT.PUT_LINE(\'v_Numbers.COUNT = \' || v_Numbers.COUNT); DBMS_OUTPUT.PUT_LINE(\'v_Numbers.LAST = \' || v_Numbers.LAST); END; /
输出为: Element 1: -2 Element 2: -1 Element 3: 0 Element 4: 1 Element 5: 2 Element 1: -2 Element 2: -1 Element 4: 1 Element 5: 2 Element 1: -2 Element 2: -1 Element 4: 1 Element 5: 2 Element 6: -2 Element 7: -2 v_Numbers.COUNT = 6 v_Numbers.LAST = 7
8、6、7 delete方法 适用于index-by表,嵌套表(可变数组大小固定,不能删除指定元素) 删除指定元素
1)删除全部元素,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS TABLE OF varchar(6); v_num NumberList; BEGIN v_num:=NumberList(\'3\',\'F\',\'Jack\',null,\'*\'); DBMS_OUTPUT.PUT_LINE(v_num.count); v_num.delete(); DBMS_OUTPUT.PUT_LINE(v_num.count); END; /
可变数组虽然不能删除指定元素,但是可以删除全部元素,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS VARRAY(10) OF varchar(6); v_num NumberList; BEGIN v_num:=NumberList(\'3\',\'F\',\'Jack\',null,\'*\'); DBMS_OUTPUT.PUT_LINE(v_num.count); v_num.delete(); DBMS_OUTPUT.PUT_LINE(v_num.count); END; /
2)删除指定元素,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS TABLE OF varchar(6); v_num NumberList; v_Index INTEGER; BEGIN v_num:=NumberList(\'3\',\'F\',\'Jack\',null,\'*\'); DBMS_OUTPUT.PUT_LINE(v_num.count); v_num.delete(3); DBMS_OUTPUT.PUT_LINE(v_num.count); v_Index := v_num.FIRST; WHILE v_Index <= v_num.LAST LOOP DBMS_OUTPUT.PUT(\'Element \' || v_Index || \': \'); DBMS_OUTPUT.PUT_LINE(v_num(v_Index)); v_Index := v_num.NEXT(v_Index); END LOOP; /*for v_count in 1..v_num.count loop DBMS_OUTPUT.PUT_LINE(v_num(v_count)); end loop;*/ /*if v_num(3) is null then DBMS_OUTPUT.PUT_LINE(\'null\'); end if;*/ END; /
删除后会影响count,但是对现有元素的下标(名)没有影响,同时被删除元素相应的下标不能再次访问
3)删除指定范围的元素(如果删除的某元素不存在,不会引起错误,而仅仅跳过),如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS TABLE OF varchar(6); v_num NumberList; v_Index INTEGER; BEGIN v_num:=NumberList(\'3\',\'F\',\'Jack\',null,\'*\'); DBMS_OUTPUT.PUT_LINE(v_num.count); v_num.delete(2,4); DBMS_OUTPUT.PUT_LINE(v_num.count); v_Index := v_num.FIRST; WHILE v_Index <= v_num.LAST LOOP DBMS_OUTPUT.PUT(\'Element \' || v_Index || \': \'); DBMS_OUTPUT.PUT_LINE(v_num(v_Index)); v_Index := v_num.NEXT(v_Index); END LOOP; END; /
8、6、8 trim方法 适用于嵌套表、可变数组 删除末端元素,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS VARRAY(10) OF varchar(6); v_num NumberList; BEGIN v_num:=NumberList(\'3\',\'F\',\'Jack\',null,\'*\'); DBMS_OUTPUT.PUT_LINE(v_num.count); v_num.trim(); DBMS_OUTPUT.PUT_LINE(v_num.count); v_num.trim(2); DBMS_OUTPUT.PUT_LINE(v_num.count); END; /
注意:被删除的元素也参加trim运算,如: set serveroutput on size 1000000; DECLARE TYPE NumberList IS TABLE OF varchar(6); v_num NumberList; v_Index INTEGER; BEGIN v_num:=NumberList(\'3\',\'F\',\'Jack\',null,\'*\'); v_num.delete(3); v_Index := v_num.FIRST; WHILE v_Index <= v_num.LAST LOOP DBMS_OUTPUT.PUT(\'Element \' || v_Index || \': \'); DBMS_OUTPUT.PUT_LINE(v_num(v_Index)); v_Index := v_num.NEXT(v_Index); END LOOP; v_num.trim(3); v_Index := v_num.FIRST; WHILE v_Index <= v_num.LAST LOOP DBMS_OUTPUT.PUT(\'Element \' || v_Index || \': \'); DBMS_OUTPUT.PUT_LINE(v_num(v_Index)); v_Index := v_num.NEXT(v_Index); END LOOP; END; /
输出为: Element 1: 3 Element 2: F Element 4: Element 5: * Element 1: 3 Element 2: F
[此贴子已经被作者于2010-12-11 20:03:19编辑过]
|