PL/SQL - Collections

Quiz

在本章中，我们将讨论 PL/SQL 中的 Collections。Collection 是一组具有相同数据类型的有序元素组。每个元素通过一个唯一下标来标识，该下标表示其在 collection 中的位置。

PL/SQL 提供了三种 collection 类型 −

Index-by tables 或 Associative array
Nested table
Variable-size array 或 Varray

Oracle 文档为每种 collection 类型提供了以下特性 −

Collection 类型	元素数量	下标类型	Dense 或 Sparse	创建位置	是否可以作为 Object Type 属性
Associative array (或 index-by table)	Unbounded	String 或 integer	Either	仅在 PL/SQL block 中	No
Nested table	Unbounded	Integer	Starts dense, can become sparse	在 PL/SQL block 中或在 schema 级别	Yes
Variablesize array (Varray)	Bounded	Integer	Always dense	在 PL/SQL block 中或在 schema 级别	Yes

我们已经在 'PL/SQL arrays' 章节中讨论了 varray。在本章中，我们将讨论 PL/SQL tables。

两种 PL/SQL tables 类型，即 index-by tables 和 nested tables，具有相同的结构，其行使用下标表示法进行访问。然而，这两种 tables 在一个方面不同；nested tables 可以存储在数据库列中，而 index-by tables 不能。

Index-By Table

index-by 表（也称为 关联数组）是一组键值对。每个键都是唯一的，用于定位对应的值。键可以是整数或字符串。

index-by 表使用以下语法创建。在此，我们创建一个名为 table_name 的 index-by 表，其键的类型为 subscript_type，关联的值的类型为 element_type。

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 
 
table_name type_name;

示例

以下示例展示了如何创建一个表来存储整数值及其对应的姓名，然后打印相同的姓名列表。

DECLARE 
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); 
   salary_list salary; 
   name   VARCHAR2(20); 
BEGIN 
   -- 添加元素到表中 
   salary_list('Rajnish') := 62000; 
   salary_list('Minakshi') := 75000; 
   salary_list('Martin') := 100000; 
   salary_list('James') := 78000;  
   
   -- 打印表 
   name := salary_list.FIRST; 
   WHILE name IS NOT null LOOP 
      dbms_output.put_line 
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); 
      name := salary_list.NEXT(name); 
   END LOOP; 
END; 
/

在 SQL 提示符下执行上述代码时，会产生以下结果 −

Salary of James is 78000 
Salary of Martin is 100000 
Salary of Minakshi is 75000 
Salary of Rajnish is 62000  

PL/SQL procedure successfully completed.

示例

index-by 表的元素也可以是任何数据库表的 %ROWTYPE 或任何数据库表字段的 %TYPE。以下示例说明了这一概念。我们将使用存储在数据库中的 CUSTOMERS 表，如下所示 −

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

DECLARE 
   CURSOR c_customers is 
      select name from customers; 

   TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; 
   name_list c_list; 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter)); 
   END LOOP; 
END; 
/

在 SQL 提示符下执行上述代码时，会产生以下结果 −

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed

嵌套表

嵌套表 类似于具有任意数量元素的一维数组。然而，嵌套表与数组在以下方面有所不同 −

数组具有声明的元素数量，但嵌套表没有。嵌套表的大小可以动态增加。
数组始终是稠密的，即它始终具有连续的下标。嵌套表最初是稠密的，但当从中删除元素时，它可以变得稀疏。

嵌套表使用以下语法创建 −

TYPE type_name IS TABLE OF element_type [NOT NULL]; 
 
table_name type_name;

这种声明类似于 index-by 表的声明，但没有 INDEX BY 子句。

嵌套表可以存储在数据库列中。它还可以用于简化 SQL 操作，在这些操作中，您可以将单列表与更大的表连接。关联数组无法存储在数据库中。

示例

以下示例说明了嵌套表的使用 −

DECLARE 
   TYPE names_table IS TABLE OF VARCHAR2(10); 
   TYPE grades IS TABLE OF INTEGER;  
   names names_table; 
   marks grades; 
   total integer; 
BEGIN 
   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i IN 1 .. total LOOP 
      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i)); 
   end loop; 
END; 
/

在 SQL 提示符下执行上述代码时，会产生以下结果 −

Total 5 Students 
Student:Kavita, Marks:98 
Student:Pritam, Marks:97 
Student:Ayan, Marks:78 
Student:Rishav, Marks:87 
Student:Aziz, Marks:92  

PL/SQL procedure successfully completed.

示例

嵌套表 的元素也可以是任何数据库表的 %ROWTYPE 或任何数据库表字段的 %TYPE。以下示例说明了这一概念。我们将使用存储在我们数据库中的 CUSTOMERS 表 −

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

DECLARE 
   CURSOR c_customers is  
      SELECT  name FROM customers;  
   TYPE c_list IS TABLE of customerS.No.ame%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); 
   END LOOP; 
END; 
/

在 SQL 提示符下执行上述代码时，会产生以下结果 −

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed.

Collection Methods

PL/SQL 提供了内置的 collection methods，使得 collections 更容易使用。下表列出了这些方法及其用途 −

序号	方法名称及用途
1	EXISTS(n) 如果 collection 中的第 n 个元素存在，则返回 TRUE；否则返回 FALSE。
2	COUNT 返回 collection 当前包含的元素数量。
3	LIMIT 检查 collection 的最大大小。
4	FIRST 返回使用 integer subscripts 的 collection 中的第一个（最小）索引号。
5	LAST 返回使用 integer subscripts 的 collection 中的最后一个（最大）索引号。
6	PRIOR(n) 返回 collection 中位于索引 n 之前的索引号。
7	NEXT(n) 返回位于索引 n 之后的索引号。
8	EXTEND 向 collection 追加一个 null 元素。
9	EXTEND(n) 向 collection 追加 n 个 null 元素。
10	EXTEND(n,i) 向 collection 追加 n 个第 i^th 元素的副本。
11	TRIM 从 collection 的末尾移除一个元素。
12	TRIM(n) 从 collection 的末尾移除 n 个元素。
13	DELETE 从 collection 中移除所有元素，并将 COUNT 设置为 0。
14	DELETE(n) 从具有 numeric key 的 associative array 或 nested table 中移除第 n^th 个元素。如果 associative array 具有 string key，则删除对应于该 key 值的元素。如果 n 为 null，则 DELETE(n) 不执行任何操作。
15	DELETE(m,n) 从 associative array 或 nested table 中移除范围 m..n 内的所有元素。如果 m 大于 n，或者 m 或 n 为 null，则 DELETE(m,n) 不执行任何操作。

Collection Exceptions

下表提供了 collection exceptions 及其引发情况 −

Collection Exception	引发情况
COLLECTION_IS_NULL	尝试对 atomically null 的 collection 执行操作。
NO_DATA_FOUND	下标指定了一个已被删除的元素，或 associative array 的不存在元素。
SUBSCRIPT_BEYOND_COUNT	下标超过了 collection 中的元素数量。
SUBSCRIPT_OUTSIDE_LIMIT	下标超出了允许的范围。
VALUE_ERROR	下标为 null 或无法转换为 key type。如果 key 定义为 PLS_INTEGER 范围，且下标超出此范围，则可能会发生此异常。

笙亿网络策划

PL/SQL Collections 怎么用？集合类型有哪些常见操作？

PL/SQL - Collections

Index-By Table

示例

示例

嵌套表

示例

示例

Collection Methods

Collection Exceptions

PL/SQL Collections 怎么用？集合类型有哪些常见操作？

PL/SQL - Collections

Index-By Table

示例

示例

嵌套表

示例

示例

Collection Methods

Collection Exceptions

相关问答