第五章 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中。

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