PL\SQL用户指南与参照5.1.2 转载

六、集合的赋值

集合可以用INSERT、UPDATE、FETCH或SELECT语句来赋值，也可以用赋值语句或调用子程序来赋值。

我们可以使用下面的语法来为某个指定的集合元素进行赋值：

collection_name(subscript)?:=?expression;

其中expression的值和被指定的元素类型必须一致。下面我们来看三个例子。

• 例一：数据的兼容性

例子中的集合与集合之间互相赋值，但必须是两个集合类型相同的才可以，光是元素的类型相同是不够的。

DECLARE ??TYPE?clientele?IS?VARRAY(100)?OF?customer; ??TYPE?vips?IS?VARRAY(100)?OF?customer; ??--?These?first?two?variables?have?the?same?datatype. ??group1???clientele?:=?clientele(...); ??group2???clientele?:=?clientele(...); ??--?This?third?variable?has?a?similar?declaration, ??--?but?is?not?the?same?type. ??group3???vips??????:=?vips(...); BEGIN ??--?Allowed?because?they?have?the?same?datatype ??group2????:=?group1; ??--?Not?allowed?because?they?have?different?datatypes ??group3????:=?group2; END;

• 例二：为嵌套表赋空值

当我们把一个被自动初始化为空的嵌套表或变长数组赋给另外一个嵌套表或变长数组时，被赋值的集合就会被重新初始化，结果也为NULL。

DECLARE ??TYPE?clientele?IS?TABLE?OF?VARCHAR2(64); ??--?This?nested?table?has?some?values. ??group1???clientele?:=?clientele('Customer?1',?'Customer?2'); ??--?This?nested?table?is?not?initialized?("atomically?null"). ??group2???clientele; BEGIN ??--?At?first,?the?test?IF?group1?IS?NULL?yields?FALSE. ??--?Then?we?assign?a?null?nested?table?to?group1. ??group1????:=?group2; ??--?Now?the?test?IF?group1?IS?NULL?yields?TRUE. ??--?We?must?use?another?constructor?to?give?it?some?values. END;

• 例三：集合赋值时可能引起的异常

在下面几种给集合元素赋值的情况下，可能会引起多种异常。

1. 如果下标索引不存在或无法转换成正确的数据类型，PL/SQL就会抛出预定义异常VALUE_ERROR。通常，下标是一个整数。但关联数组的下标也可以是VARCHAR2类型。
2. 如果所给下标索引指向了一个未初始化的元素时，PL/SQL就会抛出SUBSCRIPT_BEYOND_COUNT异常。
3. 如果集合被自动初始化为空值并且程序引用了其中的一个元素，PL/SQL会抛出COLLECTION_IS_NULL异常。

DECLARE ??TYPE?wordlist?IS?TABLE?OF?VARCHAR2(5); ??words???wordlist; BEGIN ??/*?Assume?execution?continues?despite?the?raised?exceptions.?*/ ??--?Raises?COLLECTION_IS_NULL.?We?haven't?used?a?constructor?yet. ??--?This?exception?applies?to?varrays?and?nested?tables,?but?not ??--?associative?arrays?which?don't?need?a?constructor. ??words(1)??????:=?10; ??--?After?using?a?constructor,?we?can?assign?values?to?the?elements. ??words?????????:=?wordlist(10, ????????????????????????????20, ????????????????????????????30 ???????????????????????????); ??--?Any?expression?that?returns?a?VARCHAR2(5)?is?OK. ??words(1)??????:=?'yes'; ??words(2)??????:=?words(1)?||?'no'; ??--?Raises?VALUE_ERROR?because?the?assigned?value?is?too?long. ??words(3)??????:=?'longer?than?5?characters'; ??--?Raises?VALUE_ERROR?because?the?subscript?of?a?nested?table?must ??--?be?an?integer. ??words('B')????:=?'dunno'; ??--?Raises?SUBSCRIPT_BEYOND_COUNT?because?we?only?made?3?elements ??--?in?the?constructor.?To?add?new?ones,?we?must?call?the?EXTEND ??--?method?first. ??words(4)??????:=?'maybe'; END;

七、比较集合

我们可以检查一个集合是不是空，但不能判断两个集合是不是相同。像大于、小于这样的操作都是不允许使用的。

• 例一：检查集合是否为空

嵌套表和变长数组都能被自动初始化为空值，所以它们可以做判空操作：

DECLARE ??TYPE?staff?IS?TABLE?OF?employee; ??members???staff; BEGIN ??--?Condition?yields?TRUE?because?we?haven’t?used?a?constructor. ??IF?members?IS?NULL?THEN?... END;

• 例二：比较两个集合

集合不能直接进行等或不等的比较。例如下面的IF条件表达式就是不允许的。

DECLARE ??TYPE?clientele?IS?TABLE?OF?VARCHAR2(64); ??group1???clientele?:=?clientele('Customer?1',?'Customer?2'); ??group2???clientele?:=?clientele('Customer?1',?'Customer?3'); BEGIN ??--?Equality?test?causes?compilation?error. ??IF?group1?=?group2?THEN ????... ??END?IF; END;

这个约束也适用于隐式的比较。所以，集合不能出现在DISTINCT、GROUP BY或ORDER BY中。

如果我们想对集合进行比较的话，就得自定义等于、小于、大于比较规则。同时还要编写一个或多个函数来检查集合和它们的元素并返回真假值。

八、在SQL语句中使用PL/SQL的集合类型

集合允许我们用PL/SQL来操作复杂的数据类型。我们的程序能计算下标索引值，并在内存中处理指定的元素，然后用SQL语句把结果保存到数据库中。

1、关于嵌套表的例子

• 例一：创建与PL/SQL嵌套表对应的SQL类型

在SQL*Plus中，我们可以创建与PL/SQL嵌套表和变长数组相对应的SQL类型：

SQL>?CREATE?TYPE?CourseList?AS?TABLE?OF?VARCHAR2(64);

我们可以把这些类型当作数据库字段来使用：

SQL>?CREATE?TABLE?department?( 2?name?VARCHAR2(20), 3?director?VARCHAR2(20), 4?office?VARCHAR2(20), 5?courses?CourseList) 6?NESTED?TABLE?courses?STORE?AS?courses_tab;

字段COURSES中的每一个值都是一个嵌套表类型，能够保存系(department)所提供的课程。

• 例二：向数据库中插入嵌套表

现在，我们可以数据表填充了。嵌套表的构造函数为字段COURSES提供了值：

BEGIN ??INSERT?INTO?department ???????VALUES?('English',?'Lynn?Saunders',?'Breakstone?Hall?205', ???????????????courselist?('Expository?Writing', ???????????????????????????'Film?and?Literature', ???????????????????????????'Modern?Science?Fiction', ???????????????????????????'Discursive?Writing', ???????????????????????????'Modern?English?Grammar', ???????????????????????????'Introduction?to?Shakespeare', ???????????????????????????'Modern?Drama', ???????????????????????????'The?Short?Story', ???????????????????????????'The?American?Novel' ??????????????????????????)); END;

• 例三：从数据库中检索嵌套表

我们可以把英语系所提供的所有课程放到PL/SQL嵌套表中：

DECLARE ??english_courses?CourseList; BEGIN ??SELECT?courses? ????INTO?english_courses? FROM?department ???WHERE?name?=?'English'; END;

在PL/SQL中，我们可以循环遍历嵌套表的元素并使用TRIM或EXTEND方法来更新嵌套表中部分或全部的元素。然后，在把更新后的结果保存到数据库中去。

• 例四：用嵌套表更新数据库中

我们可以修改英语系所提供的课程列表：

DECLARE ??new_courses???courselist ????:=?courselist?('Expository?Writing', ???????????????????'Film?and?Literature', ???????????????????'Discursive?Writing', ???????????????????'Modern?English?Grammar', ???????????????????'Realism?and?Naturalism', ???????????????????'Introduction?to?Shakespeare', ???????????????????'Modern?Drama', ???????????????????'The?Short?Story', ???????????????????'The?American?Novel', ???????????????????'20th-Century?Poetry', ???????????????????'Advanced?Workshop?in?Poetry' ??????????????????); BEGIN ??UPDATE?department ?????SET?courses?=?new_courses ???WHERE?NAME?=?'English'; END;

2、变长数组的一些例子

假设我们在SQL*Plus中定义了对象类型Project：

SQL>?CREATE?TYPE?Project?AS?OBJECT?( 2?project_no?NUMBER(2), 3?title?VARCHAR2(35), 4?cost?NUMBER(7,2));

下一步，定义VARRAY类型的ProjectList，用来存放Project对象：

SQL>?CREATE?TYPE?ProjectList?AS?VARRAY(50)?OF?Project;

最后，创建关系表department，其中的一个字段类型为ProjectList：

SQL>?CREATE?TABLE?department?( 2?dept_id?NUMBER(2), 3?name?VARCHAR2(15), 4?budget?NUMBER(11,2), 5?projects?ProjectList);

在字段projects中的每一项都是一个用于存放给定系的项目计划的变长数组。

现在让我们准备插入一些测试数据。注意一下，在下面的例子中，变长数组的构造函数ProjectList()是如何为字段projects提供数据的：

BEGIN ??INSERT?INTO?department ???????VALUES?(30,?'Accounting',?1205700, ???????????????projectlist?(project?(1,?'Design?New?Expense?Report',?3250), ????????????????????????????project?(2,?'Outsource?Payroll',?12350), ????????????????????????????project?(3,?'Evaluate?Merger?Proposal',?2750), ????????????????????????????project?(4,?'Audit?Accounts?Payable',?1425) ???????????????????????????)); ??INSERT?INTO?department ???????VALUES?(50,?'Maintenance',?925300, ???????????????projectlist?(project?(1,?'Repair?Leak?in?Roof',?2850), ????????????????????????????project?(2,?'Install?New?Door?Locks',?1700), ????????????????????????????project?(3,?'Wash?Front?Windows',?975), ????????????????????????????project?(4,?'Repair?Faulty?Wiring',?1350), ????????????????????????????project?(5,?'Winterize?Cooling?System',?1125) ???????????????????????????)); ??INSERT?INTO?department ???????VALUES?(60,?'Security',?750400, ???????????????projectlist?(project?(1,?'Issue?New?Employee?Badges',?13500), ????????????????????????????project?(2,?'Find?Missing?IC?Chips',?2750), ????????????????????????????project?(3,?'Upgrade?Alarm?System',?3350), ????????????????????????????project?(4,?'Inspect?Emergency?Exits',?1900) ???????????????????????????)); END;

现在，让我们对Security系做个更新操作：

DECLARE ??new_projects???projectlist ????:=?projectlist?(project?(1,?'Issue?New?Employee?Badges',?13500), ????????????????????project?(2,?'Develop?New?Patrol?Plan',?1250), ????????????????????project?(3,?'Inspect?Emergency?Exits',?1900), ????????????????????project?(4,?'Upgrade?Alarm?System',?3350), ????????????????????project?(5,?'Analyze?Local?Crime?Stats',?825) ???????????????????); BEGIN ??UPDATE?department ?????SET?projects?=?new_projects ???WHERE?dept_id?=?60; END;

接下来，对Accounting系做一次查询操作，并把结果放到本地变量中去：

DECLARE ??my_projects???projectlist; BEGIN ??SELECT?projects ????INTO?my_projects ????FROM?department ???WHERE?dept_id?=?30; END;

最后，删除记录Accounting：

BEGIN ??DELETE?FROM?department ????????WHERE?dept_id?=?30; END;

3、使用SQL语句操作特定的集合元素

默认情况下，SQL操作会一次性的保存或检索整个集合而不是一个独立的元素。要用SQL语句操作集合中的独立的元素，可以使用TABLE操作符。 TABLE操作符用一个子查询把变长数组或嵌套表的内容提取出来，这样的话，INSERT、UPDATE或DELETE语句就可以作用于嵌套表，而不是整张数据表了。

下面，让我们看看一些具体的操作实例。

• 例一：向嵌套表中插入元素

首先，我们向历史系的嵌套表COURSES插入一条记录：

BEGIN ??--?The?TABLE?operator?makes?the?statement?apply?to?the?nested ??--?table?from?the?'History'?row?of?the?DEPARTMENT?table. ??INSERT?INTO?TABLE?(SELECT?courses ???????????????????????FROM?department ??????????????????????WHERE?NAME?=?'History') ???????VALUES?('Modern?China'); END;

• 例二：更新嵌套表中的元素

然后对嵌套表的学分进行调整：

BEGIN ??UPDATE?TABLE?(SELECT?courses ??????????????????FROM?department ?????????????????WHERE?NAME?=?'Psychology') ?????SET?credits?=?credits?+?adjustment ???WHERE?course_no?IN?(2200,?3540); END;

• 例三：从嵌套表中检索一个元素

下面，我们从历史系检索出一个特定课程名称：

DECLARE ??my_title???VARCHAR2?(64); BEGIN ??--?We?know?that?there?is?one?history?course?with?'Etruscan' ??--?in?the?title.?This?query?retrieves?the?complete?title ??--?from?the?nested?table?of?courses?for?the?History?department. ??SELECT?title ????INTO?my_title ????FROM?TABLE?(SELECT?courses ??????????????????FROM?department ?????????????????WHERE?NAME?=?'History') ???WHERE?NAME?LIKE?'%Etruscan%'; END;

• 例四：从嵌套表中删除元素

最后，我们从英语系中删除所有那些学分为5的课程：

BEGIN ??DELETE??????TABLE?(SELECT?courses ???????????????????????FROM?department ??????????????????????WHERE?NAME?=?'English') ????????WHERE?credits?=?5; END;

• 例五：从变长数组中检索元素

下面例子演示了从变长数组类型的字段projects中检索出公务处第四个项目的名称和费用：

DECLARE ??my_cost????NUMBER?(7,?2); ??my_title???VARCHAR2?(35); BEGIN ??SELECT?COST,?title ????INTO?my_cost,?my_title ????FROM?TABLE?(SELECT?projects ??????????????????FROM?department ?????????????????WHERE?dept_id?=?50) ???WHERE?project_no?=?4; ??... END;

• 例六：对变长数组应用INSERT、UPDATE和DELETE操作

目前，我们还不能在INSERT、UPDATE和DELETE语句中引用变长数组中的元素，必须先检索整个变长数组，使用PL/SQL来添加、删除或更新元素，然后把修改结果重新放回数据库中。

下面的存储过程ADD_PROCEDURE演示了如何按给定的位置向department中插入一个新的project。

CREATE?PROCEDURE?add_project?( ??dept_no?IN?NUMBER, ??new_project?IN?project, ??POSITION?IN?NUMBER )?AS ??my_projects???projectlist; BEGIN ??SELECT????????projects ???????????INTO?my_projects ???????????FROM?department ??????????WHERE?dept_no?=?dept_id ??FOR?UPDATE?OF?projects; ??my_projects.EXTEND;???--?make?room?for?new?project ??/*?Move?varray?elements?forward.?*/ ??FOR?i?IN?REVERSE?POSITION?..?my_projects.LAST?-?1?LOOP ????my_projects?(i?+?1)??:=?my_projects?(i); ??END?LOOP; ??my_projects?(POSITION)??:=?new_project;???--?add?new?project ??UPDATE?department ?????SET?projects?=?my_projects ???WHERE?dept_no?=?dept_id; END?add_project;

下例代码为一个指定的工程更新数据：

CREATE?PROCEDURE?update_project?( ??dept_no?IN?NUMBER, ??proj_no?IN?NUMBER, ??new_title?IN?VARCHAR2?DEFAULT?NULL, ??new_cost?IN?NUMBER?DEFAULT?NULL )?AS ??my_projects???projectlist; BEGIN ??SELECT????????projects ???????????INTO?my_projects ???????????FROM?department ??????????WHERE?dept_no?=?dept_id ??FOR?UPDATE?OF?projects; ??/*?Find?project,?update?it,?then?exit?loop?immediately.?*/ ??FOR?i?IN?my_projects.FIRST?..?my_projects.LAST?LOOP ????IF?my_projects?(i).project_no?=?proj_no?THEN ??????IF?new_title?IS?NOT?NULL?THEN ????????my_projects?(i).title??:=?new_title; ??????END?IF; ??????IF?new_cost?IS?NOT?NULL?THEN ????????my_projects?(i).COST??:=?new_cost; ??????END?IF; ??????EXIT; ????END?IF; ??END?LOOP; ??UPDATE?department ?????SET?projects?=?my_projects ???WHERE?dept_no?=?dept_id; END?update_project;

• 例七：对嵌套表应用INSERT、UPDATE和DELETE操作

为了能对一个PL/SQL嵌套表使用DML操作，我们需要使用TABLE和CAST操作符。这样，我们就可以直接使用SQL标志对嵌套表进行集合操作而不用把更改过的嵌套表保存在数据库中。

CAST的操作数可以是PL/SQL集合变量和SQL集合类型(使用CREATE TYPE语句创建)。CAST可以把PL/SQL集合转成SQL类型的。

下面的例子用来计算修改后的课程列表和原始课程列表的不同点的数量(注意，课程3720的学分从4变成3)：

DECLARE ??revised???????courselist ????:=?courselist?(course?(1002,?'Expository?Writing',?3), ???????????????????course?(2020,?'Film?and?Literature',?4), ???????????????????course?(2810,?'Discursive?Writing',?4), ???????????????????course?(3010,?'Modern?English?Grammar?',?3), ???????????????????course?(3550,?'Realism?and?Naturalism',?4), ???????????????????course?(3720,?'Introduction?to?Shakespeare',?3), ???????????????????course?(3760,?'Modern?Drama',?4), ???????????????????course?(3822,?'The?Short?Story',?4), ???????????????????course?(3870,?'The?American?Novel',?5), ???????????????????course?(4210,?'20th-Century?Poetry',?4), ???????????????????course?(4725,?'Advanced?Workshop?in?Poetry',?5) ??????????????????); ??num_changed???INTEGER; BEGIN ??SELECT?COUNT?(*) ????INTO?num_changed ????FROM?TABLE?(CAST?(revised?AS?courselist))?NEW, ?????????TABLE?(SELECT?courses ??????????????????FROM?department ?????????????????WHERE?NAME?=?'English')?OLD ???WHERE?NEW.course_no?=?OLD.course_no ?????AND?(NEW.title?!=?OLD.title?OR?NEW.credits?!=?OLD.credits); ??DBMS_OUTPUT.put_line?(num_changed); END;

九、使用多级集合

除了标量类型或对象类型集合之外，我们也可以创建集合的集合。例如，我们可以创建元素是变长数组类型的变长数组，元素是嵌套表类型的变长数组等。

在用SQL创建字段类型为嵌套表类型的嵌套表时，Oracle会检查CREATE TABLE语句的语法，看如何定义存储表。

这里有几个例子演示了多级集合的语法。

• 多级VARRAY

DECLARE ??TYPE?t1?IS?VARRAY(10)?OF?INTEGER; ??TYPE?nt1?IS?VARRAY(10)?OF?t1;???--?multilevel?varray?type ??va????t1??????:=?t1(2,?3,?5); ??--?initialize?multilevel?varray ??nva???nt1?????:=?nt1(va,?t1(55,?6,?73),?t1(2,?4),?va); ??i?????INTEGER; ??va1???t1; BEGIN ??--?multilevel?access ??i????????????:=?nva(2)(3);???--?i?will?get?value?73 ??DBMS_OUTPUT.put_line(i); ??--?add?a?new?varray?element?to?nva ??nva.EXTEND; ??nva(5)???????:=?t1(56,?32); ??--?replace?an?inner?varray?element ??nva(4)???????:=?t1(45,?43,?67,?43345); ??--?replace?an?inner?integer?element ??nva(4)(4)????:=?1;???--?replaces?43345?with?1 ??--?add?a?new?element?to?the?4th?varray?element ??--?and?store?integer?89?into?it. ??nva(4).EXTEND; ??nva(4)(5)????:=?89; END;

• 多级嵌套表

DECLARE ??TYPE?tb1?IS?TABLE?OF?VARCHAR2(20); ??TYPE?ntb1?IS?TABLE?OF?tb1;???--?table?of?table?elements ??TYPE?tv1?IS?VARRAY(10)?OF?INTEGER; ??TYPE?ntb2?IS?TABLE?OF?tv1;???--?table?of?varray?elements ??vtb1????tb1??:=?tb1('one',?'three'); ??vntb1???ntb1?:=?ntb1(vtb1); ??vntb2???ntb2?:=?ntb2(tv1(3,?5),?tv1(5,?7,?3));???--?table?of?varray?elements BEGIN ??vntb1.EXTEND; ??vntb1(2)????:=?vntb1(1); ??--?delete?the?first?element?in?vntb1 ??vntb1.DELETE(1); ??--?delete?the?first?string?from?the?second?table?in?the?nested?table ??vntb1(2).DELETE(1); END; /

• 多级关联数组

DECLARE ??TYPE?tb1?IS?TABLE?OF?INTEGER ????INDEX?BY?BINARY_INTEGER; ??--?the?following?is?index-by?table?of?index-by?tables ??TYPE?ntb1?IS?TABLE?OF?tb1 ????INDEX?BY?BINARY_INTEGER; ??TYPE?va1?IS?VARRAY(10)?OF?VARCHAR2(20); ??--?the?following?is?index-by?table?of?varray?elements ??TYPE?ntb2?IS?TABLE?OF?va1 ????INDEX?BY?BINARY_INTEGER; ??v1???va1??:=?va1('hello',?'world'); ??v2???ntb1; ??v3???ntb2; ??v4???tb1; ??v5???tb1;???--?empty?table BEGIN ??v4(1)????????:=?34; ??v4(2)????????:=?46456; ??v4(456)??????:=?343; ??v2(23)???????:=?v4; ??v3(34)???????:=?va1(33,?456,?656,?343); ??--?assign?an?empty?table?to?v2(35)?and?try?again ??v2(35)???????:=?v5; ??v2(35)(2)????:=?78;???--?it?works?now END; /

• 多级集合和批量SQL

CREATE?TYPE?t1?IS?VARRAY(10)?OF?INTEGER; / CREATE?TABLE?tab1?(c1?t1); INSERT?INTO?tab1 ?????VALUES?(t1(2,?3,?5)); INSERT?INTO?tab1 ?????VALUES?(t1(9345,?5634,?432453)); DECLARE ??TYPE?t2?IS?TABLE?OF?t1; ??v2???t2; BEGIN ??SELECT?c1 ??BULK?COLLECT?INTO?v2 ????FROM?tab1; ??DBMS_OUTPUT.put_line(v2.COUNT);???--?prints?2 END; /

十、集合的方法

集合提供了以下几个方法，能帮助我们更加方便维护和使用它：

1. EXISTS
2. COUNT
3. LIMIT
4. FIRST和LAST
5. PRIOR和NEXT
6. EXTEND
7. TRIM
8. DELETE

一个集合方法就是一个内置于集合中并且能够操作集合的函数或过程，可以通过点标志来调用。使用方法如下：

collection_name.method_name[(parameters)]

集合的方法不能在SQL语句中使用。并且，EXTEND和TRIM方法不能用于关联数组。EXISTS，COUNT，LIMIT，FIRST， LAST，PRIOR和NEXT是函数；EXTEND，TRIM和DELETE是过程。EXISTS，PRIOR，NEXT，TRIM，EXTEND和 DELETE对应的参数是集合的下标索引，通常是整数，但对于关联数组来说也可能是字符串。

只有EXISTS能用于空集合，如果在空集合上调用其它方法，PL/SQL就会抛出异常COLLECTION_IS_NULL。

1、检测集合中的元素是否存在(EXISTS)

函数EXISTS(n)在第n个元素存在的情况下会返回TRUE，否则返回FALSE。我们主要使用EXISTS和DELETE来维护嵌套表。其中EXISTS还可以防止引用不存在的元素，避免发生异常。下面的例子中，PL/SQL只在元素i存在的情况下执行赋值语句：

IF?courses.EXISTS(i)?THEN ??courses(i)????:=?new_course; END?IF;

当下标越界时，EXISTS会返回FALSE，而不是抛出SUBSCRIPT_OUTSIDE_LIMIT异常。

2、计算集合中的元素个数(COUNT)

COUNT能够返回集合所包含的元素个数。例如，当下面的变长数组projects中含有25个元素时，IF条件就为TRUE：

IF?projects.COUNT?=?25?THEN?...

COUNT函数很有用，因为集合的当前大小不总是能够被确定。例如，如果我们把一个字段中的值放入嵌套表中，那么嵌套表中会有多少个元素呢？COUNT会给我们答案。

我们可以在任何可以使用整数表达式的地方使用COUNT函数。下例中，我们用COUNT来指定循环的上界值：

FOR?i?IN?1?..?courses.COUNT?LOOP?...

对于变长数组来说，COUNT值与LAST值恒等，但对于嵌套表来说，正常情况下COUNT值会和LAST值相等。但是，当我们从嵌套表中间删除一个元素，COUNT值就会比LAST值小。

计算元素个数时，COUNT会忽略已经被删除的元素。

3、检测集合的最大容量(LIMIT)

因为嵌套表和关联数组都没有上界限制，所以LIMIT总会返回NULL。但对于变长数组来说，LIMIT会返回它所能容纳元素的个数最大值，该值是在变长数组声明时指定的，并可用TRIM和EXTEND方法调整。例如下面的变长数组projects在最大容量是25的时候，IF的条件表达式值为真：

IF?projects.LIMIT?=?25?THEN?...

我们可以在任何允许使用整数表达式的地方使用LIMIT函数。下面的例子中，我们使用LIMIT来决定是否可以为变长数组再添加15个元素：

IF?(projects.COUNT?+?15)?<?projects.LIMIT?THEN?...

4、查找集合中的首末元素(FIRST和LAST)

FIRST和LAST会返回集合中第一个和最后一个元素在集合中的下标索引值。而对于使用VARCHAR2类型作为键的关联数组来说，会分别返回最低和最高的键值；键值的高低顺序是基于字符串中字符的二进制值，但是，如果初始化参数NLS_COMP被设置成ANSI的话，键值的高低顺序就受初始化参数NLS_SORT所影响了。

空集合的FIRST和LAST方法总是返回NULL。只有一个元素的集合，FIRST和LAST会返回相同的索引值。

IF?courses.FIRST?=?courses.LAST?THEN?...???--?only?one?element

下面的例子演示了使用FIRST和LAST函数指定循环范围的下界和上界值：

FOR?i?IN?courses.FIRST?..?courses.LAST?LOOP?...

实际上，我们可以在任何允许使用整数表达式的地方使用FIRST或LAST函数。下例中，我们用FIRST函数来初始化一个循环计数器：

i?:=?courses.FIRST; WHILE?i?IS?NOT?NULL?LOOP?...

对于变长数组来说，FIRST恒等于1，LAST恒等于COUNT；但对嵌套表来说，FIRST正常情况返回1，如果我们把第一个元素删除，那么FIRST的值就要大于1，同样，如果我们从嵌套表的中间删除一个元素，LAST就会比COUNT大。

在遍历元素时，FIRST和LAST都会忽略被删除的元素。

5、循环遍历集合中的元素(PRIOR和NEXT)

PRIOR(n)会返回集合中索引为n的元素的前驱索引值；NEXT(n)会返回集合中索引为n的元素的后继索引值。如果n没有前驱或后继，PRIOR(n)或NEXT(n)就会返回NULL。

对于使用VARCHAR2作为键的关联数组来说，它们会分别返回最低和最高的键值；键值的高低顺序是基于字符串中字符的二进制值，但是，如果初始化参数NLS_COMP被设置成ANSI的话，键值的高低顺序就受初始化参数NLS_SORT所影响了。

这种遍历方法比通过固定的下标索引更加可靠，因为在循环过程中，有些元素可能被插入或删除。特别是关联数组，因为它的下标索引可能是不连续的，有可能是(1,2,4,8,16)或('A','E','I','O','U')这样的形式。

PRIOR和NEXT不会从集合的一端到达集合的另一端。例如，下面的语句把NULL赋给n，因为集合中的第一个元素没有前驱：

n?:=?courses.PRIOR(courses.FIRST);???--?assigns?NULL?to?n

PRIOR是NEXT的逆操作。比如说，存在一个元素i，下面的语句就是用元素i给自身赋值：

projects(i)?:=?projects.PRIOR(projects.NEXT(i));

我们可以使用PRIOR或NEXT来遍历集合。在下面的例子中，我们使用NEXT来遍历一个包含被删除元素的嵌套表：

i????:=?courses.FIRST;???--?get?subscript?of?first?element WHILE?i?IS?NOT?NULL?LOOP ??--?do?something?with?courses(i) ??i????:=?courses.NEXT(i);???--?get?subscript?of?next?element END?LOOP;

在遍历元素时，PRIOR和NEXT都会忽略被删除的元素。

6、扩大集合的容量(EXTEND)

为了扩大嵌套表或变长数组的容量，可以使用EXTEND方法。但该方法不能用于索引表。该方法有三种形式：

1. EXTEND 在集合末端添加一个空元素
2. EXTEND(n) 在集合末端添加n个空元素
3. EXTEND(n,i) 把第i个元素拷贝n份，并添加到集合的末端

例如，下面的语句在嵌套表courses的末端添加了元素1的5个副本：

courses.EXTEND(5,1);

不能使用EXTEND初始化一个空集合。同样，当我们对TABLE或VARRAY添加了NOT NULL约束之后，就不能再使用EXTEND的前两种形式了。

?

EXTEND操作的是集合内部大小，其中也包括被删除的元素。所以，在计算元素个数的时候，EXTEND也会把被删除的元素考虑在内。PL/SQL会为每一个被删除的元素保留一个占位符，以便在适当的时候让我们重新使用。如下例：

DECLARE ??TYPE?courselist?IS?TABLE?OF?VARCHAR2(10); ??courses???courselist; BEGIN ??courses???????:=?courselist('Biol?4412',?'Psyc?3112',?'Anth?3001'); ??courses.DELETE(3);???--?delete?element?3 ??/*?PL/SQL?keeps?a?placeholder?for?element?3.?So,?the ??next?statement?appends?element?4,?not?element?3.?*/ ??courses.EXTEND;???--?append?one?null?element ??/*?Now?element?4?exists,?so?the?next?statement?does ??not?raise?SUBSCRIPT_BEYOND_COUNT.?*/ ??courses(4)????:=?'Engl?2005'; END;

当包含被删除元素时，嵌套表的内部大小就不同于COUNT和LAST返回的值了。举一个例子，假如我们初始化一个长度为5的嵌套表，然后删除第二个和第五个元素，这时的内部长度是5，COUNT返回值是3，LAST返回值是4。EXTEND方法会把所有的被删除的元素都一样对待，无论它是第一个，最后一个还是中间的。

7、缩减集合的空间(TRIM)

TRIM有两种形式：

1. TRIM 从集合末端删除一个元素
2. TRIM(n) 从集合末端删除n个元素

例如，下面的表达式从嵌套表courses中删除最后三个元素：

courses.TRIM(3);

如果n值过大的话，TRIM(n)就会抛出SUBSCRIPT_BEYOND_COUNT异常。

同EXTEND相似，TRIM也不会忽略被删除的元素。看一下下面的例子：

DECLARE ??TYPE?courselist?IS?TABLE?OF?VARCHAR2(10); ??courses???courselist; BEGIN ??courses????:=?courselist('Biol?4412',?'Psyc?3112',?'Anth?3001'); ??courses.DELETE(courses.LAST);???--?delete?element?3 ??/*?At?this?point,?COUNT?equals?2,?the?number?of?valid ??elements?remaining.?So,?you?might?expect?the?next ??statement?to?empty?the?nested?table?by?trimming ??elements?1?and?2.?Instead,?it?trims?valid?element?2 ??and?deleted?element?3?because?TRIM?includes?deleted ??elements?in?its?tally.?*/ ??courses.TRIM(courses.COUNT); ??DBMS_OUTPUT.put_line(courses(1));???--?prints?'Biol?4412' END;

一般的，不要同时使用TRIM和DELETE方法。最好是把嵌套表当作定长数组，只对它使用DELETE方法，或是把它当作栈，只对它使用TRIM和EXTEND方法。PL/SQL对TRIM掉的元素不再保留占位符。这样我们就不能简单地为被TRIM掉的元素赋值了。

8、删除集合中的元素(DELETE)

DELETE方法有三种形式：

1. DELETE 删除集合中所有元素
2. DELETE(n) 从以数字作主键的关联数组或者嵌套表中删除第n个元素。如果关联数组有一个字符串键，对应该键值的元素就会被删除。如果n为空，DELETE(n)不会做任何事情。
3. DELETE(m,n) 从关联数组或嵌套表中，把索引范围m到n的所有元素删除。如果m值大于n或是m和n中有一个为空，那么DELETE(m,n)就不做任何事。

例如：

BEGIN ??courses.DELETE(2);???--?deletes?element?2 ??courses.DELETE(7,?7);???--?deletes?element?7 ??courses.DELETE(6,?3);???--?does?nothing ??courses.DELETE(3,?6);???--?deletes?elements?3?through?6 ??projects.DELETE;???--?deletes?all?elements ??nicknames.DELETE('Chip');???--?deletes?element?denoted?by?this?key ??nicknames.DELETE('Buffy',?'Fluffy'); ??--?deletes?elements?with?keys ??--?in?this?alphabetic?range END;

变长数组是密集的，我们不能从中删除任何一个元素。如果被删除的元素不存在，DELETE只是简单地忽略它，并不抛出异常。PL/SQL会为被删除的元素保留一个占位符，以便我们可以重新为被删除的元素赋值。

DELETE方法能让我们维护有间隙的嵌套表。下面的例子中，我们把嵌套表prospects的内容放到临时表中，然后从中删除一部分元素后，再重新把它存入数据库中：

DECLARE ??my_prospects???prospectlist; ??revenue????????NUMBER; BEGIN ??SELECT?prospects? ????INTO?my_prospects? ????FROM?customers? ???WHERE?... ??FOR?i?IN?my_prospects.FIRST?..?my_prospects.LAST?LOOP ????estimate_revenue(my_prospects(i),?revenue);???--?call?procedure ????IF?revenue?<?25000?THEN ??????my_prospects.DELETE(i); ????END?IF; ??END?LOOP; ??UPDATE?customers? ?????SET?prospects?=?my_prospects? ???WHERE?... END;

分配给嵌套表的内存是动态的，删除元素时内存会被释放。

9、使用集合类型参数的方法

在子程序中，我们可以对集合类型的参数直接调用它的内置方法，如下例：

CREATE?PACKAGE?personnel?AS ??TYPE?staff?IS?TABLE?OF?employee; ??... ??PROCEDURE?award_bonuses(members?IN?staff); END?personnel; CREATE?PACKAGE?BODY?personnel?AS ??PROCEDURE?award_bonuses(members?IN?staff)?IS ????... ??BEGIN ????... ????IF?members.COUNT?>?10?THEN???--?apply?method ??????... ????END?IF; ??END; END?personnel;

注意：对于变长数组参数来说，LIMIT的值与参数类型定义相关，与参数的模式无关。

十一、避免集合异常

大多情况下，如果我们引用了一个集合中不存在的元素，PL/SQL就会抛出一个预定义异常。例如：

DECLARE ??TYPE?numlist?IS?TABLE?OF?NUMBER; ??nums???numlist;???--?atomically?null BEGIN ??/*?Assume?execution?continues?despite?the?raised?exceptions.?*/ ??nums(1)???????:=?1;???--?raises?COLLECTION_IS_NULL?(1) ??nums??????????:=?numlist(1,?2);???--?initialize?table ??nums(NULL)????:=?3;???--?raises?VALUE_ERROR?(2) ??nums(0)???????:=?3;???--?raises?SUBSCRIPT_OUTSIDE_LIMIT?(3) ??nums(3)???????:=?3;???--?raises?SUBSCRIPT_BEYOND_COUNT?(4) ??nums.DELETE(1);???--?delete?element?1 ??IF?nums(1)?=?1?THEN? ????...?--?raises?NO_DATA_FOUND?(5) END;

第一句，嵌套表是空的；第二句，下标为空；三四句，下标超出合法范围之外；第五句，下标指向了一个被删除的元素。下表是一些异常情况的说明：

在某些情况下，如果我们为一个方法传递了一个无效的下标，并不会抛出异常。例如在使用DELETE方法的时候，我们向它传递NULL，它只是什么都没做而已。同样，用新值替换被删除的元素也不会引起NO_DATA_FOUND异常，如下例：

DECLARE ??TYPE?numlist?IS?TABLE?OF?NUMBER; ??nums???numlist?:=?numlist(10,?20,?30);???--?initialize?table BEGIN ??nums.DELETE(-1);???--?does?not?raise?SUBSCRIPT_OUTSIDE_LIMIT ??nums.DELETE(3);???--?delete?3rd?element ??DBMS_OUTPUT.put_line(nums.COUNT);???--?prints?2 ??nums(3)????:=?30;???--?allowed;?does?not?raise?NO_DATA_FOUND ??DBMS_OUTPUT.put_line(nums.COUNT);???--?prints?3 END;

打包集合类型和本地集合类型总是不兼容的。假设我们想调用下面的打包过程：

CREATE?PACKAGE?pkg1?AS ??TYPE?NumList?IS?VARRAY(25)?OF?NUMBER(4); ?? ??PROCEDURE?delete_emps?(emp_list?NumList); END?pkg1; CREATE?PACKAGE?BODY?pkg1?AS ??PROCEDURE?delete_emps?(emp_list?NumList)?IS?... ????... END?pkg1;

在运行下面PL/SQL块时，第二个过程调用会因参数的数量或类型错误(wrong number or types of arguments error)而执行失败。这是因为打包VARRAY和本地VARRAY类型不兼容，虽然它们的定义形式都是一样的：

DECLARE ??TYPE?numlist?IS?VARRAY(25)?OF?NUMBER(4); ??emps????pkg1.numlist?:=?pkg1.numlist(7369,?7499); ??emps2???numlist??????:=?numlist(7521,?7566); BEGIN ??pkg1.delete_emps(emps); ??pkg1.delete_emps(emps2);???--?causes?a?compilation?error END;