oracle 集合

当提到 Oracle 数据库中的集合时，我们通常指的是三种类型的集合：VARRAY、Nested table 和 Associative array。

VARRAY

VARRAY 是一种变长数组，可以在定义时指定数组大小但也可以在后续向数组添加元素的过程中动态地改变其大小。下面是一个 VARRAY 的定义示例：

-- 定义一个 name_vary 数组，数组元素类型为字符串类型（最大长度为50）
DECLARE
  TYPE name_varry_t IS VARRAY(10) OF VARCHAR2(50);
  name_vary name_varry_t := name_varry_t();
BEGIN
  name_vary.extend(2);
  name_vary(1) := 'Alice';
  name_vary(2) := 'Bob';
END;

在上述示例中，我们创建了一个名为 name_vary 的数组，其元素类型为 VARCHAR2(50)，即最大长度为50的字符串类型。我们使用 name_varry_t 定义了一个类型名，用 VARRAY(10) OF 表示该类型是一个 VARRAY，且最多可以包含 10 个元素。

接着我们创建了 name_vary 变量，并使用 extend(2) 方法将数组长度扩充为 2。最后，我们设定数组的第一个和第二个元素分别为 Alice 和 Bob。

Nested table

与 VARRAY 不同的是，Nested table 在定义时不需要指定初始长度，而且可以接受 NULL 值。我们也可以使用 extend 方法动态地扩展其长度。

以下是一个 Nested table 的定义示例：

-- 定义一个 name_list Nested table，元素类型为字符串类型（最大长度为50）
DECLARE
  TYPE name_list_t IS TABLE OF VARCHAR2(50);
  name_list name_list_t := name_list_t();
BEGIN
  name_list.extend(2);
  name_list(1) := 'Alice';
  name_list(2) := 'Bob';
END;

注意：我们也使用了 TABLE OF 来表示该类型是一个 Nested table。

Associative array

除了 VARRAY 和 Nested table，Oracle 数据库还支持键值对形式的 Associative array。我们可以使用关键字 INDEX BY 来定义该类型。示例代码如下：

-- 定义一个 name_dict Associative array，元素类型为字符串类型（最大长度为50）
DECLARE
  TYPE name_dict_t IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(30);
  name_dict name_dict_t := name_dict_t();
BEGIN
  name_dict('Alice') := 'Alice Smith';
  name_dict('Bob') := 'Bob Johnson';
END;

在上述示例中，我们定义了一个 name_dict 的 Associative array，其元素类型为 VARCHAR2(50) 类型的字符串。我们用 INDEX BY VARCHAR2(30) 表示键的类型也为 VARCHAR2(30) 类型的字符串。接下来，我们设置了两个键值对，键为 Alice 和 Bob，值为分别为 Alice Smith 和 Bob Johnson。

示例说明

下面通过两组示例来说明如何在 SQL 语句中使用以上三种集合类型。

示例一：在 SQL 语句中使用 VARRAY

示例需求：使用 PL/SQL 代码创建一个 VARRAY 类型，然后在 SQL 语句中使用该 VARRAY 类型。

下面是示例代码：

-- 创建 VARRAY
DECLARE
  TYPE number_list_t IS VARRAY(10) OF NUMBER;
  number_list number_list_t := number_list_t();
BEGIN
  number_list.extend(5);
  number_list(1) := 1;
  number_list(2) := 3;
  number_list(3) := 5;
  number_list(4) := 7;
  number_list(5) := 9;
END;

-- 在 SQL 语句中使用 VARRAY
SELECT * FROM employees WHERE department_id IN (SELECT t.column_value FROM TABLE(number_list) t);

上述代码中，我们创建了一个名为 number_list 的 VARRAY 类型，并在其中添加了 5 个元素。接着，我们在 SQL 语句中使用了 TABLE 关键字，将 VARRAY 转换为数据表，并使用 IN 操作符进行条件过滤。最后，我们可以看到 SQL 查询返回的结果为部门 ID 为 1、3、5、7、9 的员工。

示例二：在 SQL 语句中使用 Associative array

示例需求：使用 PL/SQL 代码创建一个 Associative array 类型，然后在 SQL 语句中使用该 Associative array 类型。