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 ;

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

集合异常 发生时机

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 ;