Oracle PL/SQL 集合：Varray、嵌套表和索引表
Fiona Brown 什么是集合？
集合是指特定数据类型的有序元素组。它可以是简单数据类型的集合，也可以是复杂数据类型的集合（如用户定义类型或记录类型）。
在集合中，每个元素都由一个称为“下标”的术语标识。集合中的每个项都分配有一个唯一的下标。可以通过引用该唯一下标来操作或获取集合中的数据。
当需要处理或操作大量相同类型的数据时，集合是最有用的。可以使用Oracle中的“BULK”选项将集合整体进行填充和操作。
集合根据结构、下标和存储进行分类，如下所示。
- 索引表（也称为关联数组）
- 嵌套表
- Varrays
在任何时候，集合中的数据都可以通过三个术语来引用：集合名称、下标、字段/列名，格式为“<collection_name>(<subscript>).<column_name>”。在下面的部分中，您将进一步了解上述集合类别。
Varrays
Varray是集合方法的一种，其中数组的大小是固定的。数组的大小不能超过其固定值。Varray的下标是数值。以下是Varrays的属性。
- 上限大小固定
- 从下标‘1’开始顺序填充
- 此集合类型始终是连续的，即我们不能删除任何数组元素。Varray可以整体删除，也可以从末尾修剪。
- 由于其本质上始终是连续的，因此灵活性非常小。
- 当数组大小已知且对所有数组元素执行类似活动时，使用它更合适。
- 下标和序列始终保持稳定，即集合的下标和计数始终相同。
- 它们需要在程序中使用之前进行初始化。对未初始化的集合执行的任何操作（EXISTS操作除外）都会引发错误。
- 它可以创建为数据库对象，该对象在整个数据库中可见，也可以在子程序内部创建，只能在该子程序中使用。
下图将以图解方式解释Varray（连续）的内存分配。
| 下标 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 值 | Xyz | Dfv | Sde | Cxs | Vbc | Nhu | Qwe |
VARRAY语法
TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
- 在上面的语法中，type_name被声明为给定大小限制的‘DATA_TYPE’类型的VARRAY。数据类型可以是简单类型或复杂类型。
嵌套表
嵌套表是数组大小不固定的集合。它具有数字下标类型。以下是关于嵌套表类型的更多描述。
- 嵌套表没有上限大小。
- 由于上限大小不是固定的，因此每次使用集合之前，都需要扩展内存。我们可以使用‘EXTEND’关键字来扩展集合。
- 从下标‘1’开始顺序填充。
- 此集合类型可以是连续的，也可以是稀疏的，即我们可以创建连续的集合，也可以随机删除单个数组元素，这使其成为稀疏的。
- 它提供了更大的灵活性，可以删除数组元素。
- 它存储在系统生成的数据库表中，并可以在select查询中使用以获取值。
- 下标和序列不稳定，即数组元素的下标和计数可能会有所不同。
- 它们需要在程序中使用之前进行初始化。对未初始化的集合执行的任何操作（EXISTS操作除外）都会引发错误。
- 它可以创建为数据库对象，该对象在整个数据库中可见，也可以在子程序内部创建，只能在该子程序中使用。
下图将以图解方式解释嵌套表（连续和稀疏）的内存分配。黑色的元素空间表示集合中的空元素，即稀疏。
| 下标 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 值（连续） | Xyz | Dfv | Sde | Cxs | Vbc | Nhu | Qwe |
| 值（稀疏） | Qwe | Asd | Afg | Asd | Wer |
嵌套表语法
TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
- 在上面的语法中，type_name被声明为‘DATA_TYPE’类型的嵌套表集合。数据类型可以是简单类型或复杂类型。
索引表
索引表是数组大小不固定的集合。与其它集合类型不同，在索引表中，下标可以由用户定义。以下是索引表的属性。
- 下标可以是整数或字符串。在创建集合时，应指定下标类型。
- 这些集合不是顺序存储的。
- 它们本质上总是稀疏的。
- 数组大小不是固定的。
- 它们不能存储在数据库列中。它们应在会话中创建并在任何程序中使用。
- 它们在维护下标方面提供了更大的灵活性。
- 下标也可以是负数下标序列。
- 它们更适合用于相对较小的值集合，其中集合可以在同一子程序中初始化和使用。
- 它们在使用前无需初始化。
- 它不能创建为数据库对象。它只能在子程序内部创建，并且只能在该子程序中使用。
- 在此集合类型中不能使用BULK COLLECT，因为需要为集合中的每个记录显式指定下标。
下图将以图解方式解释索引表（稀疏）的内存分配。黑色的元素空间表示集合中的空元素，即稀疏。
| 下标（varchar） | FIRST | SECOND | THIRD | FOURTH | FIFTH | SIXTH | SEVENTH |
| 值（稀疏） | Qwe | Asd | Afg | Asd | Wer |
索引表语法
TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
- 在上面的语法中，type_name被声明为‘DATA_TYPE’类型的索引表集合。数据类型可以是简单类型或复杂类型。子脚本/索引变量为VARCHAR2类型，最大尺寸为10。
集合中的构造函数和初始化概念
构造函数是Oracle提供的内置函数，其名称与对象或集合相同。在会话中首次引用对象或集合时，它们会首先执行。以下是集合上下文中构造函数的关键详细信息：
- 对于集合，这些构造函数必须显式调用才能进行初始化。
- Varray和嵌套表都需要在程序中引用之前通过这些构造函数进行初始化。
- 构造函数会隐式扩展集合的内存分配（Varray除外），因此构造函数也可以为集合分配变量。
- 通过构造函数为集合赋值永远不会使集合变得稀疏。
集合方法
Oracle提供了许多用于操作和处理集合的函数。这些函数在程序中对于确定和修改集合的各种属性非常有用。下表将列出不同的函数及其描述。
| 方法 | 描述 | 语法 |
|---|---|---|
| EXISTS (n) | 此方法返回布尔结果。如果第n个元素存在于该集合中，它将返回‘TRUE’，否则返回FALSE。未初始化的集合只能使用EXISTS函数。 | <collection_name>.EXISTS(element_position) |
| COUNT | 给出集合中元素的总数。 | <collection_name>.COUNT |
| LIMIT | 它返回集合的最大大小。对于Varray，它将返回已定义的固定大小。对于嵌套表和索引表，它返回NULL。 | <collection_name>.LIMIT |
| FIRST | 返回集合的第一个索引变量（下标）的值。 | <collection_name>.FIRST |
| LAST | 返回集合的最后一个索引变量（下标）的值。 | <collection_name>.LAST |
| PRIOR (n) | 返回集合中第n个元素的先前索引变量。如果没有先前索引值，则返回NULL。 | <collection_name>.PRIOR(n) |
| NEXT (n) | 返回集合中第n个元素的后续索引变量。如果没有后续索引值，则返回NULL。 | <collection_name>.NEXT(n) |
| EXTEND | 在集合末尾扩展一个元素。 | <collection_name>.EXTEND |
| EXTEND (n) | 在集合末尾扩展n个元素。 | <collection_name>.EXTEND(n) |
| EXTEND (n,i) | 在集合末尾扩展第i个元素的n个副本。 | <collection_name>.EXTEND(n,i) |
| TRIM | 从集合末尾删除一个元素。 | <collection_name>.TRIM |
| TRIM (n) | 从集合末尾删除n个元素。 | <collection_name>.TRIM (n) |
| DELETE | 删除集合中的所有元素。使集合变为空。 | <collection_name>.DELETE |
| DELETE (n) | 从集合中删除第n个元素。如果第n个元素为NULL，则此操作无效。 | <collection_name>.DELETE(n) |
| DELETE (m,n) | 删除集合中从第m个到第n个范围内的元素。 | <collection_name>.DELETE(m,n) |
示例1：子程序级别的记录类型
在本例中，我们将使用‘BULK COLLECT’来填充集合，并学习如何引用集合数据。
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’类型，并使用null构造函数进行初始化。
- 代码行12-15：将示例数据插入‘emp’表。
- 代码行16：提交插入事务。
- 代码行17：从‘emp’表获取记录，并使用“BULK COLLECT”命令将集合变量作为批量进行填充。此时，变量‘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 ----------------------------------------------
