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

第五章 PL/SQL集合与记录

一、什么是集合

集合就是相同类型的元素的有序合集。它一个通用的概念，其中包含了列表、数组和其他相似的数据类型。每一个元素都有唯一的下标来标识当前元素在集合中的位置。PL/SQL提供了以下几种集合类型：

1. 索引表，也称为关联数组，可以让我们使用数字或字符串作下标来查找元素。(这有点和其他语言中的哈希表相类似。)
2. 嵌套表可以容纳任意个数的元素，使用有序数字作下标。我们可以定义等价的SQL类型，把嵌套表存到数据库中去，并通过SQL语句进行操作。
3. 变长数组能保存固定数量的元素(但可以在运行时改变它的大小)，使用有序数字作为下标。同嵌套表一样，也可以保存到数据库中去，但灵活性不如嵌套表好。

虽热集合是一维的，但我们可以把一个集合作为另外一个集合的元素来建立多维集合。

要在应用程序中使用集合，我们要先定义一个或多个PL/SQL类型，然后声明这些类型的变量。我们可以在过程、函数或包中定义集合类型。还可以把集合作为参数在客户端和存储子程序之间传递数据。

要查找复杂类型的数据，我们可以在集合中存放PL/SQL记录或SQL对象类型。嵌套表和变长数组也可以作为对象类型的属性。

1、理解嵌套表

在数据库中，嵌套表可以被当作单列的数据表来使用。Oracle在往嵌套表中存放数据时是没有特定顺序的。但是，当我们把检索出来的数据存放在PL/SQL变量时，所有行的下标就会从1开始顺序编号。这样，就能像访问数组那样访问每一行数据。

嵌套表有两个重要的地方不同于数组：

1. 数组有固定的上限，而嵌套表是没有上界的。所以，嵌套表的大小是可以动态增长的。如下图：
2. 数 组必须是密集的(dense)，有着连续的下标索引。所以我们不能从数组中删除元素。而对于嵌套表来说，初始化时，它是密集的，但它是允许有间隙的 (sparse)，也就是说它的下标索引可以是不连续的。所以我们可以使用内置过程DELETE从嵌套表中删除元素。这样做会在下标索引上留下空白，但内 置函数NEXT仍能让我们遍历连续地访问所有下标。

2、理解变长数组

VARRAY被称为变长数组。它允许我们使用一个独立的标识来确定整个集合。这种关联能让我们把集合作为一个整体来操作，并很容易地引用其中每一个元素。下面是一个变长数组的例子，如果我们要引用第三个元素的话，就可以使用Grade(3)。

变长数组有一个长度最大值，是在我们定义时指定的。它的索引有一个固定的下界1和一个可扩展的上界。例如变长数组Grades当前上界是7，但我们 可以把它扩展到8、9、10等等。因此，一个变长数组能容纳不定个数的元素，从零(空的时候)到类型定义时所指定的最大长度。

3、理解关联数组(索引表)

关联数组就是键值对的集合，其中键是唯一的，用于确定数组中对应的值。键可以是整数或字符串。第一次使用键来指派一个对应的值就是添加元素，而后续这样的操作就是更新元素。下面的例子演示了如何使用关联数组：

DECLARE
  TYPE population_type IS TABLE OF NUMBER
    INDEX BY VARCHAR2(64);

  country_population     population_type;
  continent_population   population_type;
  howmany                NUMBER;
  which                  VARCHAR2(64);
BEGIN
  country_population('Greenland')       := 100000;
  country_population('Iceland')         := 750000;
  howmany                               := country_population('Greenland');
  continent_population('Australia')     := 30000000;
  continent_population('Antarctica')    := 1000;   -- Creates new entry
  continent_population('Antarctica')    := 1001;   -- Replaces previous value
  which                                 := continent_population.FIRST;
  -- Returns 'Antarctica'
  -- as that comes first alphabetically.
  which                                 := continent_population.LAST;
  -- Returns 'Australia'
  howmany                               :=
                              continent_population(continent_population.LAST);
  -- Returns the value corresponding to the last key, in this
  -- case the population of Australia.
END;
/

关联数组能帮我们存放任意大小的数据集合，快速查找数组中的元素。它像一个简单的SQL表，可以按主键来检索数据。

因为关联数组的作用是存放临时数据，所以不能对它应用像INSERT和SELECT INTO这样的SQL语句。

4、全球化设置对使用VARCHAR2类型作主键的关联数组的影响

如果在使用VARCHAR2作为键的关联数组的会话中改变国家语言或全球化设置，就有可能引起一个运行时异常。例如，在一个会话中改变初始化参数 NLS_COMP或NLS_SORT的值后，再调用NEXT和PRIOR就可能会引起异常。如果我们必须在会话中更改这些设置的话，那么，在重新使用关联 数组的相关操作之前，就必须确保参数值被改回原值。

在用字符串作为关联数组的键的时候，声明时必须使用VARCHAR2、STRING或LONG类型，但使用的时候可以使用其他类型，如NVARCHAR2，VARCHAR2等，甚至是DATE，这些类型值都会被TO_CHAR函数转成VARCHAR2。

但是，在使用其他类型作为键的时候一定要慎重。这里举几个例子：当初始化参数NLS_DATE_FORMAT发生改变时，函数SYSDATE转成字 符串的值就可能发生改变，这样的话，array_element(SYSDATE)的结果就和先前的结果不一样了；两个不同的NVARCHAR2类型值转 成VARCHAR2值之后可能得出的结果是相同的，这样，数组array_element(national_string1)和 array_element(national_string2)可能引用同一个元素。

当我们使用数据库连接(database link)把关联数组作为参数传递给远程数据库时，如果两个数据库的全球化设置不一致，远程数据库会使用自己的字符顺序来调用FIRST和NEXT操作， 即使该顺序与原集合顺序不同。由于字符集的不同，就可能出现在一个数据库中两个不同的键在另一个数据库被当作同一个键处理的情况，这时程序就会收到一个 VALUE_ERROR异常。

二、集合类型的选择

如果我们有用其他语言编写的代码或业务逻辑，通常可以把其中的数组或集合直接转成PL/SQL的集合类型。

1. 其他语言中的数组可以转成PL/SQL中的VARRAY。
2. 其他语言中的集合和包(bags)可以转成PL/SQL中的嵌套表。
3. 哈希表和其他无序查找表(unordered lookup table)可以转成PL/SQL中的关联数组。

当编写原始代码或从头开始设计业务逻辑的时候，我们应该考虑每种类型的优势，然后决定使用哪个类型更加合适。

1、嵌套表与关联数组间的选择

嵌套表和关联数组(原来称为索引表)都使用相似的下标标志，但它们在持久化和参数传递上有些不同的特性。

嵌套表可以保存到数据表字段中，而关联数组不可以。嵌套表适于存放能够被持久化的重要数据。

关联数组适用于存放较小量的数据，每次调用过程或包初始化时在内存中构建出来。它能够保存容量不固定的信息，因为它的长度大小是可变的。关联数组的索引值很灵活，可以是负数，不连续的数字，适当的时候还可以使用字符串代替数字。

PL/SQL能自动地将使用数字作为键的关联数组和主数组(host array)进行转换。集合和数据库服务器间数据传输的最有效的方法就是使用匿名PL/SQL块进行批量绑定数据绑定。

2、嵌套表与变长数组间的选择

在数据个数能够预先确定的情况下，使用变长数组是一个很好的选择。在存入数据库的时候，变长数组会保持它们原有的顺序和下标。

无论在表内(变长数组大小不到4k)还是在表外(变长数组大小超过4k)，每个变长数组都被作为独立的一个对象对待。我们必须对变长数组中的所有元素进行一次性检索或更新。但对于较大量的数据来说，变长数组就不太适用了。

嵌套表是可以有间隙的：我们可以任意地删除元素，不必非得从末端开始。嵌套表数据是存放在系统生成的数据表中，这就使嵌套表适合查询和更新集合中的部分元素。我们不能依赖于元素在嵌套表中的顺序和下标，因为这些顺序和下标在嵌套表存到数据库时并不能被保持。

三、定义集合类型

要使用集合，我们首先要创建集合类型，然后声明该类型的变量。我们可以在任何PL/SQL块、子程序或包的声明部分使用TABLE和VARRAY类型。

集合的作用域和初始化规则同其他类型和变量一样。在一个块或子程序中，当程序进入块或子程序时集合被初始化，退出时销毁。在包中，集合在我们第一次引用包的时候初始化，直至会话终止时才销毁。

• 嵌套表

对于嵌套表，可以使用下面的语法来进行定义：

TYPE type_name IS TABLE OF element_type [NOT NULL];

其中type_name是在集合声明使用的类型标识符，而element_type可以是除了REF CURSOR类型之外的任何PL/SQL类型。对于使用SQL声明的全局嵌套表来说，它的元素类型受到一些额外的限制。以下几种类型是不可以使用的：

1. BINARY_INTEGER, PLS_INTEGER
2. BOOLEAN
3. LONG, LONG RAW
4. NATURAL, NATURALN
5. POSITIVE, POSITIVEN
6. REF CURSOR
7. SIGNTYPE
8. STRING

• 变长数组

对于变长数组类型，可以使用下面的语法进行定义：

TYPE
  type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF 
  element_type [NOT NULL];

type_name和element_type的含义与嵌套表相同。size_limit是正整数，代表数组中最多允许存放元素的个数。在定义VARRAY时，我们必须指定它的长度最大值。下例中，我们定义了一个存储366个DATE类型的VARRAY：

DECLARE 
  TYPE Calendar IS VARRAY(366) OF DATE;

• 关联数组

对于关联数组，可以使用下面的语法进行定义：

TYPE type_name IS TABLE OF element_type [NOT NULL]
  INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];
  INDEX BY key_type;

key_type可以是BINARY_INTEGER或PLS_INTEGER，也可以是VARCHAR2或是它的子类型VARCHAR、 STRING或LONG。在用VARCHAR2做键的时候，我们必须指定VARCHAR2的长度，但这里不包括LONG类型，因为LONG等价于 VARCHAR2(32760)。而RAW、LONG RAW、ROWID、CHAR和CHARACTER都是不允许作为关联数组的键的。在引用一个使用VARCHAR2类型作为键的关联数组中的元素时，我们 还可以使用其他类型，如DATE或TIMESTAMP，因为它们自动地会被TO_CHAR函数转换成VARCHAR2。索引表可以使用不连续的键作下标索 引。如下例中，索引表的下标是7468而不是1：

DECLARE
  TYPE emptabtyp IS TABLE OF emp%ROWTYPE
    INDEX BY BINARY_INTEGER;

  emp_tab   emptabtyp;
BEGIN
  /* Retrieve employee record. */
  SELECT *
    INTO emp_tab(7468)
    FROM emp
   WHERE empno = 7468;
END;

1、定义与PL/SQL集合类型等价的SQL类型

要把嵌套表或变长数组存到数据表中，我们必须用CREATE TYPE来创建SQL类型。SQL类型可以当作数据表的字段或是SQL对象类型的属性来使用。

我们可以在PL/SQL中声明与之等价的类型，或在PL/SQL变量声明时直接使用SQL类型名。

• 嵌套表的例子

下面的SQL*Plus脚本演示了如何在SQL中创建嵌套表，并把它作为对象类型的属性来使用：

CREATE TYPE CourseList AS TABLE OF VARCHAR2(10)   -- define type
/
CREATE TYPE Student AS OBJECT (   -- create object
  id_num INTEGER(4),
  name VARCHAR2(25),
  address VARCHAR2(35),
  status CHAR(2),
  courses CourseList)   -- declare nested table as attribute
/

标识符courses代表整张嵌套表，courses中的每个元素存放一个大学课程的代号，如"Math 1020"。

• 变长数组的例子

下面的脚本创建了能够存储变长数组的数据库字段，其中每个元素包含一个VARCHAR2类型值：

 -- Each project has a 16-character code name.
 -- We will store up to 50 projects at a time in a database column.

CREATE TYPE projectlist AS VARRAY(50) OF VARCHAR2(16);
/

CREATE  TABLE department (   -- create database table
    dept_id NUMBER(2),
    NAME VARCHAR2(15),
    budget NUMBER(11,2),
    -- Each department can have up to 50 projects.
    projects     projectlist)
/

四、声明PL/SQL集合变量

在定义了集合类型之后，我们就可以声明该类型的变量了。在声明中要使用新的类型名称，使用方法跟使用预定义类型(如NUMBER和INTEGER等)声明的方法一样。

• 例一：声明嵌套表、变长数组和关联数组

DECLARE
  TYPE nested_type IS TABLE OF VARCHAR2(20);

  TYPE varray_type IS VARRAY(50) OF INTEGER;

  TYPE associative_array_type IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;

  v1   nested_type;
  v2   varray_type;
  v3   associative_array_type;

• 例二：%TYPE

我们可以利用%TYPE来引用已声明过的集合类型，这样，在集合的定义发生改变时，所有依赖这个集合类型的变量也会相应地改变自己的元素个数和类型，与类型保持一致：

DECLARE
  TYPE platoon IS VARRAY(20) OF soldier;

  p1   platoon;
  -- If we change the number of soldiers in a platoon, p2 will
  -- reflect that change when this block is recompiled.
  p2   p1%TYPE;

• 例三：把嵌套表声明为过程参数

我们可以把集合声明为函数或过程的形式参数。这样，就能把集合从一个存储子程序传递到另一个。下面例子中把嵌套表声明为打包过程的参数：

CREATE PACKAGE personnel AS
  TYPE staff IS TABLE OF employee;
  ...
  PROCEDURE award_bonuses(members IN staff);
END personnel;

想要从包外调用PERSONNEL.AWARD_BONUSES，我们就得声明PERSONNEL.STAFF类型的变量，然后把它作为参数传递进去。我们还可以在函数说明部分指定RETURN的类型为集合：

DECLARE
  TYPE SalesForce IS VARRAY(25) OF Salesperson;
  FUNCTION top_performers (n INTEGER) RETURN SalesForce IS ...

• 例四：用%TYPE和%ROWTYPE指定集合的元素类型

在指定元素的集合类型时，我们可以使用%TYPE和%ROWTYPE。示例如下：

DECLARE
  TYPE EmpList IS TABLE OF emp.ename%TYPE;   -- based on column
  CURSOR c1 IS SELECT * FROM dept;
  TYPE DeptFile IS VARRAY(20) OF c1%ROWTYPE;   -- based on cursor

• 例五：记录类型的变长数组

下面的例子中，我们使用RECORD作为元素的数据类型：

DECLARE
  TYPE anentry IS RECORD(
    term      VARCHAR2(20),
    meaning   VARCHAR2(200)
  );

  TYPE glossary IS VARRAY(250) OF anentry;

• 例六：为集合的元素添加NOT NULL约束

DECLARE
  TYPE EmpList IS TABLE OF emp.empno%TYPE NOT NULL;

五、初始化与引用集合

在我们为嵌套表和变长数组初始化之前，它们都会自动地被设置成空值。所谓的空值指的是集合本身是空，不是针对它所拥有的元素。可以使用系统定义的与集合类型同名的函数来初始化集合。

我们必须显式地调用构造函数为每一个变长数组和嵌套表变量进行初始化操作(对于关联数组来说，是不需要使用构造函数进行初始化的)。

• 例一：嵌套表的构造函数

在下面的例子中，我们为构造函数CourseList()传递多个元素，然后构造函数就能为我们返回包含这些元素的嵌套表：

DECLARE
  TYPE courselist IS TABLE OF VARCHAR2(16);

  my_courses   courselist;
BEGIN
  my_courses    := courselist('Econ 2010',
                              'Acct 3401',
                              'Mgmt 3100'
                             );
END;

由于嵌套表没有声明最大长度，所以我们可以在构造中可以放置任意个数的元素。

• 例二：变长数组的构造函数

DECLARE
  TYPE projectlist IS VARRAY(50) OF VARCHAR2(16);

  accounting_projects   projectlist;
BEGIN
  accounting_projects    := projectlist('Expense Report',
                                        'Outsourcing',
                                        'Auditing'
                                       );
END;

我们不需要初始化整个变长数组，对于一个长度为50的变长数组来说，我们只需传递一部分元素给它的构造函数即可。

• 例三：包含空元素的集合构造函数

如果我们没有对元素使用NOT NULL约束，那么我们就可以把空值传给构造函数：

BEGIN
  my_courses := CourseList('Math 3010', NULL, 'Stat 3202');

• 例四：把声明和构造结合起来

我们可以在声明的时候初始化集合，这是一个很好的编程习惯：

DECLARE
  TYPE courselist IS TABLE OF VARCHAR2(16);

  my_courses   courselist := courselist('Art 1111',
                                        'Hist 3100',
                                        'Engl 2005'
                                       );

• 例五：空的(empty)变长数组构造函数

如果在调用构造函数时不传递任何参数，就会得到一个空的(empty)集合，这里指的是集合内容为空，而不是集合本身为空：

DECLARE
  TYPE clientele IS VARRAY(100) OF customer;

  vips   clientele := clientele();   -- initialize empty varray
BEGIN
  IF vips IS NOT NULL THEN
    -- condition yields TRUE
    ...
  END IF;
END;

这种情况下，我们可以调用EXTEND方法来添加元素。

• 例六：SQL语句中使用嵌套表构造函数

下例中，我们把几个标量值和一个CourseList嵌套表插入到表SOPHOMORES中：

BEGIN
  INSERT INTO sophomores
       VALUES (5035, 'Janet Alvarez', '122 Broad St', 'FT',
               courselist('Econ 2010',
                          'Acct 3401',
                          'Mgmt 3100'
                         ));

• 例七：SQL语句中使用变长数组构造函数

下例中，我们把一行数据插入到表DEPARTMENT。变长数组构造函数ProjectList()为字段PROJECTS提供数据：

BEGIN
  INSERT INTO department
       VALUES (60, 'Security', 750400,
               projectlist('New Badges',
                           'Track Computers',
                           'Check Exits'
                          ));

1、引用集合中的元素

集合的引用包含了集合的名称和用圆括号夹起来的下标索引。下标索引决定了要选取哪个元素。语法如下：

collection_name(subscript)

多数情况下，下标是一个运算结果为整数的表达式，对于使用字符串作键的关联数组来说也可能是一个VARCHAR2类型值。下标索引允许的范围如下：

1. 对于嵌套表，索引值的范围在1至2**31之间。
2. 对于变长数组，索引值的范围在1至最大长度之间，最大长度是在声明时指定的。
3. 对于使用数字作键的关联数组来说，索引值的范围在-2**31至2**31之间。
4. 对于使用字符串作键的关联数组来说，键的长度和可用值的数量要依赖于类型声明时对VARCHAR2的长度限制和数据库字符集。

• 例一：使用下标索引来引用嵌套表中的元素

这里我们演示一下如何引用嵌套表NAMES中的元素：

DECLARE
  TYPE roster IS TABLE OF VARCHAR2(15);

  names   roster := roster('J Hamil',
                           'D Caruso',
                           'R Singh'
                          );
BEGIN
  FOR i IN names.FIRST .. names.LAST LOOP
    IF names(i) = 'J Hamil' THEN
      NULL;
    END IF;
  END LOOP;
END;

• 例二：把嵌套表元素作为参数传递

这个例子中我们在调用子程序时引用了集合中的元素：

DECLARE
  TYPE roster IS TABLE OF VARCHAR2(15);

  names   roster         := roster('J Hamil',
                                   'D Piro',
                                   'R Singh'
                                  );
  i       BINARY_INTEGER := 2;
BEGIN
  verify_name(names(i));   -- call procedure
END;

六、集合的赋值

集合可以用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;

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

集合异常	发生时机
COLLECTION_IS_NULL	调用一个空集合的方法
NO_DATA_FOUND	下标索引指向一个被删除的元素，或是关联数组中不存在的元素
SUBSCRIPT_BEYOND_COUNT	下标索引值超过集合中的元素个数
SUBSCRIPT_OUTSIDE_LIMIT	下标索引超过允许范围之外
VALUE_ERROR	下标索引值为空，或是不能转换成正确的键类型。当键被定义在 PLS_INTEGER的范围内，而下标索引值超过这个范围就可能抛 出这个异常

在某些情况下，如果我们为一个方法传递了一个无效的下标，并不会抛出异常。例如在使用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;