Collections - Introduction
In
PL/SQL Version 2, Oracle introduced the TABLE data type as a way of storing
singly dimensioned sparse arrays in PL/SQL. PL/SQL8 introduces two new
collection structures that have a wide range of new uses. These structures are
nested tables and variable-size arrays (VARRAYs). Like PL/SQL tables, these
structures can also be used in PL/SQL programs. But what is new is the ability
to use the new collections as the datatypes of fields in conventional tables
and attributes of objects. While not an exhaustive implementation of
user-defined datatypes, collections offer rich new physical (and, by extension,
logical) design opportunities for Oracle practitioners.
Types of
Collections
- PL/SQL tables are singly
dimensioned, unbounded, sparse collections of homogeneous elements and are
available only in PL/SQL. These are called index-by tables in version 8i
and associative arrays in version 9i.
- Nested tables are also
singly dimensioned, unbounded collections of homogeneous elements. They are
initially dense but can become sparse through deletions. Nested tables are
available in both PL/SQL and the database (for example, as a column in a
table).
- VARRAYs, like the
other two collection types, are also singly dimensioned collections of
homogeneous elements. However, they are always bounded and never sparse.
Like nested tables, they can be used in PL/SQL and in the database. Unlike
nested tables, when you store and retrieve a VARRAY, its element order is
preserved. Using a nested table or
VARRAY, you can store and retrieve nonatomic data in a single column.
For example, the employee table used by the HR department could store the
date of birth for each employee’s dependents in a single column. This is
illustrated in the table below:
Table 1: Storing a Nonatomic Column of
Dependents in a Table of Employees
|
Id (NUMBER)
|
Name (VARCHAR2)
|
Dependents_ages
(Dependent_birthdate_t)
|
|
10010
|
Zaphod
Beeblebrox
4-JUL-1977
22-MAR-2021
|
12-JAN-1763
|
|
10020
|
Molly
Squiggly
15-NOV-1968
|
15-NOV-1968
|
|
10030
|
Joseph
Josephs
|
|
|
10040
|
Cepheus
Usrbin
9-AUG-1996
|
27-JUN-1995
|
|
1
|
9-JUN-1997
|
|
|
10050
|
Deirdre
Quattlebaum
|
21-SEP-1997
|
It’s
not difficult to create such a table. First we define the collection type:
CREATE TYPE
Dependent_birthdate_t AS VARRAY(10) OF DATE;
Now
we can use it in the table definition:
CREATE TABLE employees (
id NUMBER,
name VARCHAR2(50),
...other columns...,
Dependents_ages Dependent_birthdate_t
);
We
can populate this table using the following INSERT syntax, which relies on the
type’s default constructor to transform a list of dates into values of the
proper data type:
INSERT INTO employees VALUES
(42, 'Zaphod Beeblebrox', ...,
Dependent_birthdate_t( '12-JAN-1763',
'4-JUL-1977', '22-MAR-2021'));
While
most of us have been trained to view nonatomic data as a design flaw, in some
situations (for those in which you don’t need to scan the contents of all the
values in all the rows), theoreticians and practitioners alike consider
nonatomic data to be perfectly acceptable. Some database designers have
believed for years that the large percentage of nonatomic data inherent in
their applications demands a nonrelational solution. Setting aside theoretical
arguments about "natural" data representations, Oracle collections
allow to pass an entire collection between the database and PL/SQL using a
single fetch. This feature alone could have significant positive impact on
application performance.
As
we’ve mentioned, within PL/SQL both nested tables and VARRAYs are ordered
collections of homogeneous elements. Both bear some resemblance to the PL/SQL
Version 2 table datatype, the elder member of the "collection"
family. The new types are also singly dimensioned arrays, but they differ in
areas such as sparseness (not exactly), how they’re initialized (via a
constructor), and whether they can be null (yes).
One chief
difference between nested tables and VARRAYs surfaces when we use them as
column datatypes. Although using a VARRAY as a column’s datatype can achieve
much the same result as a nested table, VARRAY data must be predeclared to be
of a maximum size, and is actually stored "inline" with the rest of
the table’s data.
Nested tables,
by contrast, are stored in special auxiliary tables called store tables, and
there is no pre-set limit on how large they can grow. For this reason, Oracle Corporation says that
VARRAY columns are intended for "small" arrays, and that nested
tables are appropriate for "large" arrays.
The
old Version 2 table datatype is now called an index-by table, in honor of the
INDEX BY BINARY_INTEGER syntax required when declaring such a type. Despite the
many benefits of the new collection types, index-by tables have one important
unique feature: initial sparseness. The table below illustrates many of the
additional differences among index-by tables and the new collection types.
Table 2: Oracle Collection Types
Comparison
|
Characteristic Dimensionality
|
Index-By Table Single
|
Nested Table Single
|
VARRAY Single
|
|
Usable
in SQL?
|
No
|
Yes
|
Yes
|
|
Usable
as column datatype in a table?
|
No
|
Yes;
data stored "out of line" (in separate table)
|
Yes;
data stored "in of line" (in same table)
|
|
Uninitialized
state
|
Empty
(cannot be null); elements undefined
|
Atomically
null; illegal to reference elements
|
Atomically
null; illegal to reference elements
|
|
Initialization
|
Automatic,
when declared
|
Via
constructor, fetch, or assignment. In PL/SQL, elements referenced via
BINARY_INTEGER (-2,147,483,647 .. 2,147,483,647) and positive integer between
1 and 2,147,483,647
|
Via
constructor, fetch, or assignment. Positive integer between 1 and
2,147,483,647
|
|
Sparse?
|
Yes
|
Initially,
no; after deletions, yes
|
No
|
|
Bounded?
|
No
|
Can
be extended
|
Yes
|
|
Can
assign value to any element at any time?
|
Yes
|
No;
may need to EXTEND first
|
No;
may need to EXTEND first, and cannot EXTEND past upper bound
|
|
Means
of extending
|
Assign
value to element with a new subscript
|
Use
built-in EXTEND procedure (or TRIM to condense), with no predefined maximum
|
EXTEND
(or TRIM), but only up to declared maximum size
|
|
Can
be compared for equality?
|
No
|
No
|
No
|
|
Retains
ordering and subscripts when stored in and retrieved from database?
|
N/A
|
No
|
Yes
|
Click
here
for suggestions on which construct to use.
Collection Terminology
Collection
A term which can
have several different meanings:
·
A
nested table, index-by table, or VARRAY datatype
·
A
PL/SQL variable of type nested table, index-by table, or VARRAY
·
A
table column of type nested table or VARRAY
Inner
table
The
"enclosed" collection that is implemented as a column in a table;
also known as a "nested table column"
Nested
table
Unfortunately,
the term "nested table" can be a bit misleading. A nested table, when
declared and used in PL/SQL, is not nested at all! It is instead fairly similar
to an array. Even when you use a nested table as a table column, in Oracle 8.0
you can only nest these structures to a single level. That is, your column
cannot consist of a nested table of nested tables.
One-dimensional
(or
single-dimensional) Essentially, a collection has just a single column of
information in each row, and is in this way similar to a one-dimensional array.
You cannot define a collection so that it can be referenced as follows:
my_table (10, 44)
This is a two-dimensional structure and not
currently supported.
Outer
table
A term referring
to the "enclosing" table in which you have used a nested table or
VARRAY as a column’s datatype
Store
table
The physical
table which Oracle creates to hold values of the inner table
VARRAY
"Variable-size array" is also a deceptive
name; one might assume, based on the fact that it is supposed to be
"variable size," that it can be arbitrarily extended; quite the
opposite is true. Although a VARRAY can have a variable number of elements,
this number can never exceed the limit that you define when you create the type
