Oracle PL/SQL 集合：Varray、嵌套和按表索引

什么是集合？

集合是特定数据类型的一组有序元素。它可以是简单数据类型的集合，也可以是复杂数据类型的集合，例如用户自定义数据类型或记录类型。

在一个集合中，每个元素都由一个称为元素的术语来标识。 “下标”。 每个项目都被分配一个唯一的下标，可以通过引用该唯一的下标来操作或获取数据。

当需要处理或操作大量相同类型的数据时，集合最为有用。可以使用“批量”选项对集合进行整体填充和操作。 Oracle.

集合根据结构、下标和存储方式进行分类，如下所示：

• 索引表（也称为关联数组）
• 嵌套表
• 变量数组

任何时候，集合中的数据都可以用三个术语来指代：集合名称、下标和字段或列名称，例如“ （ ）。您将在以下章节中了解这些收藏类别。

收藏类型概览

这三种收藏类型各有优缺点。下表将它们并列列出，然后再详细介绍每一种。

变量数组

Varray 是一种数组，其数组大小固定且不能超过限制。Varray 的下标是一个数值。Varray 的属性包括：

• 尺寸上限是固定的。
• 从下标“1”开始按顺序填充。
• 这种集合类型始终是稠密的；我们无法删除单个数组元素。Varray 可以整体删除，也可以从末尾进行裁剪。
• 因为它密度很高，所以几乎没有柔韧性。
• 当数组大小已知，并且对所有元素执行类似操作时，这种方法更为合适。
• 集合的下标和计数始终保持稳定。
• 必须先初始化才能使用。对未初始化的集合执行除 EXISTS 之外的任何操作都会抛出错误。
• 它可以创建为整个数据库中可见的数据库对象，也可以创建为仅在子程序中使用的子程序对象。

下图解释了 Varray（密集型）的内存分配。

VARRAY 的语法：

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

• 在上述语法中，type_name 被声明为类型为 'DATA_TYPE' 的 VARRAY，大小限制为给定值。数据类型可以是简单数据类型，也可以是复杂数据类型。

嵌套表

嵌套表是一种数组大小不固定的集合。它具有数值下标类型。更多关于嵌套表类型的信息：

• 嵌套表没有大小上限。
• 由于上限不固定，每次使用前都需要使用“EXTEND”关键字扩展内存。
• 从下标“1”开始按顺序填充。
• 这种集合类型可以同时满足以下两个条件： 密集和稀疏我们可以将其创建为密集型，也可以随机删除单个元素，使其变为稀疏型。
• 它为删除数组元素提供了更大的灵活性。
• 它存储在系统生成的数据库表中，可以在选择查询中使用以获取值。
• 下标和计数可以不同。
• 必须先初始化才能使用。对未初始化的集合执行除 EXISTS 之外的任何操作都会抛出错误。
• 它可以创建为整个数据库中可见的数据库对象，也可以创建为仅在子程序中使用的子程序对象。

下图解释了嵌套表（稠密表和稀疏表）的内存分配。空白元素区域表示稀疏元素。

嵌套表的语法：

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

• 在上述语法中，type_name 被声明为类型为 'DATA_TYPE' 的嵌套表集合。数据类型可以是简单类型，也可以是复杂类型。

按表索引

索引表是一种数组大小不固定的集合。与其他集合类型不同，索引表的下标可以由用户定义。索引表的属性包括：

• 下标可以是整数或字符串。创建集合时应指定下标类型。
• 这些集合不是按顺序存储的。
• 它们在本质上总是稀疏的。
• 数组大小不固定。
• 它们无法存储在数据库列中。它们是在特定会话中创建和使用的。
• 它们在维护下标方面提供了更大的灵活性。
• 下标可以是负序列。
• 它们更适合在同一子程序中使用的相对较小的集合值。
• 它们无需在使用前进行初始化。
• 它们不能作为数据库对象创建；它们只能在子程序内部创建。
• BULK COLLECT 不能与此集合类型一起使用，因为必须为每个记录显式指定下标。

下图解释了索引表（稀疏表）的内存分配。空白元素区域表示稀疏元素。

索引表的语法：

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);

• 在上述语法中，type_name 被声明为类型为 'DATA_TYPE' 的索引表集合。下标变量被指定为 VARCHAR2 类型，最大长度为 10。

集合中的构造函数和初始化概念

构造函数是由提供的内置函数。 Oracle 与对象或集合同名的构造函数。每当会话中首次引用对象或集合时，这些构造函数都会首先执行。集合上下文中构造函数的重要细节：

• 对于集合，必须显式调用这些构造函数来初始化集合。
• Varray 和嵌套表都需要通过这些构造函数进行初始化，然后才能在程序中引用它们。
• 构造函数隐式地扩展了集合（Varray 除外）的内存分配，因此它也可以向集合分配变量。
• 通过构造函数赋值永远不会使集合变为稀疏集合。

收集方法

Oracle 它提供了许多用于操作和处理集合的函数。这些函数用于确定和修改集合的不同属性。下表列出了不同的函数及其说明。

示例 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;
/

Code 说明

• Code 第 2-8 行： 记录类型 'emp_det' 表声明包含列 emp_no、emp_name、manager 和 salary，数据类型分别为 NUMBER、VARCHAR2、NUMBER 和 NUMBER。
• Code 第9行： 创建记录类型为“emp_det”的集合“emp_det_tbl”。
• Code 第10行： 将变量“guru99_emp_rec”声明为“emp_det_tbl”类型，并使用空构造函数对其进行初始化。
• Code 第 12-15 行： 将示例数据插入“emp”表。
• Code 第16行： 提交插入事务。
• Code 第17行： 使用“BULK COLLECT”从“emp”表中批量提取记录并填充集合变量。变量“guru99_emp_rec”现在包含表“emp”中的所有记录。
• Code 第 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
----------------------------------------------

常见问题