Oracle Programming with PL/SQL Collections
PL/SQL applications typically consist of SQL statements intermixed with procedural logic to process data retreived 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.
Just about all 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. As I stated earlier, PL/SQL programs are a good place to make expensive SQL calls but that doesn't mean that we shouldn't try to keep those calls to a minimum.
Oracle provides three types of PL/SQL collections: nested tables, varrays, and associative arrays. We will review each of these collection types in turn. But first, let's take a quick look at traditional approaches to collections programming. At its conclusion, this article will offer some suggestions on when to use each of them.
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.
TYPE book_title_tab IS TABLE OF book.title%TYPE INDEX BY BINARY_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'.
--Define a PL/SQL record type representing a book: TYPE book_rec IS RECORD (title book.title%TYPE, author book.author_last_name%TYPE, year_published published_date.%TYPE)); --define a PL/SQL table containing entries of type book_rec: Type book_rec_tab IS TABLE OF book_rec%TYPE INDEX BY BINARY_INTEGER; my_book_rec book_rec%TYPE; my_book_rec_tab book_rec_tab%TYPE; ... ... my_book_rec := my_book_rec_tab(5); find_authors_books(my_book_rec.author); ... ...
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. However, the Associative Array does contain some enhanced functionality, as we will see.
Oracle Collections Today
Let's fast forward to today and take a good look at Oracle's three collection types: nested tables, varrays, and associative arrays.