Oracle PL/SQL 集合：Varray、嵌套和按表索引
菲奥娜·布朗
什么是收藏？
集合是特定数据类型元素的有序集合。它可以是简单数据类型或复杂数据类型（如用户定义或记录类型）的集合。
在集合中，每个元素都由一个术语来标识，称为 “下标”。 集合中的每个项目都分配有一个唯一的下标。可以通过引用该唯一下标来操作或获取该集合中的数据。
当需要处理或操作大量相同类型的数据时，集合是最有用的。可以使用“BULK”选项填充和操作整个集合 Oracle.
集合根据结构、下标和存储进行分类，如下所示。
- 按表索引（也称为关联数组）
- 嵌套表
- 变量数组
在任何时候，集合中的数据都可以通过三个术语来引用：集合名称、下标、字段/列名称，即“ （ ）。 ”。您将在下面的部分进一步了解上述这些收集类别。
变量数组
Varray 是一种数组大小固定的集合方法。数组大小不能超过其固定值。Varray 的下标为数值。以下是 Varray 的属性。
- 上限尺寸固定
- 从下标“1”开始按顺序填充
- 此集合类型始终是密集的，即我们无法删除任何数组元素。Varray 可以整体删除，也可以从末尾修剪。
- 由于它的本质总是密集的，所以它的灵活性很差。
- 当已知数组大小时使用并对所有数组元素执行类似的活动更为合适。
- 下标和序列始终保持稳定，即集合的下标和计数始终相同。
- 在程序中使用它们之前需要初始化它们。对未初始化的集合执行任何操作（EXISTS 操作除外）都会抛出错误。
- 它可以被创建为一个数据库对象，在整个数据库或子程序内部可见，并且只能在该子程序中使用。
下图将以图表方式解释 Varray（dense）的内存分配。
| 标 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 价值 | XYZ | 直流电压 | 斯德 | CXS | 维BC | 新和成 | QWE |
VARRAY 的语法：
TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
- 在上面的语法中，type_name 被声明为给定大小限制的 'DATA_TYPE' 类型的 VARRAY。数据类型可以是简单类型或复杂类型。
嵌套表
嵌套表是一种数组大小不固定的集合。它具有数字下标类型。下面是有关嵌套表类型的更多描述。
- 嵌套表没有大小上限。
- 由于上限大小不固定，每次使用集合之前都需要扩展内存。我们可以使用“EXTEND”关键字来扩展集合。
- 从下标“1”开始按顺序填充。
- 此集合类型可以同时为 密集和稀疏，即我们可以将集合创建为密集的，并且我们也可以随机删除单个数组元素，使其变得稀疏。
- 它为删除数组元素提供了更大的灵活性。
- 它存储在系统生成的数据库表中，可以在选择查询中使用来获取值。
- 下标和序列不稳定，即数组元素的下标和计数可以变化。
- 在程序中使用它们之前需要进行初始化，对未初始化的集合进行任何操作（EXISTS 操作除外）都会抛出错误。
- 它可以被创建为一个数据库对象，在整个数据库或子程序内部可见，并且只能在该子程序中使用。
下图将以图表形式解释嵌套表（密集和稀疏）的内存分配。黑色元素空间表示集合中的空元素，即稀疏元素。
| 标 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 值（密集） | XYZ | 直流电压 | 斯德 | CXS | 维BC | 新和成 | QWE |
| 值（稀疏） | QWE | ASD | 阿富汗 | ASD | 黄万华 |
嵌套表的语法：
TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
- 在上面的语法中，type_name 被声明为 'DATA_TYPE' 类型的嵌套表集合。数据类型可以是简单类型，也可以是复杂类型。
按表索引
按表索引是一种数组大小不固定的集合。与其他集合类型不同，按表索引集合中的下标可以由用户定义。以下是按表索引的属性。
- 下标可以是整数或字符串。在创建集合时，应提及下标类型。
- 这些集合不是按顺序存储的。
- 它们在本质上总是稀疏的。
- 数组大小不固定。
- 它们不能存储在数据库列中。它们应在特定会话中的任何程序中创建和使用。
- 它们在维护下标方面提供了更大的灵活性。
- 下标也可以是负下标序列。
- 它们更适合用于相对较小的集合值，其中集合可以在同一个子程序中初始化和使用。
- 在开始使用它们之前不需要初始化它们。
- 它不能作为数据库对象创建。它只能在子程序内部创建，并且只能在该子程序中使用。
- 此集合类型中不能使用 BULK COLLECT，因为集合中每条记录都必须明确给出下标。
下图将以图表形式解释嵌套表（稀疏）的内存分配。黑色元素空间表示集合中的空元素，即稀疏元素。
| 下标 (varchar) | 第一 | 第二 | 第三 | 第四 | 第五 | 第六 | 第七 |
| 值（稀疏） | QWE | ASD | 阿富汗 | ASD | 黄万华 |
表索引的语法
TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
- 在上面的语法中，type_name 被声明为类型为“DATA_TYPE”的索引表集合。数据类型可以是简单类型或复杂类型。subsciprt/index 变量被指定为 VARCHAR2 类型，最大大小为 10。
集合中的构造函数和初始化概念
构造函数是 Oracle 提供的内置函数，其名称与对象或集合相同。当对象或集合在会话中第一次被引用时，它们会首先执行。以下是集合上下文中构造函数的重要细节：
- 对于集合，应该明确调用这些构造函数来初始化它。
- 在引入程序之前，Varray 和 Nested 表都需要通过这些构造函数进行初始化。
- 构造函数隐式扩展了集合（Varray 除外）的内存分配，因此构造函数也可以将变量分配给集合。
- 通过构造函数为集合分配值永远不会使集合变得稀疏。
收集方法
Oracle 提供许多函数来操作和使用集合。这些函数在程序中非常有用，可以确定和修改集合的不同属性。下表将给出不同的函数及其描述。
| 付款方式 | 描述 | 句法 |
|---|---|---|
| 存在（n） | 此方法将返回布尔结果。如果 nth 元素存在于该集合中，否则将返回 FALSE。只有 EXISTS 函数可以在未初始化的集合中使用 | .EXISTS(元素位置) |
| COUNT个 | 给出集合中元素的总数 | 。数数 |
| 极限 | 它返回集合的最大大小。对于 Varray，它将返回已定义的固定大小。对于嵌套表和按表索引，它返回 NULL | 。限制 |
| 第一 | 返回集合第一个索引变量（下标）的值 | 。第一的 |
| LAST | 返回集合最后一个索引变量（下标）的值 | 。最后的 |
| 先前 (n) | 返回集合中位于索引变量之前的 nth 元素。如果没有前导索引值，则返回 NULL | .PRIOR(n) |
| 下一个（n） | 返回集合中成功索引变量的 nth 元素。如果没有成功索引值则返回 NULL | .NEXT(n) |
| 延伸 | 在集合末尾扩展一个元素 | 。延长 |
| 延伸（n） | 在集合末尾扩展 n 个元素 | .EXTEND(n) |
| 扩展 (n,i) | 扩展 i 的 n 个副本th 集合末尾的元素 | .EXTEND(n,i) |
| TRIM | 从集合末尾删除一个元素 | 。修剪 |
| 修剪（n） | 从集合末尾删除 n 个元素 | .TRIM（n） |
| 删除 | 删除集合中的所有元素。使集合为空 | 。删除 |
| 删除（n） | 从集合中删除第 n 个元素。如果第 n 个th 元素为 NULL，则此操作不会执行任何操作 | .删除（n） |
| 删除 (m,n) | 删除范围 m 内的元素th 至nth 在集合中 | .删除（m，n） |
例1：子程序级别的记录类型
在此示例中，我们将了解如何使用“批量收集'以及如何引用收集数据。
DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
TYPE emp_det_tbl IS TABLE OF emp_det; guru99_emp_rec emp_det_tbl:= emp_det_tbl();
BEGIN
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1000,’AAA’,25000,1000);
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1001,'XXX’,10000,1000);
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
INSERT INTO emp (emp_no,emp_name,salary, manager) VALUES (1003,’ZZZ’,'7500,1000);
COMMIT:
SELECT emp no,emp_name,manager,salary BULK COLLECT INTO guru99_emp_rec
FROM emp;
dbms_output.put_line (‘Employee Detail');
FOR i IN guru99_emp_rec.FIRST..guru99_emp_rec.LAST
LOOP
dbms_output.put_line (‘Employee Number: '||guru99_emp_rec(i).emp_no);
dbms_output.put_line (‘Employee Name: '||guru99_emp_rec(i).emp_name);
dbms_output.put_line (‘Employee Salary:'|| guru99_emp_rec(i).salary);
dbms_output.put_line(‘Employee Manager Number:'||guru99_emp_rec(i).manager);
dbms_output.put_line('--------------------------------');
END LOOP;
END;
/
代码说明：
- 代码行 2-8: 记录类型 'emp_det' 由 emp_no、emp_name、salary 和 manager 等列声明，数据类型为 NUMBER、VARCHAR2、NUMBER、NUMBER。
- 代码第 9 行： 创建记录类型元素“emp_det”的集合“emp_det_tbl”
- 代码第 10 行： 将变量“guru99_emp_rec”声明为“emp_det_tbl”类型并使用空构造函数初始化。
- 代码行12-15： 将示例数据插入“emp”表。
- 代码第 16 行： 提交插入事务。
- 代码第 17 行： 使用命令“BULK COLLECT”从“emp”表中获取记录，并将收集变量作为批量填充。现在变量“guru99_emp_rec”包含表“emp”中存在的所有记录。
- 代码行19-26： 设置“FOR”循环用于逐一打印集合中的所有记录。集合方法 FIRST 和 LAST 用作 循环.
输出：如您在上面的屏幕截图中看到的，当执行上述代码时，您将获得以下输出
Employee Detail Employee Number: 1000 Employee Name: AAA Employee Salary: 25000 Employee Manager Number: 1000 ---------------------------------------------- Employee Number: 1001 Employee Name: XXX Employee Salary: 10000 Employee Manager Number: 1000 ---------------------------------------------- Employee Number: 1002 Employee Name: YYY Employee Salary: 15000 Employee Manager Number: 1000 ---------------------------------------------- Employee Number: 1003 Employee Name: ZZZ Employee Salary: 7500 Employee Manager Number: 1000 ----------------------------------------------
