PL/SQL Collections （Nested Tables)

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 familiar datatypes. Each element has a unique subscript that determines its position in the collection.

Understanding Nested Tables

Within the database, nested tables can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows.

PL/SQL nested tables are like one-dimensional arrays. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables.

Define a Collection of Nested Tables

For nested tables, use the syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL];

type_name is a type specifier used later to declare collections. For nested tables declared within PL/SQL,element_type is any PL/SQL datatype except:

Initializing and Referencing Collections

Until you initialize it, a nested table or varray is atomically null: the collection itself is null, not its elements. To initialize a nested table or varray, you use aconstructor, a system-defined function with the same name as the collection type. This function "constructs" collections from the elements passed to it.

You must explicitly call a constructor for each varray and nested table variable. (Associative arrays, the third kind of collection, do not use constructors.) Constructor calls are allowed wherever function calls are allowed.

Example: Constructor for a Nested Table

In the following example, you pass multiple elements to the constructorCourseList(), which returns a nested table containing those elements:

DECLARE   TYPE CourseList IS TABLE OF VARCHAR2(16);   my_courses CourseList;BEGIN   my_courses :=      CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100');END;

Example

DECLARE   TYPE table_varchar IS TABLE OF VARCHAR2 (30);   old_table_varchar   table_varchar;   new_table_varchar   table_varchar;BEGIN   old_table_varchar :=      table_varchar ('AA',                     'BB');   new_table_varchar :=      table_varchar ('CC',                     'DD');   FOR i IN old_table_varchar.FIRST .. old_table_varchar.LAST   LOOP      DBMS_OUTPUT.put_line (old_table_varchar (i) || '-' || new_table_varchar (i));      UPDATE TEMP00         SET COL01 = new_table_varchar (i)       WHERE COL01 = old_table_varchar (i);   END LOOP;EXCEPTION   WHEN OTHERS   THEN      ROLLBACK;      DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);END;/

参考：http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm

参考：http://www.adp-gmbh.ch/ora/plsql/coll/constructor.html