在 Oracle 中使用不同类型的 PL/SQL 集合的目的

Purpose of using different types of PL/SQL collections in Oracle

提问by Madhav

What is the main purpose of using collections in oracle ?

在 oracle 中使用集合的主要目的是什么？

1. Index by tables

2. Nested tables

3. Variable size ARRAY

1. 按表索引

2. 嵌套表

3. 可变尺寸阵列

Can you please explain the difference between the above types of collections ?

你能解释一下上述类型的集合之间的区别吗？

回答by APC

Let's start with Nested Tables, they are the most common form of collection and so represent a useful basis of comparison.

让我们从嵌套表开始，它们是最常见的集合形式，因此代表了一个有用的比较基础。

A nested table is a variable which can hold more than one instance of something, often a record from a database table. They might be declared like this:

嵌套表是一种变量，它可以保存多个事物的实例，通常是数据库表中的一条记录。他们可以这样声明：

type emp_nt is table of emp%rowtype;
emp_rec_nt emp_nt;

They are useful whenever we want to store multiple instances of data against which we want to do the same thing. The classic example is using BULK COLLECT to store multiple records:

每当我们想要存储多个数据实例时，它们都非常有用，我们想要针对这些实例做同样的事情。经典示例是使用 BULK COLLECT 存储多条记录：

select * 
bulk collect into emp_rec_nt
from employees;

This gives us a source of data we can loop round; crucially we can navigate backwards as well as forwards, even skip to the end or the beginning, which are things we cannot do with a cursor. Nested tables can be collections of any data type, including composites such as PL/SQL records or user-defined types.

这为我们提供了一个可以循环的数据源；至关重要的是，我们可以向后和向前导航，甚至可以跳到结尾或开头，这是我们不能用光标做的事情。嵌套表可以是任何数据类型的集合，包括诸如 PL/SQL 记录或用户定义类型的组合。

An Index By table is better called (as the docs do) an Associative Array . These are simple collections of single attributeswith an index. Nested tables also have indexes but their indexes are just row counts. With an associative array the index can be meaningful, i.e. sourced from a data value. So they are useful for caching data values for later use. The index can be a number, or (since 9iR2) a string which can be very useful. For instance, here is an associative array of salaries which is indexed by the employee identifier.

Index By 表更好地称为（如文档所做的那样）一个 Associative Array 。这些是带有索引的单个属性的简单集合。嵌套表也有索引，但它们的索引只是行数。对于关联数组，索引可以是有意义的，即源自数据值。因此它们可用于缓存数据值以备后用。索引可以是一个数字，或者（从 9iR2 开始）一个非常有用的字符串。例如，这是一个由员工标识符索引的工资关联数组。

type emp_sal_aa is table of emp.sql%type
     index by emp.empno%type;
l_emp_sales emp_sal_aa;

Note that I could have declared that array using INDEX BY BINARY_INTEGER but it is clearer to use the %TYPE syntax instead (self-documenting code). Elements of that array can identified by an index value, in this case EMPNO:

请注意，我可以使用 INDEX BY BINARY_INTEGER 声明该数组，但使用 %TYPE 语法（自记录代码）更清楚。该数组的元素可以由索引值标识，在本例中为 EMPNO：

l_emp_sals(l_emp_no) := l_emp_sal;

Other than caching reference tables or similar look-up values there aren't many use cases for associative arrays.

除了缓存引用表或类似的查找值之外，关联数组的用例并不多。

Variable arrays are just nested tables with a pre-defined limit on the number of elements. So perhaps the name is misleading: they are actually fixed arrays. There's little we can do with VArrays which we can't do with nested tables (except constrain the number of elements and it's pretty rare that we would want to do that). They are declared like this:

变量数组只是对元素数量有预定义限制的嵌套表。所以这个名字可能有误导性：它们实际上是固定数组。我们对 VArray 无能为力，而对于嵌套表则无能为力（除了限制元素的数量，而且我们很少想这样做）。它们是这样声明的：

type emp_va is varray(14) of emp%rowtype;
emp_rec_va emp_va;

We can use bulk collect to populate a VArray ...

我们可以使用批量收集来填充 VArray ...

select * 
bulk collect into emp_rec_va
from employees;

However we must be certain the query will return at mostthe number of elements specified in the VArray's declaration. Otherwise the SELECT will hurl ORA-22165.

但是，我们必须确定查询最多返回VArray 声明中指定的元素数量。否则 SELECT 将抛出 ORA-22165。

There are no known use cases for variable arrays. Okay that's a bit harsh, but almost all of the time you will use nested tables instead. The one big advantage of VArrays over nested tables is that they guarantee the order of the elements. So if you must get elements out in the same order as you inserted them use a VArray.

变量数组没有已知的用例。好吧，这有点苛刻，但几乎所有时间您都将使用嵌套表。VArrays 相对于嵌套表的一大优势是它们保证了元素的顺序。因此，如果您必须以与插入元素相同的顺序取出元素，请使用 VArray。

The PL/SQL documentation devotes an entire chapter to collections. Find out more.

PL/SQL 文档用了一整章来介绍集合。 了解更多。

回答by shivaranjani

PL/SQL offers these collection types:-

PL/SQL 提供这些集合类型：-

Associative arrays, also known as index-by tables, let you look up elements using arbitrary numbers and strings for subscript values. These are similar to hash tables in other programming languages.

关联数组，也称为索引表，让您可以使用任意数字和字符串作为下标值来查找元素。这些类似于其他编程语言中的哈希表。

Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

嵌套表包含任意数量的元素。他们使用序列号作为下标。您可以定义等效的 SQL 类型，允许将嵌套表存储在数据库表中并通过 SQL 进行操作。

Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

可变数组（可变大小数组的缩写）保存固定数量的元素（尽管您可以在运行时更改元素数量）。他们使用序列号作为下标。您可以定义等效的 SQL 类型，从而允许将变量存储在数据库表中。它们可以通过 SQL 进行存储和检索，但灵活性不如嵌套表。

1. Choosing Between Nested Tables and Associative Arrays:-

1. 在嵌套表和关联数组之间进行选择：-

Both nested tables and associative arrays (formerly known as index-by tables) use similar subscript notation, but they have different characteristics when it comes to persistence and ease of parameter passing.

嵌套表和关联数组（以前称为索引表）都使用类似的下标表示法，但它们在持久性和参数传递的便利性方面具有不同的特征。

Nested tables can be stored in a database column, but associative arrays cannot. Nested tables can simplify SQL operations where you would normally join a single-column table with a larger table.

嵌套表可以存储在数据库列中，但关联数组不能。嵌套表可以简化 SQL 操作，您通常会将单列表与更大的表连接起来。

Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because associative array subscripts can be negative, can be nonsequential, and can use string values instead of numbers.

关联数组适用于相对较小的查找表，其中每次调用过程或初始化包时都可以在内存中构造集合。它们适用于收集事先未知的信息，因为它们的大小没有固定的限制。它们的索引值更加灵活，因为关联数组下标可以是负数，可以是不连续的，并且可以使用字符串值而不是数字。

PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to set up data values in associative arrays, then use those associative arrays with bulk constructs (the FORALL statement or BULK COLLECT clause).

PL/SQL 自动在主机数组和使用数字键值的关联数组之间进行转换。将集合传入和传出数据库服务器的最有效方法是在关联数组中设置数据值，然后将这些关联数组与批量构造（FORALL 语句或 BULK COLLECT 子句）一起使用。

2. Choosing Between Nested Tables and Varrays:-

2. 在嵌套表和可变数组之间进行选择：-

Varrays are a good choice when:

在以下情况下，可变数组是一个不错的选择：

The number of elements is known in advance.

元素的数量是预先知道的。

The elements are usually all accessed in sequence.

这些元素通常都是按顺序访问的。

When stored in the database, varrays keep their ordering and subscripts.

当存储在数据库中时，varrays 保持它们的顺序和下标。

Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.

每个 Varray 都存储为单个对象，要么在它作为列的表内（如果 Varray 小于 4KB），要么在表外但仍在同一个表空间中（如果 Varray 大于 4KB）。您必须同时更新或检索 varray 的所有元素，这在一次对所有元素执行某些操作时最合适。但是您可能会发现以这种方式存储和检索大量元素是不切实际的。

Nested tables are a good choice when:

在以下情况下，嵌套表是一个不错的选择：

The index values are not consecutive.

索引值不连续。

There is no set number of index values. However, a maximum limit is imposed.

没有固定数量的索引值。但是，施加了最大限制。

You need to delete or update some elements, but not all the elements at once.

您需要一次删除或更新某些元素，但不是所有元素。

You would usually create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

您通常会创建一个单独的查找表，主表的每一行都有多个条目，并通过连接查询访问它。

Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end.

嵌套表可以是稀疏的：您可以删除任意元素，而不仅仅是从末尾删除一个项目。

Nested table data is stored in a separate store table, a system-generated database table associated with the nested table. The database joins the tables for you when you access the nested table. This makes nested tables suitable for queries and updates that only affect some elements of the collection.

嵌套表数据存储在单独的存储表中，这是一个与嵌套表相关联的系统生成的数据库表。当您访问嵌套表时，数据库会为您连接表。这使得嵌套表适用于仅影响集合中某些元素的查询和更新。

You cannot rely on the order and subscripts of a nested table remaining stable as the nested table is stored in and retrieved from the database, because the order and subscripts are not preserved in the database.

您不能依赖嵌套表的顺序和下标保持稳定，因为嵌套表存储在数据库中并从数据库中检索，因为顺序和下标未保留在数据库中。

回答by Puneet Kushwah

U can use link

你可以使用链接

What is the difference between nested array and associative array?

嵌套数组和关联数组有什么区别？

or just read it.

或者只是阅读它。

A nested table is just an array of n elements.

嵌套表只是一个包含 n 个元素的数组。

declare
  type nested_table_of_integer is table of integer;
  v_my_nested_table nested_table_of_integer;
begin
  v_my_nested_table := nested_table_of_integer(); -- initialize
  v_my_nested_table.extend(10); -- add 10 elements
  v_my_nested_table(1) := 100;
  v_my_nested_table(11) := 1000; -- ORA-06533: Subscript beyond count
end;

A nested table must be initialized as shown. It has zero elements at first. To add elements we use EXTEND. This nested table has 10 elements. They are indexed 1 to 10. Element 1 has the value 100. The others have value null. An access to a non-existent element, say the 11th element, raises an error.

嵌套表必须按所示进行初始化。它最初有零个元素。要添加元素，我们使用 EXTEND。这个嵌套表有 10 个元素。它们的索引从 1 到 10。元素 1 的值为 100。其他元素的值为 null。访问不存在的元素，比如第 11 个元素，会引发错误。

An associative array on the other hand is an array of name/value pairs. Let's use numbers (pls_integer typically) for the naming:

另一方面，关联数组是名称/值对的数组。让我们使用数字（通常为 pls_integer）来命名：

declare
      type associative_array_of_integer is table of integer index by pls_integer;
      v_my_associative_array associative_array_of_integer;
    begin
      v_my_associative_array(1) := 100;
      v_my_associative_array(11) := 1000;
      v_my_associative_array(12) := v_my_associative_array(2); -- ORA-01403: no data found

end;

An associative array needs no initialization. It is empty and gets populated. Here we associate the element called 1 with the value 100 and the element with the name 11 with the value 1000. So there are two elements in the array. We get a no data found exception when we try to access a name that is not in the array.

关联数组不需要初始化。它是空的并被填充。这里我们将名为 1 的元素与值 100 相关联，将名为 11 的元素与值 1000 相关联。因此数组中有两个元素。当我们尝试访问不在数组中的名称时，会出现 no data found 异常。

We can also use strings for the names:

我们还可以使用字符串作为名称：

declare
  type associative_array_of_integer is table of integer index by varchar2(100);
  v_my_associative_array associative_array_of_integer;
begin
  v_my_associative_array('age father') := 39;
  v_my_associative_array('age mother') := 32;
  v_my_associative_array('age daughter') := 11;
end;

You can use both collections to get table data, but you use them differently. The nested table has a count and you can just loop from 1 to count to access its elements:

您可以使用这两个集合来获取表数据，但使用方式不同。嵌套表有一个计数，您可以从 1 循环到计数以访问其元素：

declare
  type nested_table_of_integer is table of integer;
  v_my_nested_table nested_table_of_integer;
begin
  v_my_nested_table := nested_table_of_integer(); -- initialize
  select table_name bulk collect into v_my_nested_table from user_tables;
  for i in 1 .. v_my_nested_table.count loop
    dbms_output.put_line(v_my_nested_table(i));
  end loop;
end;

The associative array however must be read from whatever happens to be the first index to the next and next and next using FIRST and NEXT.

然而，必须使用 FIRST 和 NEXT 从恰好是下一个和下一个和下一个的第一个索引中读取关联数组。

declare
  type associative_array_of_integer is table of integer index by pls_integer;
  v_my_associative_array associative_array_of_integer;
  i integer;
 begin
  select table_name bulk collect into v_my_associative_array from user_tables;
  i := v_my_associative_array.first;
  while i is not null loop
    dbms_output.put_line(v_my_associative_array(i));
    i := v_my_associative_array.next(i);
  end loop;
end;

The "names" happen to be 1, 2, 3, etc. here (given thus by the bulk collection) and you could access v_my_associative_array(1) for instance. Later in your program, however, after some possible delete operations in the array, there may be gaps, so you don't know whether an element named 1 exists and whether the element before element 4 happens to be element 3. As with bulk collect the "names" for the elements have no meaning you would not really use them, but go instead through the chain as shown.

此处的“名称”恰好是 1、2、3 等（因此由批量收集给出），例如，您可以访问 v_my_associative_array(1)。但是，在您的程序后期，在数组中进行一些可能的删除操作后，可能会出现间隙，因此您不知道是否存在名为 1 的元素以及元素 4 之前的元素是否恰好是元素 3。与批量收集一样元素的“名称”没有任何意义，您不会真正使用它们，而是通过如图所示的链。

回答by Dayakark

Index by tables are user defined data types which is used to store multiple data items. Basically these tables are unconstrained tables. Index by table having two parts, these are value field and key field. In value field, oracle server stores actual data, where as in key field oracle server stores indexes, that's why index by table having ‘key value' pairs and also indexes are by default integers, and these indexes are in between negative to positive numbers. This index field behaves like a primary key, it doesn't accept duplicate values. Generally index by tables are used to improve the performance or application, because these tables are stored in memory area, that's why these tables are also called a memory tables. Generally to improve the performance of the application, these table indexes are using ‘binary-integer' data type, so we are creating in two step process. First we are creating type, then only we are creating a variable of that type.

按表索引是用户定义的数据类型，用于存储多个数据项。基本上这些表是不受约束的表。按表索引有两部分，它们是值字段和键字段。在 value 字段中，oracle server 存储实际数据，而在 key 字段中 oracle server 存储索引，这就是为什么按表索引具有“键值”对并且索引默认为整数，并且这些索引介于负数和正数之间。此索引字段的行为类似于主键，它不接受重复值。通常使用表索引来提高性能或应用程序，因为这些表存储在内存区域中，这就是为什么这些表也被称为内存表。通常为了提高应用程序的性能，这些表索引都使用“二进制整数” 数据类型，因此我们分两步创建。首先我们创建类型，然后才创建该类型的变量。

Syntax:-

句法：-

Step1:- Type typename is table of data type (size) index by binary_integer;

Step1:- 类型 typename 是由 binary_integer 索引的数据类型（大小）表；

Step2:- Variablename typename;

Step2:- 变量名类型名；

For Morinformation >>>PL/SQL Index by tables (or) PL/SQL tables (or) associative arrays

更多信息 >>> PL/SQL 表索引（或）PL/SQL 表（或）关联数组

回答by Bharat Negi

A collection is an ordered group of elements, all of the same type. Each element is identified by a unique subscript that represents its position in the collection

集合是一组有序的元素，所有元素都具有相同的类型。每个元素都由一个唯一的下标标识，该下标代表其在集合中的位置

PL/SQL provides three collection types ?

PL/SQL 提供了三种集合类型？

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

索引表或关联数组 嵌套表 可变大小数组或可变数组

回答by Rahul M

The main purpose of using collection is to improve application performance. By using collections, we can 'cache' static data that needs to be accessed frequently and need to be modified. This results in reduced calls to a database.

使用集合的主要目的是提高应用程序性能。通过使用集合，我们可以“缓存”需要频繁访问和需要修改的静态数据。这会减少对数据库的调用。

Also, if you need to process a number of items of a similar type, storing these items in a collection will allow you to loop through each element with ease, referencing each one by an index.

此外，如果您需要处理多个类似类型的项目，将这些项目存储在一个集合中将使您能够轻松地遍历每个元素，并通过索引引用每个元素。

Refer this article for more detail information about collection

有关收集的更多详细信息，请参阅本文