Working with Collections
Part 8 in a series of articles on understanding and
using PL/SQL
ORACLE DATABASE
In the previous article in this series, I showed you how to work with a PL/SQL
record, which is a composite datatype composed of one or more fields. In this article,
I will explore another composite datatype,
the collection. An Oracle PL/SQL collection
is a single-dimensional array; it consists of
one or more elements accessible through an
index value.
Collections are used in some of the most
important performance optimization features of PL/SQL, such as
•;BULK;COLLECT.;SELEC T statements that
retrieve multiple rows with a single fetch,
increasing the speed of data retrieval.
•;FORALL.;Inserts, updates, and deletes that
use collections to change multiple rows of
data very quickly
•;Table;functions. PL/SQL functions that
return collections and can be called in the
FROM clause of a SELECT statement.
You can also use collections to work with
lists of data in your program that are not
stored in database tables.
This article introduces you to collections
and gives you a solid foundation in both collection syntax and features.
I-HUA CHEN
COLLEC TiOn;COnCEp Ts;And
TERminOLOgy
Before exploring collections, it is helpful to
have a common collections vocabulary that
includes the following terms.
index;value. The location of the data in a col-
lection. Index values are usually integers but
for one type of collection can also be strings.
Element. The data stored at a specific index
value in a collection. Elements in a collection
are always of the same type (all of them are
strings, dates, or records). PL/SQL collec-
tions are homogeneous.
sparse. A collection is sparse if there is at
least one index value between the lowest
and highest defined index values that is not
defined. For example, a sparse collection
has an element assigned to index value 1
and another to index value 10 but nothing in
between. The opposite of a sparse collection
is a dense one.
method. A collection method is a procedure
or function that either provides informa-
tion about the collection or changes the
contents of the collection. Methods are
attached to the collection variable with dot
notation (object-oriented syntax), as in
my_collection.FIRST.
TypEs;OF;COLLEC TiOns
Collections were first introduced in Oracle7
Server and have been enhanced in several
ways through the years and across Oracle
Database versions. There are now three types
of collections to choose from, each with its
own set of characteristics and each best
suited to a different circumstance.
Associative;array. The first type of collection available in PL/SQL, this was originally
called a “PL/SQL table” and can be used
only in PL/SQL blocks. Associative arrays can
be sparse or dense and can be indexed by
integer or string.
nested;table. Added in Oracle8 Database, the
nested table can be used in PL/SQL blocks,
in SQL statements, and as the datatype of
columns in tables. Nested tables can be
sparse but are almost always dense. They can
be indexed only by integer. You can use the
MULTISET operator to perform set operations and to perform equality comparisons
on nested tables.
Varray. Added in Oracle8 Database, the
varray (variable-size array) can be used in
PL/SQL blocks, in SQL statements, and as
the datatype of columns in tables. Varrays are
always dense and indexed by integer. When
a varray type is defined, you must specify the
maximum number of elements allowed in a
collection declared with that type.
You will rarely encounter a need for a
varray (How many times do you know in
advance the maximum number of elements you will define in your collection?).
The associative array is the most commonly
used collection type, but nested tables
have some powerful, unique features
(such as MULTISE T operators) that can
simplify the code you need to write to
use your collection.
nEs TEd;TABLE;ExAmpLE
Let’s take a look at the simple example in
Listing 1, which introduces the many aspects
of collections explored later in the article.
When I run the block in Listing 1, I see the
following output:
Veva
Steven
Listing 1 also includes references to the
lines in the code block and descriptions of
how those lines contribute to the nested
table example.
dECLARE;COLLEC TiOn;TypEs
And;VARiABLEs
Before you can declare and use a collection variable, you need to define the type
on which it is based. Oracle Database
predefines several collection types in supplied packages such as DBMS_SQL and
DBMS_UTILIT Y. So if you need, for example,
to declare an associative array of strings
whose maximum length is 32767, you could
write the following:
l_names DBMS_UTILITY.maxname_array;
In most cases, however, you will declare
your own application-specific collection
types. Here are examples of declaring each of
the different types of collections:
