Oracle in a Nutshell

Collection Datatypes

In addition to the scalar datatypes described in the previous sections, there are a number of additional datatypes that can hold or point to a collection of data:

RECORD

Used only in PL/SQL. Composed of fields with datatypes, just like a table in the database. You can insert or update RECORDs into matching tables in the database or use the RETURNING clause to retrieve values from a table after an INSERT, UPDATE, or DELETE statement.

TABLE

A nested table. Cannot be of datatype REF CURSOR in PL/SQL or the PL/SQL datatypes, such as BINARY_INTEGER or POSITIVE, when stored in the database. You can delete individual values in a TABLE. When stored in the database, preserves order as stored.

In PL/SQL, you can use an INDEX BY clause, which adds an index that can be used to access the data in the table. The INDEX BY clause can specify BINARY_INTEGER, PLS_INTEGER, a VARCHAR2(n), or a key type.

A TABLE can be part of an object type, which would make it a nested table. For more information on object types, see Chapter 7.

VARRAY(n)

An array of values with n values. You reference individual values in a VARRAY with a subscript. You cannot delete individual values in a VARRAY, because values must be consecutive. When stored in the database, does not preserve order as stored.