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:TYPEtype_name
IS TABLE OFelement_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