第五章 PL/SQL集合与记录（2）

八、在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的值与参数类型定义相关，与参数的模式无关。