第五章 PL/SQL集合与记录

PL/SQL集合与记录

一、什么是集合

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

嵌套表可以容纳任意个数的元素，使用有序数字作下标。我们可以定义等价的SQL类型，把嵌套表存到数据库中去，并通过SQL语句进行操作。

类型名称	别名/别称	核心特点（下标、元素数量、存储性）	与其他类型的对比
嵌套表	Nested Table	1. 下标：有序数字（从1开始，可非连续） 2. 元素数量：任意个数（动态增减，无固定上限） 3. 存储性：可定义为SQL类型，存入数据库表中，支持SQL语句直接操作（如查询、插入元素）	灵活性高，适合存储“一个实体对应多个同类型属性”（如一个客户的多个电话）。

变长数组能保存固定数量的元素(但可以在运行时改变它的大小)，使用有序数字作为下标。同嵌套表一样，也可以保存到数据库中去，但灵活性不如嵌套表好。

类型名称	别名/别称	核心特点（下标、元素数量、存储性）	与其他类型的对比
变长数组	Varray	1. 下标：有序数字（从1开始，连续） 2. 元素数量：声明时需指定最大容量（运行时可动态调整大小，但不能超过最大容量） 3. 存储性：可存入数据库表，但灵活性低于嵌套表	适合元素数量有明确上限的场景（如一个订单最多包含10个商品），因容量固定，扩展性不如嵌套表。

索引表，也称为关联数组，可以让我们使用数字或字符串作下标来查找元素。(这有点和其他语言中的哈希表相类似。)

类型名称	别名/别称	核心特点（下标、元素数量、存储性）	与其他类型的对比
索引表	关联数组	1. 下标：支持数字或字符串（类似哈希表的“键”，可快速查找元素） 2. 元素数量：动态增减，无固定上限 3. 存储性：仅能在PL/SQL中作为变量使用，不能存入数据库表	类似其他语言的“哈希表”，适合内存中临时存储数据（如缓存键值对：用员工ID作数字下标，或用员工姓名作字符串下标，快速定位信息）。

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

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

1. 理解嵌套表

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

场景1：内存中的嵌套表变量（类似“动态数组”）假设定义了一个存储“电话号码”的嵌套表类型 phone_list，并声明变量 my_phones 存储某个人的3个联系方式，结构如下：

-- 定义嵌套表类型（存储电话号码）
TYPE phone_list IS TABLE OF VARCHAR2(20);

-- 声明一个嵌套表变量，存储某人的电话，等号右边的 phone_list('138...', '010...', '186...') 并不是 “类型名”，而是 PL/SQL 自动生成的构造函数（Constructor） 
my_phones phone_list := phone_list('13800138000', '010-12345678', '18600186000');

# 嵌套表类型定义：phone_list（元素类型：VARCHAR2(20)）
┌─────────────────────────────────────────────────┐
│ TYPE phone_list IS TABLE OF VARCHAR2(20);       │
└─────────────────────────────────────────────────┘
                    ↓
# 嵌套表变量：my_phones（存储3个电话号码，通过“数字下标”访问）
┌─────────────────────────────────────────────────┐
│ 变量名：my_phones  (类型：phone_list)           │
│ ┌───────────────┐  ┌───────────────┐            │
│ │ 下标=1        │  │ 元素值        │            │
│ │               │→ │ '13800138000' │            │
│ └───────────────┘  └───────────────┘            │
│                                                  │
│ ┌───────────────┐  ┌───────────────┐            │
│ │ 下标=2        │  │ 元素值        │            │
│ │               │→ │ '010-12345678' │            │
│ └───────────────┘  └───────────────┘            │
│                                                  │
│ ┌───────────────┐  ┌───────────────┐            │
│ │ 下标=3        │  │ 元素值        │            │
│ │               │→ │ '18600186000' │            │
│ └───────────────┘  └───────────────┘            │
└─────────────────────────────────────────────────┘
# 特点：下标从1开始，元素有序；可动态增删（比如删下标2，下标会变成1、3）

场景2：数据库中的主表-嵌套表（“表中嵌套子表”）假设创建了“客户表 customers”，其中 contact_phones 列是嵌套表类型（存储客户的多个电话），并指定嵌套表的实际存储表为 phones_table，结构如下：

-- 1. 先在SQL层定义嵌套表类型（必须创建为数据库对象，才能存到表中）
CREATE TYPE phone_list IS TABLE OF VARCHAR2(20);

-- 2. 创建主表，其中contact_phones列是嵌套表类型
CREATE TABLE customers (
    id NUMBER,
    name VARCHAR2(50),
    contact_phones phone_list  -- 嵌套表列，存储多个电话
) NESTED TABLE contact_phones STORE AS phones_table;  -- 指定嵌套表的存储表

整体关系（主表 + 嵌套表存储表）

┌─────────────────────────────────────────────────────────────────────┐
│ 主表：customers（存储客户基本信息）                                  │
│ ┌──────────┬──────────┬─────────────────────────────────────────┐   │
│ │ id       │ name     │ contact_phones（嵌套表列，指向子表）    │   │
│ │ (客户ID) │ (客户名) │ （类型：phone_list）                   │   │
│ ├──────────┼──────────┼─────────────────────────────────────────┤   │
│ │ 1        │ 张三     │ → 子表1（存储张三的2个电话）            │   │
│ ├──────────┼──────────┼─────────────────────────────────────────┤   │
│ │ 2        │ 李四     │ → 子表2（存储李四的3个电话）            │   │
│ └──────────┴──────────┴─────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────┘
               ↓ （通过内部关联字段，主表每行对应嵌套表存储表的一组数据）
┌─────────────────────────────────────────────────────────────────────┐
│ 嵌套表存储表：phones_table（Oracle自动创建，存储所有客户的电话）     │
│ ┌────────────────┬────────────────┬────────────────┐                │
│ │ 主表关联ID     │ 嵌套表下标     │ phone（电话值）│                │
│ │ （隐式字段）   │ （隐式字段）   │                │                │
│ ├────────────────┼────────────────┼────────────────┤                │
│ │ 1              │ 1              │ '13800138000'  │  → 张三的电话  │
│ │ 1              │ 2              │ '010-12345678'  │                │
│ ├────────────────┼────────────────┼────────────────┤                │
│ │ 2              │ 1              │ '13900139000'  │  → 李四的电话  │
│ │ 2              │ 2              │ '18900189000'  │                │
│ │ 2              │ 3              │ '021-87654321'  │                │
│ └────────────────┴────────────────┴────────────────┘                │
└─────────────────────────────────────────────────────────────────────┘
# 关键：主表的“嵌套表列”不直接存数据，而是通过“主表关联ID”指向存储表中的对应组数据；
#      存储表的“嵌套表下标”对应内存中嵌套表的下标，保证元素顺序。

单条客户数据的具象化（以“张三”为例）

┌─────────────────────────────────────────────────┐
│ 客户：张三（主表 customers 中 id=1 的行）       │
│ ┌──────────┐  ┌──────────┐  ┌───────────────┐  │
│ │ id=1     │  │ name=张三│  │ contact_phones│  │
│ └──────────┘  └──────────┘  │ （嵌套表）    │  │
│                             │ ┌───────────┐ │  │
│                             │ │ 下标=1    │ │  │
│                             │ │ 13800138000│ │  │
│                             │ └───────────┘ │  │
│                             │                │  │
│                             │ ┌───────────┐ │  │
│                             │ │ 下标=2    │ │  │
│                             │ │ 010-12345678│ │  │
│                             │ └───────────┘ │  │
│                             └───────────────┘  │
└─────────────────────────────────────────────────┘
         ↓ （实际数据存在存储表中，逻辑上属于张三）
┌─────────────────────────────────────────────────┐
│ phones_table 中属于张三的行                     │
│ ┌─────────────┐  ┌─────────────┐  ┌─────────┐  │
│ │ 主表关联ID=1 │  │ 下标=1      │  │ 138...  │  │
│ └─────────────┘  └─────────────┘  └─────────┘  │
│                                                │
│ ┌─────────────┐  ┌─────────────┐  ┌─────────┐  │
│ │ 主表关联ID=1 │  │ 下标=2      │  │ 010...  │  │
│ └─────────────┘  └─────────────┘  └─────────┘  │
└─────────────────────────────────────────────────┘

数组有固定的上限，而嵌套表是没有上界的。所以，嵌套表的大小是可以动态增长的。如下图：

-- 1. 定义嵌套表类型
CREATE OR REPLACE TYPE num_table_type IS TABLE OF NUMBER;
/

DECLARE
    -- 2. 声明嵌套表变量并初始化
    nt_num  num_table_type := num_table_type();  -- 初始化空嵌套表
    i       NUMBER;
BEGIN
    -- 3. 动态扩展嵌套表并添加元素（下标从1开始）
    nt_num.EXTEND(5);  -- 扩展5个元素位置
    nt_num(1) := 10;
    nt_num(2) := 20;
    nt_num(3) := 30;
    nt_num(4) := 40;
    nt_num(5) := 50;
    
    DBMS_OUTPUT.PUT_LINE('初始嵌套表元素:');
    FOR i IN nt_num.FIRST .. nt_num.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('下标 ' || i || ': ' || nt_num(i));
    END LOOP;
    
    -- 4. 从嵌套表中删除元素（产生间隙，变为稀疏）
    nt_num.DELETE(3);  -- 删除下标为3的元素
    nt_num.DELETE(5);  -- 删除下标为5的元素
    
    DBMS_OUTPUT.PUT_LINE(CHR(10) || '删除元素后（存在间隙）:');
    i := nt_num.FIRST;
    WHILE i IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('下标 ' || i || ': ' || nt_num(i));
        i := nt_num.NEXT(i);  -- 使用NEXT函数遍历连续元素
    END LOOP;
    
    -- 5. 动态添加新元素（嵌套表大小可增长）
    -- 作用是为嵌套表动态增加一个空的元素位置， PL/SQL 对嵌套表的语法限制：必须先分配位置，才能赋值
    nt_num.EXTEND;
    nt_num(6) := 60;  -- 新元素下标为6（延续最大下标）
    
    DBMS_OUTPUT.PUT_LINE(CHR(10) || '添加新元素后:');
    i := nt_num.FIRST;
    WHILE i IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE('下标 ' || i || ': ' || nt_num(i));
        i := nt_num.NEXT(i);
    END LOOP;
END;
/

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

内存中：像“带有序下标的列表”，每个下标对应一个元素，可直接通过下标访问/修改；
数据库中：像“主表每行绑了一个专属子表”，子表的所有数据统一存在Oracle自动创建的“存储表”中，通过隐式字段与主表关联，逻辑上属于主表的一行，但物理上分开存储；
“嵌套”的本质：不是“表里面套表的物理结构”，而是“逻辑上一个实体（如客户）对应多个同类型属性（如多个电话），并通过嵌套表类型将这些属性绑定到实体上”。

2. 理解变长数组

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

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

-- 1. 定义变长数组类型：指定元素类型为NUMBER，最大长度为10（最多存10个元素）
CREATE OR REPLACE TYPE grade_varray_type IS VARRAY(10) OF NUMBER;
/

DECLARE
  -- 2. 声明变长数组变量，并初始化（初始可空，或直接指定元素）
  grades  grade_varray_type := grade_varray_type();  -- 初始为空数组（长度0）
  i       NUMBER;
BEGIN
  -- 3. 动态扩展数组（上界从0增长到5，未超过最大长度10）
  -- 扩展并赋值：索引从1开始，依次添加5个元素
  grades.EXTEND(5);  -- 扩展5个位置，当前上界=5
  grades(1) := 85;   -- 第一个元素
  grades(2) := 92;   -- 第二个元素
  grades(3) := 78;   -- 第三个元素（对应文字中的Grade(3)）
  grades(4) := 90;
  grades(5) := 88;
  
  DBMS_OUTPUT.PUT_LINE('当前数组元素（上界=5）：');
  FOR i IN 1 .. grades.COUNT LOOP  -- COUNT获取当前元素个数（即上界）
    DBMS_OUTPUT.PUT_LINE('grades(' || i || ') = ' || grades(i));
  END LOOP;
  
  -- 4. 继续扩展（上界从5增长到7，仍未超过最大长度10）
  grades.EXTEND(2);  -- 再扩展2个位置，当前上界=7
  grades(6) := 76;
  grades(7) := 95;
  
  DBMS_OUTPUT.PUT_LINE(CHR(10) || '扩展后元素（上界=7）：');
  FOR i IN 1 .. grades.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('grades(' || i || ') = ' || grades(i));
  END LOOP;
  
  -- 5. 尝试扩展到最大长度10（允许）
  grades.EXTEND(3);  -- 再扩展3个位置，当前上界=10（达到最大值）
  grades(8) := 82;
  grades(9) := 89;
  grades(10) := 91;
  
  DBMS_OUTPUT.PUT_LINE(CHR(10) || '达到最大长度10时的元素：');
  FOR i IN 1 .. grades.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('grades(' || i || ') = ' || grades(i));
  END LOOP;
  
  -- 6. 若尝试扩展超过最大长度（如扩展到11），会报错：
  -- grades.EXTEND(1);  -- 执行此句会触发"ORA-06532: 下标超出范围"
END;
/

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;       -- 南极洲人口（创建新条目）
    continent_population('Antarctica') := 1001;       -- 南极洲人口（替换已有值）
    
    -- 获取大陆人口数组的第一个索引键（按字母顺序排序）
    which := continent_population.FIRST;  -- 返回'Antarctica'
    
    -- 获取大陆人口数组的最后一个索引键（按字母顺序排序）
    which := continent_population.LAST;   -- 返回'Australia'
    
    -- 获取最后一个索引键对应的人口数量
    howmany := continent_population(continent_population.LAST);  -- 返回澳大利亚人口数
END;
/

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

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

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

关联数组的“键”是字符串，但字符串的“表现形式”受全球化设置影响

关联数组（索引表）用字符串当键时，本质是通过 “字符串的字面内容” 定位元素（比如键是'张三'，就找对应'张三'的元素）。但Oracle的“全球化设置”（如NLS_COMP排序规则、NLS_DATE_FORMAT日期格式、字符集）会改变：

同一个值（如SYSDATE、NVARCHAR2字符串）转成VARCHAR2（键的最终类型）后的结果；
字符串的排序/比较规则（如NEXT/PRIOR函数遍历键时的顺序）。

一旦这些设置变了，原本“能匹配的键”可能匹配不上，原本“不同的键”可能被当成同一个，最终导致异常。

场景1：改了NLS_COMP或NLS_SORT（字符串排序/比较规则）→ 遍历键时异常

前提：关联数组的键是字符串（如'A'、'B'、'a'），用NEXT（下一个键）、PRIOR（上一个键）遍历数组。
问题：NLS_COMP控制字符串比较方式（如是否区分大小写），NLS_SORT控制排序规则（如按字母顺序还是拼音顺序）。
例：原设置NLS_SORT=BINARY（区分大小写，'A' < 'a'），遍历顺序是'A'→'B'→'a'；若改成NLS_SORT=BINARY_CI（不区分大小写，'A'='a'），再用NEXT遍历，Oracle无法识别原本的顺序，会抛出运行时异常（不知道下一个键该找谁）。
结论：改了这两个参数后，关联数组的“键顺序”被打乱，遍历操作会出错。

场景2：用非VARCHAR2类型当键（如DATE、NVARCHAR2）→ 转成VARCHAR2后键“变味”

“声明键为VARCHAR2，但使用时可传DATE、NVARCHAR2等，会自动用TO_CHAR转成VARCHAR2”——但这个“自动转换”会受全球化设置影响，导致键不匹配。

举两个的例子，更直观：

例子1：DATE类型转字符串（受NLS_DATE_FORMAT影响）
原设置NLS_DATE_FORMAT='YYYY-MM-DD'，SYSDATE（如2024-05-20）转成'2024-05-20'，关联数组中存的键是'2024-05-20'；
若改成NLS_DATE_FORMAT='MM/DD/YYYY'，SYSDATE会转成'05/20/2024'——此时再用array_element(SYSDATE)，找的是'05/20/2024'，但原键是'2024-05-20'，自然找不到，或找错元素。
例子2：NVARCHAR2转VARCHAR2（字符集不兼容）
NVARCHAR2是Unicode字符集，VARCHAR2是数据库默认字符集（如GBK）。若两个不同的NVARCHAR2字符串（如特殊符号'①'和'㈠'），转成VARCHAR2时因字符集不支持，可能都转成'?'——此时array_element('①')和array_element('㈠')会指向同一个键'?'，导致数据混乱。

跨数据库传递关联数组（数据库链接）→ 两端设置不一致导致键错乱

前提：通过database link把关联数组（字符串键）传给远程数据库，本地和远程的全球化设置不同（如本地字符集是UTF8，远程是GBK）。
问题1：遍历顺序乱了
本地用NLS_SORT=UTF8排序键，远程用NLS_SORT=GBK排序——远程调用FIRST（第一个键）、NEXT时，得到的顺序和本地完全不同，无法正确遍历数组。
问题2：键被误判为相同
本地的两个不同键（如'张三'在UTF8中是两个字节，在GBK中可能被转成其他字符），到远程后因字符集转换，被当成同一个键——Oracle会抛出VALUE_ERROR异常（无法区分两个键，不知道该操作哪个元素）。

给开发者的2个核心建议

尽量用固定格式的字符串当键：如果必须用DATE当键，不要依赖自动TO_CHAR，而是手动指定格式（如TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')），避免受NLS_DATE_FORMAT影响；
避免在会话中改全球化参数：如果改了NLS_COMP、NLS_SORT等，必须在操作关联数组前改回原值；跨数据库传递时，确保两端的全球化设置（字符集、排序规则）一致。

二、集合类型的选择

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

其他语言中的数组可以转成PL/SQL中的VARRAY。

其他语言中的集合和包(bags)可以转成PL/SQL中的嵌套表。

哈希表和其他无序查找表(unordered lookup table)可以转成PL/SQL中的关联数组。

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

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

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

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

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

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

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

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

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

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

三、定义集合类型

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

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

嵌套表

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

1
2

-- 定义嵌套表类型
TYPE type_name IS TABLE OF element_type [NOT NULL];

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

-- 对于使用SQL声明的全局嵌套表，以下类型不能作为元素类型：
BINARY_INTEGER, PLS_INTEGER  -- 整数类型
BOOLEAN                      -- 布尔类型
LONG, LONG RAW               -- 长字符串和原始数据类型
NATURAL, NATURALN            -- 自然数类型
POSITIVE, POSITIVEN          -- 正整数类型
REF CURSOR                   -- 游标引用类型
SIGNTYPE                     -- 符号类型
STRING                       -- 字符串类型

变长数组

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

1
2

-- 定义可变数组(VARRAY)类型
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];

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

1
2
3

DECLARE
    -- 定义一个可变数组类型Calendar，最多可存储366个DATE类型元素
    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)];

-- 或简化表示为：
TYPE type_name IS TABLE OF element_type [NOT NULL]
    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
    -- 定义关联数组类型：存储emp表的行记录，以BINARY_INTEGER为索引
    TYPE emptabtyp IS TABLE OF emp%ROWTYPE
        INDEX BY BINARY_INTEGER;
    
    -- 声明关联数组变量
    emp_tab  emptabtyp;
BEGIN
    /* 检索员工记录 */
    SELECT *
      INTO emp_tab(7468)  -- 将查询结果存储到索引为7468的数组元素中
      FROM emp
     WHERE empno = 7468;  -- 查询员工编号为7468的记录
END;
/

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

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

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

嵌套表的例子

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

-- 定义嵌套表类型：存储课程代码（最大长度10的字符串）
CREATE TYPE CourseList AS TABLE OF VARCHAR2(10);
/

-- 创建对象类型 Student，包含嵌套表属性
CREATE TYPE Student AS OBJECT (
    id_num    INTEGER(4),       -- 学生编号
    name      VARCHAR2(25),     -- 学生姓名
    address   VARCHAR2(35),     -- 学生地址
    status    CHAR(2),          -- 学生状态（如：' freshman', 'GR'等）
    courses   CourseList        -- 嵌套表属性：存储学生所选课程
);
/

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

变长数组的例子

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

-- 注释：每个项目有一个16字符的代号
-- 注释：我们将在数据库列中一次存储最多50个项目
-- 1. 定义可变数组类型：存储项目代号（最多50个元素，每个元素为16字符字符串）
CREATE TYPE projectlist AS VARRAY(50) OF VARCHAR2(16);
/

-- 2. 创建部门表，包含可变数组类型的列
CREATE TABLE department (
    dept_id    NUMBER(2),          -- 部门编号（2位数字）
    NAME       VARCHAR2(15),       -- 部门名称（最多15字符）
    budget     NUMBER(11,2),       -- 部门预算（总长度11位，其中小数位2位）
    -- 注释：每个部门最多可拥有50个项目
    projects   projectlist         -- 项目列表：使用前面定义的可变数组类型
);
/

四、声明PL/SQL集合变量

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

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

DECLARE
    -- 定义嵌套表类型：存储最大长度为20的字符串
    -- 语法 TYPE 类型名 IS TABLE OF 元素类型；与 nested TYPE 类型名 IS TABLE OF 元素类型； 等价
    TYPE nested nested_type IS TABLE OF VARCHAR2(20);
    
    -- 定义可变数组类型：最多存储50个整数
    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
    -- 定义可变数组类型platoon，元素类型为soldier，最多包含20个元素
    TYPE platoon IS VARRAY(20) OF soldier;
    
    -- 声明platoon类型的变量p1
    p1  platoon;
    
    -- 注释：如果我们改变一个排的士兵数量，
    -- 当这个块重新编译时，p2将会反映出这种变化
    p2  p1%TYPE;  -- 声明p2为与p1相同的类型

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

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

CREATE PACKAGE personnel AS
    -- 定义嵌套表类型staff，元素类型为employee
    TYPE staff IS TABLE OF employee;
    
    -- 其他包元素定义
    ...
    
    -- 声明存储过程：为员工发放奖金
    -- 参数members：输入参数，类型为staff（员工集合）
    PROCEDURE award_bonuses(
        members  IN  staff
    );

END personnel;
/

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

DECLARE
    -- 定义可变数组类型SalesForce，存储Salesperson类型元素，最多25个
    TYPE SalesForce IS VARRAY(25) OF Salesperson;
    
    -- 声明函数：返回排名前n的销售人员
    -- 参数n：整数类型，指定返回的销售人员数量
    -- 返回值：SalesForce类型，包含排名前n的销售人员
    FUNCTION top_performers (
        n  INTEGER
    ) RETURN SalesForce IS
        -- 函数实现部分
        ...
    END top_performers;

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

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

DECLARE
    -- 定义嵌套表类型EmpList，元素类型与emp表的ename列相同
    TYPE EmpList IS TABLE OF emp.ename%TYPE;
    
    -- 声明游标c1，用于查询dept表的所有记录
    CURSOR c1 IS 
        SELECT * FROM dept;
    
    -- 定义可变数组类型DeptFile，元素类型与游标c1的行类型相同，最多存储20个元素
    TYPE DeptFile IS VARRAY(20) OF c1%ROWTYPE;

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

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

DECLARE
    -- 定义记录类型anentry，包含术语和释义两个字段
    TYPE anentry IS RECORD(
        term      VARCHAR2(20),   -- 术语（最大20个字符）
        meaning   VARCHAR2(200)   -- 释义（最大200个字符）
    );
    
    -- 定义可变数组类型glossary，元素类型为anentry，最多存储250个元素
    TYPE glossary IS VARRAY(250) OF anentry;

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

1
2
3

DECLARE
    -- 定义嵌套表类型EmpList，元素类型与emp表的empno列相同，且不允许为空值
    TYPE EmpList IS TABLE OF emp.empno%TYPE NOT NULL;

五、初始化与引用集合

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

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

例一：嵌套表的构造函数

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

DECLARE
    -- 定义嵌套表类型courselist，用于存储课程名称（最大16个字符）
    TYPE courselist IS TABLE OF VARCHAR2(16);
    
    -- 声明嵌套表变量my_courses
    my_courses  courselist;
BEGIN
    -- 初始化嵌套表并赋值
    my_courses := courselist(
        'Econ 2010',   -- 经济学课程
        'Acct 3401',   -- 会计学课程
        'Mgmt 3100'    -- 管理学课程
    );
END;
/

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

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

DECLARE
    -- 定义可变数组类型projectlist，最多存储50个项目名称（每个最多16个字符）
    TYPE projectlist IS VARRAY(50) OF VARCHAR2(16);
    
    -- 声明accounting_projects变量，类型为projectlist
    accounting_projects  projectlist;
BEGIN
    -- 初始化会计部门项目列表并赋值
    accounting_projects := projectlist(
        'Expense Report',  -- 费用报告项目
        'Outsourcing',     -- 外包项目
        'Auditing'         -- 审计项目
    );
END;
/

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

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

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

BEGIN
    -- 为my_courses赋值，包含三门课程，其中第二门为NULL
    my_courses := CourseList(
        'Math 3010',   -- 数学课程
        NULL,          -- 空值（未指定课程）
        'Stat 3202'    -- 统计学课程
    );

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

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

DECLARE
    -- 定义嵌套表类型courselist，用于存储课程名称（最大16个字符）
    TYPE courselist IS TABLE OF VARCHAR2(16);
    
    -- 声明并初始化嵌套表变量my_courses，包含三门课程
    my_courses  courselist := courselist(
        'Art 1111',    -- 艺术课程
        'Hist 3100',   -- 历史课程
        'Engl 2005'    -- 英语课程
    );

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

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

DECLARE
    -- 定义可变数组类型clientele，最多存储100个customer类型元素
    TYPE clientele IS VARRAY(100) OF customer;
    
    -- 声明并初始化空的可变数组变量vips
    vips  clientele := clientele();
BEGIN
    -- 检查vips是否不为空（已初始化的空集合在PL/SQL中被视为非NULL）
    IF vips IS NOT NULL THEN
        -- 条件判断结果为TRUE（执行此分支）
        ...
    END IF;
END;
/

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

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

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

BEGIN
    -- 向sophomores表插入一条记录，包含嵌套表类型的courses字段
    INSERT INTO sophomores
        VALUES (
            5035,                          -- 学生ID
            'Janet Alvarez',               -- 学生姓名
            '122 Broad St',                -- 学生地址
            'FT',                          -- 学生状态（全日制）
            courselist(                    -- 课程列表（嵌套表）
                'Econ 2010',               -- 经济学课程
                'Acct 3401',               -- 会计学课程
                'Mgmt 3100'                -- 管理学课程
            )
        );
END;
/

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

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

BEGIN
    -- 向department表插入一条记录，包含VARRAY类型的projects字段
    INSERT INTO department
        VALUES (
            60,                          -- 部门编号
            'Security',                  -- 部门名称
            750400,                      -- 部门预算
            projectlist(                 -- 项目列表（可变数组）
                'New Badges',            -- 新项目：新徽章
                'Track Computers',       -- 新项目：电脑追踪
                'Check Exits'            -- 新项目：出口检查
            )
        );
END;
/

六、引用集合中的元素

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

collection_name(subscript)

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

对于嵌套表，索引值的范围在1至2**31之间。

对于变长数组，索引值的范围在1至最大长度之间，最大长度是在声明时指定的。

对于使用数字作键的关联数组来说，索引值的范围在-231至231之间。

对于使用字符串作键的关联数组来说，键的长度和可用值的数量要依赖于类型声明时对VARCHAR2的长度限制和数据库字符集。

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

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

DECLARE
    -- 定义嵌套表类型roster，用于存储姓名（最大15个字符）
    TYPE roster IS TABLE OF VARCHAR2(15);
    
    -- 声明并初始化嵌套表变量names，包含3个姓名
    names  roster := roster(
        'J Hamil',    -- 姓名1
        'D Caruso',   -- 姓名2
        'R Singh'     -- 姓名3
    );
BEGIN
    -- 遍历嵌套表中的所有元素（从第一个元素到最后一个元素）
    FOR i IN names.FIRST .. names.LAST LOOP
        -- 检查当前元素是否为'J Hamil'
        IF names(i) = 'J Hamil' THEN
            NULL;  -- 找到匹配项时执行空操作（可替换为实际业务逻辑）
        END IF;
    END LOOP;
END;
/

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

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

DECLARE
    -- 定义嵌套表类型roster，用于存储姓名（最大15个字符）
    TYPE roster IS TABLE OF VARCHAR2(15);
    
    -- 声明并初始化嵌套表变量names，包含3个姓名
    names  roster := roster(
        'J Hamil',    -- 姓名1
        'D Piro',     -- 姓名2
        'R Singh'     -- 姓名3
    );
    
    -- 声明索引变量i，初始值为2（指向第二个元素）
    i  BINARY_INTEGER := 2;
BEGIN
    -- 调用verify_name过程，传入names集合中索引为i的元素
    verify_name(names(i));
END;
/

七、集合的赋值

集合可以用INSERT、UPDATE、FETCH或SELECT语句来赋值，也可以用赋值语句或调用子程序来赋值。

我们可以使用下面的语法来为某个指定的集合元素进行赋值：

collection_name(subscript) := expression;

其中expression的值和被指定的元素类型必须一致。下面我们来看三个例子。

例一：数据的兼容性

例子中的集合与集合之间互相赋值，但必须是两个集合类型相同的才可以，光是元素的类型相同是不够的。

DECLARE
    -- 定义可变array类型clientele，最多存储100个customer类型元素
    TYPE clientele IS VARRAY(100) OF customer;
    
    -- 定义另一个可变数组类型vips，结构相同但类型不同
    TYPE vips IS VARRAY(100) OF customer;
    
    -- 注释：前两个变量具有相同的数据类型
    group1  clientele := clientele(...);  -- 初始化clientele类型变量group1
    group2  clientele := clientele(...);  -- 初始化clientele类型变量group2
    
    -- 注释：第三个变量声明相似，但属于不同类型
    group3  vips := vips(...);            -- 初始化vips类型变量group3
BEGIN
    -- 允许：因为group1和group2具有相同的数据类型
    group2 := group1;
    
    -- 不允许：因为group2和group3属于不同的数据类型
    group3 := group2;
END;
/

例二：为嵌套表赋空值

当我们把一个被自动初始化为空的嵌套表或变长数组赋给另外一个嵌套表或变长数组时，被赋值的集合就会被重新初始化，结果也为NULL。

DECLARE
    -- 定义嵌套表类型clientele，用于存储客户名称（最大64个字符）
    TYPE clientele IS TABLE OF VARCHAR2(64);
    
    -- 注释：该嵌套表已初始化并赋值
    group1  clientele := clientele('Customer 1', 'Customer 2');
    
    -- 注释：该嵌套表未初始化（处于"原子空"状态）
    group2  clientele;
BEGIN
    -- 初始状态：IF group1 IS NULL 判断结果为FALSE
    -- 因为group1已通过构造函数初始化（即使为空集合也非NULL）
    
    -- 将未初始化的嵌套表赋值给group1
    group1 := group2;
    
    -- 现在：IF group1 IS NULL 判断结果为TRUE
    -- 因为group1现在引用了未初始化的嵌套表
    
    -- 注释：必须使用构造构造函数重新为其赋值
    -- 例如：group1 := clientele('New Customer');
END;
/

例三：集合赋值时可能引起的异常

在下面几种给集合元素赋值的情况下，可能会引起多种异常。

如果下标索引不存在或无法转换成正确的数据类型，PL/SQL就会抛出预定义异常VALUE_ERROR。通常，下标是一个整数。但关联数组的下标也可以是VARCHAR2类型。

如果所给下标索引指向了一个未初始化的元素时，PL/SQL就会抛出SUBSCRIPT_BEYOND_COUNT异常。

如果集合被自动初始化为空值并且程序引用了其中的一个元素，PL/SQL会抛出COLLECTION_IS_NULL异常。

DECLARE
    -- 定义嵌套表类型wordlist，存储最大长度为5的字符串
    TYPE wordlist IS TABLE OF VARCHAR2(5);
    
    -- 声明嵌套表变量words（未初始化）
    words  wordlist;
BEGIN
    /* 假设即使发生异常，程序仍继续执行 */
    
    -- 引发COLLECTION_IS_NULL异常
    -- 原因：尚未使用构造函数初始化集合
    -- 此异常适用于可变数组和嵌套表，但不适用于
    -- 不需要构造函数的关联数组
    words(1) := 10;
    
    -- 使用构造函数初始化后，才能为元素赋值
    words := wordlist(
        10,   -- 隐式转换为VARCHAR2(5)
        20,
        30
    );
    
    -- 任何返回VARCHAR2(5)类型的表达式都是允许的
    words(1) := 'yes';                  -- 直接赋值字符串
    words(2) := words(1) || 'no';       -- 字符串拼接（结果为'yesno'，长度5）
    
    -- 引发VALUE_ERROR异常
    -- 原因：赋值的字符串长度超过5个字符
    words(3) := 'longer than 5 characters';
    
    -- 引发VALUE_ERROR异常
    -- 原因：嵌套表的下标必须是整数
    words('B') := 'dunno';
    
    -- 引发SUBSCRIPT_BEYOND_COUNT异常
    -- 原因：构造函数只创建了3个元素
    -- 要添加新元素，必须先调用EXTEND方法
    words(4) := 'maybe';
END;
/

八、比较集合

我们可以检查一个集合是不是空，但不能判断两个集合是不是相同。像大于、小于这样的操作都是不允许使用的。

例一：检查集合是否为空

嵌套表和变长数组都能被自动初始化为空值，所以它们可以做判空操作：

DECLARE
    -- 定义嵌套表类型staff，用于存储employee类型元素
    TYPE staff IS TABLE OF employee;
    
    -- 声明嵌套表变量members（未初始化）
    members  staff;
BEGIN
    -- 条件判断结果为TRUE，因为尚未使用构造函数初始化members
    IF members IS NULL THEN
        ...  -- 执行当members为NULL时的处理逻辑
    END IF;
END;
/

例二：比较两个集合

集合不能直接进行等或不等的比较。例如下面的IF条件表达式就是不允许的。

DECLARE
    -- 定义嵌套表类型clientele，用于存储客户名称（最大64个字符）
    TYPE clientele IS TABLE OF VARCHAR2(64);
    
    -- 声明并初始化嵌套表变量group1
    group1  clientele := clientele('Customer 1', 'Customer 2');
    
    -- 声明并初始化嵌套表变量group2
    group2  clientele := clientele('Customer 1', 'Customer 3');
BEGIN
    -- 相等性测试会导致编译错误
    -- PL/SQL中不能直接使用=运算符比较嵌套表
    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类型：

`1`	`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
    -- 向department表插入英语系记录，包含其开设的课程列表
    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
    -- 声明变量存储英语系课程列表，类型为CourseList
    english_courses  CourseList;
BEGIN
    -- 从department表查询英语系的课程列表并赋值给变量
    SELECT courses
      INTO english_courses
      FROM department
     WHERE name = 'English';
END;
/

在PL/SQL中，我们可以循环遍历嵌套表的元素并使用TRIM或EXTEND方法来更新嵌套表中部分或全部的元素。然后，在把更新后的结果保存到数据库中去。

例四：用嵌套表更新数据库中

我们可以修改英语系所提供的课程列表：

DECLARE
    -- 声明新的课程列表，包含11门课程
    new_courses  courselist := courselist(
        'Expository Writing Writing',           -- 说明性写作
        'Film and Literature',            -- 电影与文学
        'Discursive Writing',             -- 论述性写作
        'Modern English Grammar',         -- 现代英语语法
        'Realism and Naturalism',         -- 现实主义与自然主义
        'Introduction to Shakespeare',    -- 莎士比亚入门
        'Modern Drama',                   -- 现代戏剧
        'The Short Story',                -- 短篇小说
        'The American Novelel',             -- 美国小说
        '20th-Century Poetry',            -- 20世纪诗歌
        '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对象：

`1`	`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
    -- 向department表插入会计部门记录及项目列表
    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)         -- 应付账款审计项目
            )
        );

    -- 向department表插入维护部门记录及项目列表
    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)       -- 冷却系统冬季维护项目
            )
        );

    -- 向department表插入安保部门记录及项目列表
    INSERT INTO department
        VALUES (
            60,                                  -- 部门编号
            'Security',                          -- 部门名称
            750400,                              -- 部门预算
            projectlist(                         -- 项目列表
                project(1, 'Issue New Employee Badges', 13500),    -- 发放新员工徽章项目
                project(2, 'Find Missing IC Chips', 2750),         -- 寻找丢失的IC芯片项目
                project(3, 'Upgrade Alarm System', 3350),          -- 升级报警系统项目
                project(4, 'Inspect Emergency Exits', 1900)        -- 检查紧急出口项目
            )
        );
END;
/

现在，让我们对Security系做个更新操作：

DECLARE
    -- 声明新的项目列表，包含5个安保部门项目
    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
    -- 更新部门ID为60的部门项目列表
    UPDATE department
       SET projects = new_projects
     WHERE dept_id = 60;
END;
/

接下来，对Accounting系做一次查询操作，并把结果放到本地变量中去：

DECLARE
    -- 声明变量存储项目列表，类型为projectlist
    my_projects  projectlist;
BEGIN
    -- 从department表查询部门ID为30的项目列表并赋值给变量
    SELECT projects
      INTO my_projects
      FROM department
     WHERE dept_id = 30;
END;
/

最后，删除记录Accounting：

BEGIN
    -- 删除部门ID为30的部门记录
    DELETE FROM department
     WHERE dept_id = 30;
END;
/

3. 使用SQL语句操作特定的集合元素

默认情况下，SQL操作会一次性的保存或检索整个集合而不是一个独立的元素。要用SQL语句操作集合中的独立的元素，可以使用TABLE操作符。 TABLE操作符用一个子查询把变长数组或嵌套表的内容提取出来，这样的话，INSERT、UPDATE或DELETE语句就可以作用于嵌套表，而不是整张数据表了。

下面，让我们看看一些具体的操作实例。

例一：向嵌套表中插入元素

首先，我们向历史系的嵌套表COURSES插入一条记录：

BEGIN
    -- TABLE运算符使语句作用于DEPARTMENT表中'History'行的嵌套表
    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);      -- 仅更新课程编号为2200和3540的课程
END;
/

例三：从嵌套表中检索一个元素

下面，我们从历史系检索出一个特定课程名称：

DECLARE
    -- 声明变量存储课程标题，最大长度64字符
    my_title  VARCHAR2(64);
BEGIN
    -- 已知历史系有一门课程标题包含'Etruscan'
    -- 此查询从历史系的嵌套课程表中检索完整标题
    SELECT title
      INTO my_title
      FROM TABLE (
          -- 子查询：获取历史系的课程嵌套表
          SELECT courses
            FROM department
           WHERE name = 'History'
      )
     WHERE name LIKE '%Etruscan%';  -- 筛选标题包含'Etruscan'的课程
END;
/

例四：从嵌套表中删除元素

最后，我们从英语系中删除所有那些学分为5的课程：

BEGIN
    -- 从英语系的课程嵌套表中删除学分为5的课程
    DELETE FROM TABLE (
        SELECT courses
          FROM department
         WHERE name = 'English'
    )
     WHERE credits = 5;  -- 删除条件：学分等于5
END;
/

例五：从变长数组中检索元素

下面例子演示了从变长数组类型的字段projects中检索出公务处第四个项目的名称和费用：

DECLARE
    -- 声明变量存储项目成本（最大7位数字，2位小数）
    my_cost   NUMBER(7, 2);
    
    -- 声明变量存储项目标题（最大35个字符）
    my_title  VARCHAR2(35);
BEGIN
    -- 从部门ID为50的项目列表中查询项目编号为4的成本和标题
    SELECT cost, title
      INTO my_cost, my_title
      FROM TABLE (
          -- 子查询：获取部门ID为50的项目嵌套表
          SELECT projects
            FROM department
           WHERE dept_id = 50
      )
     WHERE project_no = 4;  -- 筛选项目编号为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_id = dept_no
       FOR UPDATE OF projects;       -- 对projects字段加行级锁

    my_projects.EXTEND;              -- 扩展嵌套表容量，为新项目腾出空间

    /* 将指定位置及之后的项目元素向后移动一位 */
    FOR i IN REVERSE position .. my_projects.LAST - 1 LOOP
        my_projects(i + 1) := my_projects(i);
    END LOOP;

    my_projects(position) := new_project;  -- 在指定位置插入新项目

    -- 将更新后的项目列表写回数据库
    UPDATE department
       SET projects = my_projects
     WHERE dept_id = dept_no;
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_id = dept_no
       FOR UPDATE OF projects;       -- 对projects字段加行级锁

    /* 查找目标项目，更新其信息，然后立即退出循环 */
    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_id = dept_no;
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
    -- 统计修改后的课程列表与原始课程列表的差异数量
    -- TABLE(...)：这是 Oracle 的表函数，作用是将 “嵌套表” 转换为 “关系型表结构”，以便在 SQL 查询中像普通表一样使用。转换后，revised 这个嵌套表会被 “展开” 为一行行的记录，每行对应一个 course 对象的属性（course_no、title、credits）。
    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
    -- 定义基础级为整数的可变数组类型t1，最多存储10个元素
    TYPE t1 IS VARRAY(10) OF INTEGER;
    
    -- 定义嵌套可变数组类型nt1，元素类型为t1，最多存储10个元素
    TYPE nt1 IS VARRAY(10) OF t1;  -- 多级可变数组类型
    
    -- 初始化t1类型变量va
    va   t1 := t1(2, 3, 5);
    
    -- 初始化多级级可变 array（嵌套可变数组）
    nva  nt1 := nt1(
        va,                  -- 使用已初始化的va
        t1(55, 6, 73),       -- 直接初始化t1元素
        t1(2, 4),            -- 直接初始化t1元素
        va                   -- 再次使用va
    );
    
    -- 声明变量用于存储中间结果
    i     INTEGER;
    va1   t1;
BEGIN
    -- 多级访问：访问nva的第2个元素中的第3个值
    i := nva(2)(3);  -- i将获得值73
    DBMS_OUTPUT.put_line(i);
    
    -- 为nva添加一个新的可变数组元素
    nva.EXTEND;
    nva(5) := t1(56, 32);
    
    -- 替换内部可变数组元素
    nva(4) := t1(45, 43, 67, 43345);
    
    -- 替换内部整数元素
    nva(4)(4) := 1;  -- 将43345替换为1
    
    -- 为第4个可变数组元素添加新元素
    -- 并存储整数89
    nva(4).EXTEND;
    nva(4)(5) := 89;
END;
/

多级嵌套表

DECLARE
    -- 定义基础嵌套表类型tb1，存储VARCHAR2(20)类型元素
    TYPE tb1 IS TABLE OF VARCHAR2(20);
    
    -- 定义嵌套表的嵌套表类型ntb1（表中表结构）
    TYPE ntb1 IS TABLE OF tb1;  -- 嵌套表元素为表类型
    
    -- 定义可变数组类型tv1，存储INTEGER类型元素，最多10个
    TYPE tv1 IS VARRAY(10) OF INTEGER;
    
    -- 定义嵌套表类型ntb2，元素为可变数组类型tv1
    TYPE ntb2 IS TABLE OF tv1;  -- 嵌套表元素为可变数组类型
    
    -- 初始化基础嵌套表vtb1
    vtb1   tb1  := tb1('one', 'three');
    
    -- 初始化嵌套表的嵌套表vntb1
    vntb1  ntb1 := ntb1(vtb1);
    
    -- 初始化元素为可变数组的嵌套表vntb2
    vntb2  ntb2 := ntb2(
        tv1(3, 5),          -- 第一个元素为包含3,5的可变数组
        tv1(5, 7, 3)        -- 第二个元素为包含5,7,3的可变数组
    );  -- 元素为可变数组的嵌套表
BEGIN
    -- 扩展vntb1容量，增加一个空元素
    vntb1.EXTEND;
    
    -- 将vntb1的第一个元素值赋给第二个元素
    vntb1(2) := vntb1(1);
    
    -- 删除vntb1中的第一个元素
    vntb1.DELETE(1);
    
    -- 删除vntb1中第二个表的第一个字符串元素
    vntb1(2).DELETE(1);
END;
/

多级关联数组

DECLARE
    -- 定义索引表类型tb1，存储INTEGER类型，使用BINARY_INTEGER作为索引
    TYPE tb1 IS TABLE OF INTEGER
        INDEX BY BINARY_INTEGER;
    
    -- 定义索引表的索引表类型ntb1（索引表中嵌套索引表）
    TYPE ntb1 IS TABLE OF tb1
        INDEX BY BINARY_INTEGER;
    
    -- 定义可变数组类型va1，存储VARCHAR2(20)类型，最多10个元素
    TYPE va1 IS VARRAY(10) OF VARCHAR2(20);
    
    -- 定义索引表类型ntb2，元素为可变数组类型va1，使用BINARY_INTEGER作为索引
    TYPE ntb2 IS TABLE OF va1
        INDEX BY BINARY_INTEGER;
    
    -- 初始化可变数组v1
    v1   va1  := va1('hello', 'world');
    
    -- 声明索引表的索引表变量v2
    v2   ntb1;
    
    -- 声明元素为可变数组的索引表变量v3
    v3   ntb2;
    
    -- 声明索引表变量v4
    v4   tb1;
    
    -- 声明空索引表变量v5
    v5   tb1;   -- 空表
BEGIN
    -- 为索引表v4赋值（索引可以是非连续的）
    v4(1) := 34;
    v4(2) := 46456;
    v4(456) := 343;
    
    -- 将v4赋值给v2的第23个元素
    v2(23) := v4;
    
    -- 为v3的第34个元素赋值（存储一个包含4个整数的可变数组）
    v3(34) := va1(33, 456, 656, 343);
    
    -- 将空表v5赋值给v2的第35个元素
    v2(35) := v5;
    
    -- 现在可以为v2(35)的第2个元素赋值（因为已通过v5初始化）
    v2(35)(2) := 78;   -- 此处可以正常工作
END;
/

多级集合和批量SQL

-- 1. 创建可变数组类型t1，存储INTEGER类型，最多10个元素
CREATE TYPE t1 IS VARRAY(10) OF INTEGER;
/

-- 2. 创建表tab1，字段c1的类型为自定义可变数组t1
CREATE TABLE tab1 (
    c1  t1  -- 字段类型为可变数组t1
);
/

-- 3. 向tab1表插入第一条记录，c1字段值为包含3个整数的t1数组
INSERT INTO tab1
VALUES (t1(2, 3, 5));
/

-- 4. 向tab1表插入第二条记录，c1字段值为包含3个整数的t1数组
INSERT INTO tab1
VALUES (t1(9345, 5634, 432453));
/

-- 5. 声明PL/SQL块，批量查询tab1表数据并统计数量
DECLARE
    -- 定义嵌套表类型t2，元素类型为可变数组t1
    TYPE t2 IS TABLE OF t1;
    
    -- 声明嵌套表变量v2，用于存储批量查询结果
    v2  t2;
BEGIN
    -- 批量查询tab1表的c1字段，将结果存入v2
    -- BULK COLLECT 关键字，指示 PL/SQL 引擎 “批量获取” 查询结果，而不是一行一行地处理。它会将查询返回的所有行一次性加载到内存中的集合变量中，减少 SQL 引擎与 PL/SQL 引擎之间的上下文切换
    SELECT c1
      BULK COLLECT INTO v2
      FROM tab1;
    
    -- 输出v2中元素的数量（因tab1有2条记录，此处会打印2）
    DBMS_OUTPUT.put_line(v2.COUNT);   -- prints 2
END;
/

十一、集合的方法

集合提供了以下几个方法，能帮助我们更加方便维护和使用它：

EXISTS COUNT LIMIT FIRST和LAST PRIOR和NEXT EXTEND TRIM DELETE

一个集合方法就是一个内置于集合中并且能够操作集合的函数或过程，可以通过点标志来调用。使用方法如下：

`1`	`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存在的情况下执行赋值语句：

1
2
3

IF courses.EXISTS(i) THEN
  courses(i)    := new_course;
END IF;

当下标越界时，EXISTS会返回FALSE，而不是抛出SUBSCRIPT_OUTSIDE_LIMIT异常。

2. 计算集合中的元素个数(COUNT)

COUNT能够返回集合所包含的元素个数。例如，当下面的变长数组projects中含有25个元素时，IF条件就为TRUE：

`1`	`IF projects.COUNT = 25 THEN ...`

COUNT函数很有用，因为集合的当前大小不总是能够被确定。例如，如果我们把一个字段中的值放入嵌套表中，那么嵌套表中会有多少个元素呢？COUNT会给我们答案。

我们可以在任何可以使用整数表达式的地方使用COUNT函数。下例中，我们用COUNT来指定循环的上界值：

`1`	`FOR i IN 1 .. courses.COUNT LOOP ...`

对于变长数组来说，COUNT值与LAST值恒等，但对于嵌套表来说，正常情况下COUNT值会和LAST值相等。但是，当我们从嵌套表中间删除一个元素，COUNT值就会比LAST值小。

计算元素个数时，COUNT会忽略已经被删除的元素。

3. 检测集合的最大容量(LIMIT)

因为嵌套表和关联数组都没有上界限制，所以LIMIT总会返回NULL。但对于变长数组来说，LIMIT会返回它所能容纳元素的个数最大值，该值是在变长数组声明时指定的，并可用TRIM和EXTEND方法调整。例如下面的变长数组projects在最大容量是25的时候，IF的条件表达式值为真：

`1`	`IF projects.LIMIT = 25 THEN ...`

我们可以在任何允许使用整数表达式的地方使用LIMIT函数。下面的例子中，我们使用LIMIT来决定是否可以为变长数组再添加15个元素：

`1`	`IF (projects.COUNT + 15) < projects.LIMIT THEN ...`

4. 查找集合中的首末元素(FIRST和LAST)

FIRST和LAST会返回集合中第一个和最后一个元素在集合中的下标索引值。而对于使用VARCHAR2类型作为键的关联数组来说，会分别返回最低和最高的键值；键值的高低顺序是基于字符串中字符的二进制值，但是，如果初始化参数NLS_COMP被设置成ANSI的话，键值的高低顺序就受初始化参数NLS_SORT所影响了。

空集合的FIRST和LAST方法总是返回NULL。只有一个元素的集合，FIRST和LAST会返回相同的索引值。

`1`	`IF courses.FIRST = courses.LAST THEN ... -- only one element`

下面的例子演示了使用FIRST和LAST函数指定循环范围的下界和上界值：

`1`	`FOR i IN courses.FIRST .. courses.LAST LOOP ...`

实际上，我们可以在任何允许使用整数表达式的地方使用FIRST或LAST函数。下例中，我们用FIRST函数来初始化一个循环计数器：

1
2

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，因为集合中的第一个元素没有前驱：

1
2

-- 返回下标 x 的前一个元素的下标。如果 x 是第一个元素的下标（没有前一个元素），则返回 NULL
n := courses.PRIOR(courses.FIRST);   -- assigns NULL to n

PRIOR是NEXT的逆操作。比如说，存在一个元素i，下面的语句就是用元素i给自身赋值：

1
2

-- 返回下标 i 的后一个元素的下标。如果 i 是最后一个元素的下标，则返回 NULL。
projects(i) := projects.PRIOR(projects.NEXT(i));

我们可以使用PRIOR或NEXT来遍历集合。在下面的例子中，我们使用NEXT来遍历一个包含被删除元素的嵌套表：

i := courses.FIRST;  -- 获取第一个元素的下标
WHILE i IS NOT NULL LOOP
  -- 对 courses(i) 执行操作（例如处理元素值）
  i := courses.NEXT(i);  -- 获取下一个元素的下标
END LOOP;

在遍历元素时，PRIOR和NEXT都会忽略被删除的元素。

6. 扩大集合的容量(EXTEND)

为了扩大嵌套表或变长数组的容量，可以使用EXTEND方法。但该方法不能用于索引表。该方法有三种形式：

EXTEND 在集合末端添加一个空元素

EXTEND(n) 在集合末端添加n个空元素

EXTEND(n,i) 把第i个元素拷贝n份，并添加到集合的末端

例如，下面的语句在嵌套表courses的末端添加了元素1的5个副本：

courses.EXTEND(5,1);

不能使用EXTEND初始化一个空集合。同样，当我们对TABLE或VARRAY添加了NOT NULL约束之后，就不能再使用EXTEND的前两种形式了。

EXTEND操作的是集合内部大小，其中也包括被删除的元素。所以，在计算元素个数的时候，EXTEND也会把被删除的元素考虑在内。PL/SQL会为每一个被删除的元素保留一个占位符，以便在适当的时候让我们重新使用。如下例：

DECLARE
    -- 定义嵌套表类型courselist，存储课程编号（VARCHAR2(10)类型）
    TYPE courselist IS TABLE OF VARCHAR2(10);
    
    -- 声明嵌套表变量courses，用于存储课程列表
    courses  courselist;
BEGIN
    -- 初始化courses，存入3门课程的编号
    courses := courselist('Biol 4412', 'Psyc 3112', 'Anth 3001');
    
    -- 删除courses中索引为3的元素（即第三门课程'Anth 3001'）
    courses.DELETE(3);   -- delete element 3
    
    /* 
       PL/SQL会为被删除的元素3保留占位符，因此
       下一条语句会在末尾追加元素4，而非补全元素3
    */
    courses.EXTEND;   -- 追加一个空元素（此时元素4被创建，元素3仍为占位符）
    
    /* 
       现在元素4已存在，因此下一条语句不会抛出
       SUBSCRIPT_BEYOND_COUNT（下标超出计数）异常
    */
    courses(4) := 'Engl 2005';  -- 为元素4赋值（课程编号'Engl 2005'）
END;
/

当包含被删除元素时，嵌套表的内部大小就不同于COUNT和LAST返回的值了。举一个例子，假如我们初始化一个长度为5的嵌套表，然后删除第二个和第五个元素，这时的内部长度是5，COUNT返回值是3，LAST返回值是4。EXTEND方法会把所有的被删除的元素都一样对待，无论它是第一个，最后一个还是中间的。

7. 缩减集合的空间(TRIM)

TRIM有两种形式：TRIM 从集合末端删除一个元素

TRIM(n) 从集合末端删除n个元素

例如，下面的表达式从嵌套表courses中删除最后三个元素：courses.TRIM(3); 如果n值过大的话，TRIM(n)就会抛出SUBSCRIPT_BEYOND_COUNT异常。同EXTEND相似，TRIM也不会忽略被删除的元素。看一下下面的例子：

DECLARE
    -- 定义嵌套表类型courselist，用于存储课程编号（字符串类型，最大长度10）
    TYPE courselist IS TABLE OF VARCHAR2(10);
    
    -- 声明嵌套表变量courses，用于承载课程列表数据
    courses  courselist;
BEGIN
    -- 初始化嵌套表courses，存入3门课程的编号
    courses := courselist('Biol 4412', 'Psyc 3112', 'Anth 3001');
    
    -- 删除嵌套表的最后一个元素（此时为索引3的'Anth 3001'）
    courses.DELETE(courses.LAST);   -- delete element 3

    /* 
       此时，COUNT属性返回的值为2（剩余有效元素的数量）。
       你可能会认为下一条语句会通过裁剪元素1和2来清空嵌套表，
       但实际并非如此——因为TRIM方法在计算裁剪数量时，会将已删除的元素（如元素3）也计入总数，
       最终只会裁剪有效元素2和已删除的元素3，而非元素1。
    */
    courses.TRIM(courses.COUNT);  -- 按COUNT值（2）裁剪元素

    -- 输出嵌套表中索引1的元素（仍为初始值'Biol 4412'，未被裁剪）
    DBMS_OUTPUT.put_line(courses(1));   -- prints 'Biol 4412'
END;
/

一般的，不要同时使用TRIM和DELETE方法。最好是把嵌套表当作定长数组，只对它使用DELETE方法，或是把它当作栈，只对它使用TRIM和EXTEND方法。PL/SQL对TRIM掉的元素不再保留占位符。这样我们就不能简单地为被TRIM掉的元素赋值了。

8. 删除集合中的元素(DELETE)

DELETE方法有三种形式：DELETE 删除集合中所有元素

DELETE(n) 从以数字作主键的关联数组或者嵌套表中删除第n个元素。如果关联数组有一个字符串键，对应该键值的元素就会被删除。如果n为空，DELETE(n)不会做任何事情。

DELETE(m,n) 从关联数组或嵌套表中，把索引范围m到n的所有元素删除。如果m值大于n或是m和n中有一个为空，那么DELETE(m,n)就不做任何事。

例如：

BEGIN
    -- 删除索引为2的元素
    courses.DELETE(2);   -- deletes element 2
    
    -- 删除索引为7的元素（指定单一索引范围）
    courses.DELETE(7, 7);   -- deletes element 7
    
    -- 删除索引范围6到3的元素（因起始索引大于结束索引，实际不执行任何操作）
    courses.DELETE(6, 3);   -- does nothing
    
    -- 删除索引从3到6的所有元素（包含3和6）
    courses.DELETE(3, 6);   -- deletes elements 3 through 6
    
    -- 删除projects集合中的所有元素（清空集合）
    projects.DELETE;   -- deletes all elements
    
    -- 删除nicknames集合中键为'Chip'的元素（适用于关联数组）
    nicknames.DELETE('Chip');   -- deletes element denoted by this key
    
    -- 删除nicknames集合中键在'Buffy'到'Fluffy'范围内的所有元素
    -- （按字母顺序判断范围，适用于关联数组）
    nicknames.DELETE('Buffy', 'Fluffy');  -- deletes elements with keys in this alphabetic range
END;
/

变长数组是密集的，我们不能从中删除任何一个元素。如果被删除的元素不存在，DELETE只是简单地忽略它，并不抛出异常。PL/SQL会为被删除的元素保留一个占位符，以便我们可以重新为被删除的元素赋值。

DELETE方法能让我们维护有间隙的嵌套表。下面的例子中，我们把嵌套表prospects的内容放到临时表中，然后从中删除一部分元素后，再重新把它存入数据库中：

DECLARE
    -- 声明变量存储潜在客户列表，类型为prospectlist
    my_prospects  prospectlist;
    
    -- 声明变量存储预估收入
    revenue       NUMBER;
BEGIN
    -- 从customers表查询符合条件的潜在客户列表
    SELECT prospects
      INTO my_prospects
      FROM customers
     WHERE ...;  -- 此处省略具体查询条件

    -- 遍历潜在客户列表
    FOR i IN my_prospects.FIRST .. my_prospects.LAST LOOP
        -- 调用存储过程估算该潜在客户的收入
        estimate_revenue(my_prospects(i), revenue);   -- 调用存储过程
        
        -- 如果预估收入低于25000，则从列表中删除该潜在客户
        IF revenue < 25000 THEN
            my_prospects.DELETE(i);
        END IF;
    END LOOP;

    -- 将筛选后的潜在客户列表更新回customers表
    UPDATE customers
       SET prospects = my_prospects
     WHERE ...;  -- 此处省略具体更新条件
END;
/

分配给嵌套表的内存是动态的，删除元素时内存会被释放。

9. 使用集合类型参数的方法

在子程序中，我们可以对集合类型的参数直接调用它的内置方法，如下例：

-- 创建人事包规范，定义员工集合类型和奖金发放过程
CREATE PACKAGE personnel AS
    -- 定义员工集合类型staff，元素类型为employee
    TYPE staff IS TABLE OF employee;
    
    ...  -- 此处省略其他类型或子程序声明
    
    -- 声明奖金发放过程声明，接收staff类型的参数
    PROCEDURE award_bonuses(members IN staff);
END personnel;
/

-- 创建人事包体，实现奖金发放过程
CREATE PACKAGE BODY personnel AS
    -- 实现奖金发放过程
    PROCEDURE award_bonuses(members IN staff) IS
        ...  -- 此处省略局部变量声明
    BEGIN
        ...  -- 此处省略初始化逻辑
        
        -- 检查员工数量，如果超过10人则执行特定逻辑
        IF members.COUNT > 10 THEN   -- 调用集合方法COUNT
            ...  -- 此处省略针对大量员工的奖金处理逻辑
        END IF;
        
        ...  -- 此处省略其他处理逻辑
    END award_bonuses;
    
    ...  -- 此处省略其他子程序实现
END personnel;
/

注意：对于变长数组参数来说，LIMIT的值与参数类型定义相关，与参数的模式无关。

十二、避免集合异常

大多情况下，如果我们引用了一个集合中不存在的元素，PL/SQL就会抛出一个预定义异常。例如：

DECLARE
    -- 定义嵌套表类型numlist，存储NUMBER类型元素
    TYPE numlist IS TABLE OF NUMBER;
    
    -- 声明嵌套表变量nums（初始状态为原子空值，未初始化）
    nums  numlist;   -- atomically null
BEGIN
    /* 假设程序会继续执行，尽管会引发以下异常 */
    
    -- 尝试为未初始化的集合赋值，会引发COLLECTION_IS_NULL异常 (1)
    nums(1) := 1;   -- raises COLLECTION_IS_NULL (1)
    
    -- 初始化嵌套表，包含两个元素
    nums := numlist(1, 2);   -- initialize table
    
    -- 尝试使用NULL作为索引，会引发VALUE_ERROR异常 (2)
    nums(NULL) := 3;   -- raises VALUE_ERROR (2)
    
    -- 尝试使用0作为索引（嵌套表索引从1开始），会引发SUBSCRIPT_OUTSIDE_LIMIT异常 (3)
    nums(0) := 3;   -- raises SUBSCRIPT_OUTSIDE_LIMIT (3)
    
    -- 尝试为索引3赋值（当前集合只有2个元素），会引发SUBSCRIPT_BEYOND_COUNT异常 (4)
    nums(3) := 3;   -- raises SUBSCRIPT_BEYOND_COUNT (4)
    
    -- 删除索引为1的元素
    nums.DELETE(1);   -- delete element 1
    
    -- 尝试访问已删除的元素，会引发NO_DATA_FOUND异常 (5)
    IF nums(1) = 1 THEN 
        ... -- raises NO_DATA_FOUND (5)
    END IF;
END;
/

代码通过5个操作场景，演示了嵌套表操作中常见的异常触发情况，对应逻辑可概括为：

未初始化集合赋值：嵌套表变量nums初始为“原子空值”（未初始化），直接执行nums(1) := 1赋值操作，会触发异常；
无效索引（空值）：嵌套表初始化后，尝试用NULL作为索引（nums(NULL) := 3），会触发异常；
索引超出合法范围（下界）：嵌套表索引默认从1开始，尝试用0作为索引（nums(0) := 3），会触发异常；
索引超出合法范围（上界）：初始化后的嵌套表仅含2个元素（索引1、2），尝试用3作为索引（nums(3) := 3），会触发异常；
访问已删除元素：删除索引1的元素后，再通过nums(1)访问该位置，会触发异常。

集合异常类型与发生时机对照表

异常名称	发生时机（触发场景）
`COLLECTION_IS_NULL`	对“未初始化的空集合”调用方法或执行赋值操作时（如未初始化的嵌套表直接赋值）。
`NO_DATA_FOUND`	1. 下标索引指向“已被删除的集合元素”（如嵌套表`DELETE`后访问该位置）； 2. 关联数组中访问“不存在的键对应的元素”。
`SUBSCRIPT_BEYOND_COUNT`	下标索引值“超过集合当前实际包含的元素个数”（如含2个元素的集合，访问索引3）。
`SUBSCRIPT_OUTSIDE_LIMIT`	下标索引值“超过集合允许的索引范围”（如嵌套表访问索引0，或超出自定义索引边界）。
`VALUE_ERROR`	1. 下标索引值为`NULL`； 2. 下标索引值无法转换成集合键的合法类型； 3. 键类型为`PLS_INTEGER`时，索引值超出`PLS_INTEGER`的范围。

在某些情况下，如果我们为一个方法传递了一个无效的下标，并不会抛出异常。例如在使用DELETE方法的时候，我们向它传递NULL，它只是什么都没做而已。同样，用新值替换被删除的元素也不会引起NO_DATA_FOUND异常，如下例：

DECLARE
    -- 定义嵌套表类型numlist，存储NUMBER类型元素
    TYPE numlist IS TABLE OF NUMBER;
    
    -- 声明并初始化嵌套表nums，包含3个元素
    nums  numlist := numlist(10, 20, 30);   -- initialize table
BEGIN
    -- 尝试删除索引为-1的元素（无效索引，不执行任何操作，也不抛出SUBSCRIPT_OUTSIDE_LIMIT异常）
    nums.DELETE(-1);   -- does not raise SUBSCRIPT_OUTSIDE_LIMIT
    
    -- 删除索引为3的元素（即值为30的元素）
    nums.DELETE(3);   -- delete 3rd element
    
    -- 输出当前集合中的有效元素数量（此时为2）
    DBMS_OUTPUT.put_line(nums.COUNT);   -- prints 2
    
    -- 为索引3重新赋值（允许操作，不会抛出NO_DATA_FOUND异常）
    nums(3) := 30;   -- allowed; does not raise NO_DATA_FOUND
    
    -- 输出当前集合中的有效元素数量（此时恢复为3）
    DBMS_OUTPUT.put_line(nums.COUNT);   -- prints 3
END;
/

打包集合类型和本地集合类型总是不兼容的。假设我们想调用下面的打包过程：

-- 创建包规范pkg1，定义可变数组类型和存储过程声明
CREATE PACKAGE pkg1 AS
    -- 定义可变数组类型NumList，存储NUMBER(4)类型，最多25个元素
    TYPE NumList IS VARRAY(25) OF NUMBER(4);
    
    -- 声明存储过程delete_emps，接收NumList类型的参数
    PROCEDURE delete_emps (emp_list NumList);
END pkg1;
/

-- 创建包体pkg1，实现存储过程delete_emps
CREATE PACKAGE BODY pkg1 AS
    -- 实现删除员工的存储过程
    PROCEDURE delete_emps (emp_list NumList) IS
        ...  -- 此处省略局部变量声明
    BEGIN
        ...  -- 此处省略存储过程的具体实现逻辑
    END delete_emps;
    
    ...  -- 此处可省略其他存储过程或函数的实现
END pkg1;
/

在运行下面PL/SQL块时，第二个过程调用会因参数的数量或类型错误(wrong number or types of arguments error)而执行失败。这是因为打包VARRAY和本地VARRAY类型不兼容，虽然它们的定义形式都是一样的：

DECLARE
    -- 1. 声明与包pkg1中NumList类型结构一致的本地可变数组类型
    -- （元素类型NUMBER(4)，最大容量25）
    TYPE numlist IS VARRAY(25) OF NUMBER(4);
    
    -- 2. 声明并初始化emps：使用包pkg1的NumList类型（与存储过程参数类型一致）
    -- 这个NumList是包pkg1的公有类型（因为定义在包规范中），可以通过pkg1.NumList的方式在包外引用（Oracle 对标识符大小写不敏感，所以pkg1.numlist与pkg1.NumList等价）。
    emps   pkg1.numlist := pkg1.numlist(7369, 7499);
    
    -- 3. 声明并初始化emps2：使用本地定义的numlist类型（与包中类型结构相同但不属于同一类型）
    -- 不同作用域定义的同名类型视为完全不同的类型
    emps2  numlist      := numlist(7521, 7566);
BEGIN
    -- 调用pkg1.delete_emps：参数emps为包类型，类型匹配，正常执行
    pkg1.delete_emps(emps);
    
    -- 调用pkg1.delete_emps：参数emps2为本地类型，与包中NumList类型不兼容，会导致编译错误
    pkg1.delete_emps(emps2);   -- causes a compilation error
END;
/

小结

在数据库时代，特别是Oracle数据库时代，PL/SQL已经成为了数据库应用程序开发的标准语言。