What Is a Collection?

A collection is an ordered group of elements, all of the same type.(一组有序的元素结合还有相同的元素类型) It is a general concept that encompasses lists, arrays, and other datatypes used in classic

programming algorithms. Each element is addressed by a unique subscript.(通过唯一的下标去索引它)

PL/SQL offers these collection types:

1、Associative arrays(联合数组), also known as index-by tables, let you look up elements using arbitrary(任意的) numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)(允许使用任意的数字或者字符串作为下标索引的值，它类似于其他编程语言中的hash table的概念) 

2、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.(Nested tables 可以作为数据类型定义为一个表的某一列的类型)。

注意:Associative arrays diff with Nested tables：前者的下标可以是数字，也可以是字符串，他不可以使用在SQL中，后者的下标索引只能是数字，并且是序列化的，自增类型的。它可以使用在SQL中。前者的初始化可以直接使用任意的下表进行初始化，但是后者不许使用extend方法进行初始化。(还有好多的差别)

  Varrays (short for variable-size arrays：可变大小的数组) hold a fixed number of elements (although

you can change the number of elements at runtime：含有一个固定大小的元素数量。可以在运行的时间改变元素的个数，但是他不可以超过元素的最大数量限制(limit)). 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(可以使用SQL进行操作), but with less flexibility than nested tables(但是没有nested table 方便).

注意:

To look up data that is more complex than single values, you can store PL/SQL

records or SQL object types in collections. Nested tables and varrays can also be

attributes of object types.(可以在集合中存储RECODE或者object数据类型。嵌套表和varrays也可以作为object的属性)(OBJECT 类型的参考:手册的第一章:)

三种类型的区别对比:

 

Understanding Nested Tables

 

PL/SQL nested tables represent sets of values. You can think of them as

one-dimensional arrays(可以认为是无下标限制的一维数组) with no upper bound. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables.(也可以定义多维数组)

Within the database, nested tables are column types that hold sets of values. Oracle

stores the rows of a nested table in no particular order. When you retrieve a nested

table from the database into a PL/SQL variable, the rows are given consecutive

subscripts starting at 1. That gives you array-like access to individual rows.(他也可以作为数据库表的一列属性，默认存储是无序的，但是查询上来的值，下标是从1开始的。)

Nested tables differ from arrays in two important ways:

    1. Nested tables are unbounded, while arrays have a fixed upper bound。 The size of a nested table can increase dynamically.(Nested tables 无下标界限，但是varrays有一个固定的下标界限，nested table下标可以自动增长。)

    2. Nested tables might not have consecutive subscripts(Nested tables可能没有连续的下标，他是稀疏的，可以删除内部元素), while arrays are always dense (have consecutive subscripts但是varray有连续的下标，他是稠密的). Initially, nested tables are dense, but they can become sparse (have nonconsecutive subscripts). You can delete elements from a nested table using the built-in procedure DELETE(初始化的时候，它是稠密的，下标是连续的，当时当调用delete方法删除内部的元素后，他变为稀疏的，下标不再连续). The built-in function NEXT lets

you iterate over all the subscripts of a nested table, even if the sequence has gaps.(可以使用nest方法遍历nest table中的每一个元素)：

 

 Understanding Associative Arrays (Index-By Tables)

   Associative arrays are sets of key-value pairs, where each key is unique and is used tolocate a corresponding value in the array. The key can be an integer or a string.(是一组键值对应的集合，每一个key都是唯一的，用来确定集合中的元素的值，key可以是整型和字符串)

   Assigning a value using a key for the first time adds that key to the associative array.

Subsequent assignments using the same key update the same entry. It is important to

choose a key that is unique. For example, key values might come from the primary key

of a database table, from a numeric hash function, or from concatenating strings to

form a unique string value.(选择唯一的键值是非常重要的，一般来在一个表的主键，一个数字函数，假如之前出现了一个键值，在出现相同的，会出现值的覆盖)

Associative arrays help you represent data sets of arbitrary size, with fast lookup for  an individual element without knowing its position within the array and without  having to loop through all the array elements(不需要知道它的具体位置，就可以索引他的值，类使用于map的键值索引功能). It is like a simple version of a SQL table Varray Grades where you can retrieve values based on the primary key. For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.(他节省了空间)。

Because associative arrays are intended for temporary data rather than storing persistent data(不是持久化的数据), you cannot use them with SQL statements such as INSERT and SELECT INTO(不可以进行insert和select into 操作). You can make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body(你可以把其声明到包中，并且赋值，保证他在数据库的session是有效的。)

PL/SQL Record

 

A record is a group of related data items stored in fields, each with its own name and

datatype. You can think of a record as a variable that can hold a table row, or some

columns from a table row. The fields correspond to table columns.(你可以认为它是一个变量，用来存取表的一行输数据，或者是表中的一些列)。

The %ROWTYPE attribute lets you declare a record that represents a row in a database

table( %ROWTYPE表示声明了一个记录代表数据库表中的一行), without listing all the columns. Your code keeps working even after columns are added to the table. If you want to represent a subset of columns in a table, or columns from different tables, you can define a view or declare a cursor to select the right

columns and do any necessary joins, and then apply %ROWTYPE to the view or cursor.(假如想从不同的表中取值，可以声明一个视图，选择合适的列，或者一个游标，来使用他的%ROWTYPE属性)。

注意：假如使用%ROWTYPE声明一个RECORD，则不需要TYPE XXX IS定义语句，可以直接使用

rec3 employees%ROWTYPE;

PL/SQL Records into the Database(INSERT/UPDATE/DELETE)

INSERT:(可以使用FORALL语句)