Oracle PL/SQL – 集合
简述
在本章中，我们将讨论 PL/SQL 中的集合。集合是一组具有相同数据类型的有序元素。每个元素都由一个唯一的下标标识，该下标表示其在集合中的位置。
PL/SQL 提供三种集合类型 -
- 索引表或关联数组
- 嵌套表
- 可变大小数组或 Varray
Oracle 文档为每种类型的集合提供以下特征 -
| 集合类型 | 元素数量 | 下标类型 | 密集或稀疏 | 在哪里创建 | 可以是对象类型属性 |
|---|---|---|---|---|---|
| 关联数组（或索引表） | 无界 | 字符串或整数 | 任何一个 | 仅在 PL/SQL 块中 | 否 |
| 嵌套表 | 无界 | 整数 | 开始密集，可以变得稀疏 | 在 PL/SQL 块中或在模式级别 | 是 |
| 可变大小数组（Varray） | 有界 | 整数 | 总是密集 | 在 PL/SQL 块中或在模式级别 | 是 |
我们已经在本章讨论过 varray 'PL/SQL arrays'. 在本章中，我们将讨论 PL/SQL 表。
两种类型的 PL/SQL 表，即索引表和嵌套表具有相同的结构，并且使用下标符号访问它们的行。但是，这两种类型的表在一方面有所不同；嵌套表可以存储在数据库列中，索引表不能。
索引表
一个 index-by 表（也称为 associative array) 是一组 key-value对。每个键都是唯一的，用于定位相应的值。键可以是整数或字符串。
使用以下语法创建索引表。在这里，我们正在创建一个index-by 表命名 table_name，其键将是 subscript_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
-- adding elements to the table
salary_list('Rajnish') := 62000;
salary_list('Minakshi') := 75000;
salary_list('Martin') := 100000;
salary_list('James') := 78000;
-- printing the table
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.
例子
索引表的元素也可以是 %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
嵌套表
一种 nested table就像一个具有任意数量元素的一维数组。但是，嵌套表在以下方面与数组不同 -
-
数组具有声明的元素数量，但嵌套表没有。嵌套表的大小可以动态增加。
-
数组总是密集的，即它总是有连续的下标。嵌套数组最初是密集的，但是当从中删除元素时它会变得稀疏。
使用以下语法创建嵌套表 -
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.
例子
的元素 nested table 也可以是一个 %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 customer序号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.
集合方法
PL/SQL 提供了内置的集合方法，使集合更易于使用。下表列出了方法及其目的 -
| 序号 | 方法名称和目的 |
|---|---|
| 1 |
EXISTS(n)
如果集合中的第 n 个元素存在，则返回 TRUE；否则返回 FALSE。
|
| 2 |
COUNT
返回集合当前包含的元素数。
|
| 3 |
LIMIT
检查集合的最大大小。
|
| 4 |
FIRST
返回使用整数下标的集合中的第一个（最小）索引号。
|
| 5 |
LAST
返回使用整数下标的集合中的最后（最大）索引号。
|
| 6 |
PRIOR(n)
返回集合中索引 n 之前的索引号。
|
| 7 |
NEXT(n)
返回索引 n 之后的索引号。
|
| 8 |
EXTEND
将一个空元素附加到集合中。
|
| 9 |
EXTEND(n)
将 n 个空元素附加到集合中。
|
| 10 |
EXTEND(n,i)
附加 n将第 i个元素复制到集合中。
|
| 11 |
TRIM
从集合的末尾删除一个元素。
|
| 12 |
TRIM(n)
删除 n 集合末尾的元素。
|
| 13 |
DELETE
从集合中删除所有元素，将 COUNT 设置为 0。
|
| 14 |
DELETE(n)
删除 nth来自具有数字键或嵌套表的关联数组的元素。如果关联数组有字符串键，则删除键值对应的元素。如果n 一片空白， DELETE(n) 什么也没做。
|
| 15 |
DELETE(m,n)
删除范围内的所有元素 m..n来自关联数组或嵌套表。如果m 大于 n 或者如果 m 要么 n 一片空白， DELETE(m,n) 什么也没做。
|
集合异常
下表提供了集合异常以及它们何时引发 -
| 集合异常 | 在情况下长大 |
|---|---|
| COLLECTION_IS_NULL | 您尝试对原子性 null 集合进行操作。 |
| 没有找到数据 | 下标表示已删除的元素，或关联数组中不存在的元素。 |
| SUBSCRIPT_BEYOND_COUNT 个 | 下标超过了集合中元素的数量。 |
| SUBSCRIPT_OUTSIDE_LIMIT | 下标超出允许范围。 |
| VALUE_ERROR | 下标为空或不可转换为键类型。如果键被定义为一个PLS_INTEGER 范围，且下标在此范围之外。 |