Plsql 简明教程

PL/SQL - Collections

在本章中，我们将讨论 PL/SQL 的 Collection。Collection 是拥有相同数据类型的一组有序元素。每个元素都被一个代表其在 Collection 中位置的唯一下标标识。

In this chapter, we will discuss the Collections in PL/SQL. A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.

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

PL/SQL provides three collection types −

  1. Index-by tables or Associative array

  2. Nested table

  3. Variable-size array or Varray

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

Oracle documentation provides the following characteristics for each type of collections −

Collection Type

Number of Elements

Subscript Type

Dense or Sparse

Where Created

Can Be Object Type Attribute

Associative array (or index-by table)

Unbounded

String or integer

Either

Only in PL/SQL block

No

Nested table

Unbounded

Integer

Starts dense, can become sparse

Either in PL/SQL block or at schema level

Yes

Variablesize array (Varray)

Bounded

Integer

Always dense

Either in PL/SQL block or at schema level

Yes

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

We have already discussed varray in the chapter 'PL/SQL arrays'. In this chapter, we will discuss the PL/SQL tables.

两种类型的 PL/SQL 表，例如索引表和嵌套表有相同的结构，并且它们的行的访问是使用下标符号。不过，这两种类型的表在一点上存在差异；嵌套表可以被储存在数据库列中，而索引表不能。

Both types of PL/SQL tables, i.e., the index-by tables and the nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot.

Index-By Table

index-by 表（也称为 associative array ）是一组 key-value 对。每个键是唯一的，用于查找相应的值。键可以是整数或字符串。

An index-by table (also called an associative array) is a set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string.

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

An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name, the keys of which will be of the subscript_type and associated values will be of the element_type

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

table_name type_name;

Example

以下示例演示如何创建一个表来存储整数值以及姓名，稍后它会打印相同的姓名列表。

Following example shows how to create a table to store integer values along with names and later it prints the same list of names.

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 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

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.

Example

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

Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as −

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 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

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

PL/SQL procedure successfully completed

Nested Tables

nested table 很像一个具有任意数量元素的一维数组。但是，嵌套表在以下方面与数组不同 −

A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects −

  1. An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.

  2. An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.

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

A nested table is created using the following syntax −

TYPE type_name IS TABLE OF element_type [NOT NULL];

table_name type_name;

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

This declaration is similar to the declaration of an index-by table, but there is no INDEX BY clause.

嵌套表可以存储在数据库列中。它还可以进一步用于简化 SQL 操作，在该操作中，将单列表与较大表联接。关联数组无法存储在数据库中。

A nested table can be stored in a database column. It can further be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.

Example

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

The following examples illustrate the use of nested table −

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 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

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.

Example

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

Elements of a nested table can also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as −

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 提示符下执行时，它会生成以下结果：

When the above code is executed at the SQL prompt, it produces the following result −

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 提供了内置收集方法，使收集更容易使用。下表列出了方法及其用途 −

PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the methods and their purpose −

S.No

Method Name & Purpose

1

EXISTS(n) Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.

2

COUNT Returns the number of elements that a collection currently contains.

3

LIMIT Checks the maximum size of a collection.

4

FIRST Returns the first (smallest) index numbers in a collection that uses the integer subscripts.

5

LAST Returns the last (largest) index numbers in a collection that uses the integer subscripts.

6

PRIOR(n) Returns the index number that precedes index n in a collection.

7

NEXT(n) Returns the index number that succeeds index n.

8

EXTEND Appends one null element to a collection.

9

EXTEND(n) Appends n null elements to a collection.

10

EXTEND(n,i) Appends n copies of the ith element to a collection.

11

TRIM Removes one element from the end of a collection.

12

TRIM(n) Removes n elements from the end of a collection.

13

DELETE Removes all elements from a collection, setting COUNT to 0.

14

DELETE(n) Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.

15

DELETE(m,n) Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

Collection Exceptions

下表提供了集合异常及其引发的时间 −

The following table provides the collection exceptions and when they are raised −

Collection Exception

Raised in Situations

COLLECTION_IS_NULL

You try to operate on an atomically null collection.

NO_DATA_FOUND

A subscript designates an element that was deleted, or a nonexistent element of an associative array.

SUBSCRIPT_BEYOND_COUNT

A subscript exceeds the number of elements in a collection.

SUBSCRIPT_OUTSIDE_LIMIT

A subscript is outside the allowed range.

VALUE_ERROR

A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.