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