PL/SQL applications typically consist of SQL statements intermixed with procedural logic to process data retrieved from the database. If compiled as a stored procedure, your PL/SQL code will reside on the server, an ideal place for programs that require intensive database interaction. Having said that, anytime a software application links up with a database, there is a performance price to be paid. Not only that, programs that continually switch off between code and SQL can become quite complex. PL/SQL collections can address some of these concerns.
All the modern programming languages provide support for collections. A collection can be loosely defined as a group of ordered elements, all of the same type, that allows programmatic access to its elements through an index. Commonly used collection types used in the programming world include arrays, maps, and lists.
Storing elements in a collection can provide a number of advantages. For starters, collections can help to simplify code. If you need to process a number of items of a similar type, storing these items in a collection will allow you to loop through each element with ease, referencing each one by an index. In addition, most languages define collection types that contain built-in methods to operate on the collection. For example, a method might allow you to remove all elements from a collection in a single command.
Probably the biggest advantage a collection can provide is improved application performance. Developers utilize collections to 'cache' static data that needs to be regularly accessed. This results in reduced calls to a database. PL/SQL programs are a good place to make expensive SQL calls but that doesn't mean that you should not try to minimize those calls.
Oracle uses collections in PL/SQL the same way other languages use arrays. It is a group of elements that have similar datatypes. Each element is identified by a unique subscript that represents its position in the collection. Oracle provides three basic collections, each with an assortment of methods.
? PL/SQL tables are singly dimensioned, unbounded, sparse collections of homogeneous elements and are available only in PL/SQL. These are now called index−by tables.
? 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.
With the release of Oracle 7, Oracle introduced the PL/SQL Table. By using PL/SQL Tables, it was possible to create a collection of items, all of the same type, indexed by an integer.
The only way to access the elements of a PL/SQL Table was through its numeric index. Still, it was the first construct that gave PL/SQL developers array-like access to data.
PL/SQL tables were often combined with PL/SQL Records. By creating a PL/SQL Record, developers could define a composite type that allowed you to group items of varying type together. Combining PL/SQL Tables and Records together was often referred to as a 'PL/SQL Table of Records'.
In version 8, Oracle introduced two collection types, Nested Tables and Varrays. At this time, the PL/SQL Table was renamed to 'index-by table'. As of Oracle 9i, PL/SQL Tables (index-by tables) have again been renamed to Associative Arrays. The Associative Array functions much the same way the PL/SQL Table of old did.
Index-By Tables / Associative Arrays
The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive. Assigning values to an element using an index value that does not currently exist extends the collection.
The Varray is short for Variable Array. A Varray stores elements of the same type in the order in which they are added. The number of elements in a Varray must be known at the time of its declaration. In other words, a Varray has a fixed lower and upper bounds, making it most similar to collection types from other programming languages. Once it is created and populated, each element can be accessed by a numeric index.
When a table contains a Varray type, its data is included in-line, with the rest of the table's data. When a Varray datatype is selected from a database table, all elements are retrieved. The Varray is ideal for storing fixed values that will be processed collectively. It is not possible to perform inserts, updates, and deletes on the individual elements in a Varray. If you require your collection to be stored in the database but would like the flexibility to manipulate elements individually, Nested Tables are a better solution.
Nested table collections are an extension of the index-by tables. The main difference between the two is that nested tables can be stored in a database column but index-by tables cannot. In addition some DML operations are possible on nested tables when they are stored in the database. During creation the collection must be dense, having consecutive subscripts for the elements. Once created elements can be deleted using the DELETE method to make the collection sparse. The NEXT method overcomes the problems of traversing sparse collections.
Nested Tables, like the Varray, can be stored in a relational table as well as function as a PL/SQL program variable.
The 'IS TABLE OF' syntax was also used when declaring a PL/SQL Table. However, this declaration omits the 'INDEX BY BINARY_INTEGER' clause required by the former type. Note that we have not specified the size of the collection. This is because Nested Tables, unlike the Varray, require no size specification. In other words, they are unbound.
A Varray's contents are stored in the same table as the other columns' data (unless the collection is exceedingly large, then Oracle stores it in a BLOB, but still within the same tablespace). With Nested Tables, a separate database table will store the data. This table is specified following the 'STORE AS' clause. If a database table has more than one Nested Table type, the same storage table will store data for all the Nested Tables on that parent table. These storage tables contain a column called NESTED_TABLE_ID that allows the parent table to reference a row's nested table data.
Both Nested Tables and Varrays allow you to use SQL to select individual elements from a collection. However, Nested Tables have an advantage over Varrays in that they allow for inserts, updates, and deletes on individual elements. The Varray type does not because Varray data is stored as one single, delimited piece of data within the database.
When to Use What
If you're new to PL/SQL collections, you may have a fair understanding of their mechanics by this point, but are uncertain when to use a particular type. The table below summarizes each collection's capabilities.
Has Ability To Varray Nested Table Associative Array
be indexed by non-integer No No Yes
preserve element order Yes No No
be stored in database Yes Yes No
have elements selected individually in database Yes Yes --
Have elements updated individually in database Yes No --
In addition, the following bullet points can be referred to when deciding what collection best suits a particular solution.
? Use to preserve ordered list
? Use when working with a fixed set, with a known number of entries
? Use when you need to store in the database and operate on the Collection as a whole
? Use when working with an unbounded list that needs to increase dynamically
? Use when you need to store in the database and operate on elements individually
? Use when there is no need to store the Collection in the database. Its speed and indexing flexibility make it ideal for internal application use.
A variety of methods exist for collections, but not all are relevant for every collection type:
? EXISTS(n) - Returns TRUE if the specified element exists.
? COUNT - Returns the number of elements in the collection.
? LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.
? FIRST - Returns the index of the first element in the collection.
? LAST - Returns the index of the last element in the collection.
? PRIOR(n) - Returns the index of the element prior to the specified element.
? NEXT(n) - Returns the index of the next element after the specified element.
? EXTEND - Appends a single null element to the collection.
? EXTEND(n) - Appends n null elements to the collection.
? EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.
? TRIM - Removes a single element from the end of the collection.
? TRIM(n) - Removes n elements from the end of the collection.
? DELETE - Removes all elements from the collection.
? DELETE(n) - Removes element n from the collection.
? DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.
CREATE OR REPLACE TYPE Address AS OBJECT
( Street VARCHAR2(80),
Zip VARCHAR2(10) );
Create Type Addlist as VARRAY(3) of Address;
Create table My_Varray_Table(Nm Varchar2(10), My_Add Addlist);
Insert into My_Varray_Table values('Ram',Addlist(Address('Andheri','Mumbai','MH','401303'),
Select * From My_Varray_Table;
Select p.nm, a.*
from My_Varray_Table p, Table(p.My_Add) a
CREATE OR REPLACE TYPE Address AS OBJECT
( Street VARCHAR2(80),
Zip VARCHAR2(10) );
CREATE OR REPLACE TYPE AddressList AS TABLE OF Address;
Create table my_nested_table (pname varchar2(20), my_address AddressList)
NESTED TABLE my_address STORE AS Add_stor_tab
Insert into My_nested_table
Select * from my_nested_table;
Select p.pname, a.*
from my_nested_table p, table(p.my_address) a;