在Oracle中使用不同类型的PL / SQL集合的目的 [英] Purpose of using different types of PL/SQL collections in Oracle
问题描述
在oracle中使用集合的主要目的是什么？
What is the main purpose of using collections in oracle ?
-
按表索引
Index by tables
嵌套表格
可变大小ARRAY
您能解释上述类型集合之间的区别吗？
Can you please explain the difference between the above types of collections ?
推荐答案
对于嵌套表，它们是最常见的集合形式，因此代表了一个有用的比较基础。
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;
当我们想要存储多个数据实例时， 。经典的例子是使用BULK COLLECT来存储多个记录：
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:
select *
bulk collect into emp_rec_nt
from employees;
这给了我们一个可以循环的数据源;关键是我们可以向后和向前导航，甚至跳到结束或开始，这是我们不能用光标做的事情。嵌套表可以是任何数据类型的集合，包括诸如PL / SQL记录或用户定义类型的复合。
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.
索引表更好地称为文档do）关联数组。这些是具有索引的单个属性的简单集合。嵌套表也有索引，但它们的索引只是行数。使用关联数组，索引可以是有意义的，即源自数据值。因此，它们对于缓存数据值以供将来使用非常有用。索引可以是一个数字，或者（因为9iR2）一个字符串，这可以是非常有用的。例如，这里是一个由雇员标识符索引的关联数据。
An Index By table is better called (as the docs do) an Associative Array . These are simple collections of single attributes with 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.
type emp_sal_aa is table of emp.sql%type
index by emp.empno%type;
l_emp_sales emp_sal_aa;
<语法（自我文档化代码）。该数组的元素可以通过索引值来标识，在这种情况下是EMPNO：
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:
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.
变量数组只是对元素数量有预定义限制的嵌套表。所以也许这个名字是误导的：他们实际上是固定的数组。我们不能使用VArrays，我们不能使用嵌套表（除了约束元素的数量，这是非常罕见的，我们想这样做）。它们声明如下：
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:
type emp_va is varray(14) of emp%rowtype;
emp_rec_va emp_va;
我们可以使用批量收集来填充VArray ...
We can use bulk collect to populate a VArray ...
select *
bulk collect into emp_rec_va
from employees;
但是我们必须确定查询将最多返回 VArray声明中指定的元素。否则SELECT将会抛出ORA-22165。
However we must be certain the query will return at most the number of elements specified in the VArray's declaration. Otherwise the SELECT will hurl ORA-22165.
变量数组没有已知的用例。好吧，这有点苛刻，但几乎所有的时间，你会使用嵌套表。 VArrays对嵌套表的一个很大的优点是它们保证了元素的顺序。因此，如果你必须按照插入它们的顺序取出元素，使用VArray。
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.
PL / SQL文档将整个章节用于集合。 了解详情。
The PL/SQL documentation devotes an entire chapter to collections. Find out more.
这篇关于在Oracle中使用不同类型的PL / SQL集合的目的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
