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

What is the difference between nested array and associative array?

提问by Puneet Kushwah

There are two links http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS99981and

有两个链接 http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS99981和

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

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

by referring above two links i have two doubt

通过参考以上两个链接，我有两个疑问

1.Which one is correct nested table?

1.哪一个是正确的嵌套表？

2.If the oracle doc is correct what is the difference between nested table and associative array?

2.如果oracle doc是正确的，嵌套表和关联数组有什么区别？

回答by Wernfried Domscheit

Here is another difference which is not that commonly known. You can compare two nested tables with =or <>but associative array you cannot.

这是另一个不为人知的差异。您可以将两个嵌套表与=或<>但不能使用关联数组进行比较。

DECLARE

    TYPE associative_array IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
    a_var_associative_array associative_array;
    b_var_associative_array associative_array;

    TYPE nested_table IS TABLE OF INTEGER;
    a_var_nested_table nested_table := nested_table(1, 2, 3, 4, 5);
    b_var_nested_table nested_table := nested_table(5, 4, 3, 2, 1);

BEGIN

    IF a_var_nested_table = b_var_nested_table THEN
        -- Note, the different order of values!
        DBMS_OUTPUT.PUT_LINE ( 'TRUE' );
    ELSE
        DBMS_OUTPUT.PUT_LINE ( 'FALSE' );
    END IF;

    -- IF a_var_associative_array = b_var_associative_array THEN -> gives you an error! 

END;

When you work with nested tables you can also use Multiset Operators, Multiset Conditionsand SETwhich are not available for associative arrays.

当您使用嵌套表时，您还可以使用Multiset Operators、Multiset Conditions以及SET不适用于关联数组。

回答by Thorsten Kettner

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 couldaccess 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。与批量收集一样元素的“名称”没有任何意义，您不会真正使用它们，而是通过如图所示的链。