 |
|
Oracle Tips by Burleson |
PL/SQL
Collections
A collection is a group of objects that are stored
in memory for processing. The first type of collection that most
programmers learn about is a linked list; however, there are many
other types of collections. Programming languages such as Visual
Basic, Javaand C++have a rich set of tools to work with collections. This
is because when using a programming language, all objects being
manipulated are in memory. If a developer is working with more than a
few objects, a collection becomes an efficient and convenient way to
hold objects in memory, where they can be processes hundreds of time
faster than if they were on disk storage.
Oracle has provided collections in PL/SQL since
Oracle8 but many PL/SQL developers do not use collections. This is
mostly due to the fact that the PL/SQL developer has something the
Java/C++developer does not, the Oracle database. Storing objects
in the database (even in global temporary tables) incurs a lot of
overhead for the disk I/O. The PL/SQL developer can attain a
significant performance improvement by using collections to hold and
manipulate objects in memory rather than continually inserting and
retrieving them from the database.
Oracle’s PL/SQL collections allow the PL/SQL
programmer to place a set of objects in one defined variable (the
collection). This collection variable can be passed from one
procedure to another. Collection variables are homogenous, meaning
that all the objects in a PL/SQL collection must be of the same data
type. Oracle provides three types of collections: PL/SQL tables,
nested tables, and VARRAYs. The nested tableand
the VARRAYcan be stored in the database table as a column datatype.
The PL/SQL table is a memory only structure.
Before creating a collection the collection type
must be defined. Once the collection type is defined, a variable is
defined as that collection type.
declare
type auth_var is varray(1000) of author%rowtype;
type authnme_var is varray(300) of
author.auth_last_name%type;
a_auth
auth_var;
a_aname authname_var;
begin
The example above defines two VARRAYtypes.
One contains an author table row type, and the other contains
an author last name column type. To use a collection you must
be able to determine information about the collection. Oracle
provides several methods to access and manipulate collections. Let’s
take a closer look at collection methods.
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
