Oracle Programming with PL/SQL Collections, Page 4
Earlier, we reviewed the definition of a PL/SQL Table (also know as an index-by table). The statement
TYPE book_title_tab IS TABLE OF book.title%TYPE INDEX BY BINARY_INTEGER; book_titles book_title_tab;
defines a collection of book titles, accessible by a numeric index. Although it is feasible to locate an element by its numeric index, the limitation to this approach is that the value we have to search by is often not an integer.
SELECT title FROM book; TITLE ------------------------------ A Farewell to Arms For Whom the Bell Tolls The Sun Also Rises
Above are values from the title column of the book table. If we needed to remove an entry, given only the book title, we would have to search the entire collection in a somewhat inefficient manner. The following is code illustrates this:
FOR cursor_column IN book_titles.FIRST..book_titles.LAST LOOP IF book_titles(cursor_column) = 'A Farewell to Arms' THEN book_titles.DELETE(cursor_column); END IF; END LOOP;
With Associative Arrays, it is now possible to index by the title of the book. In fact, there are numerous different indexing options, including by VARCHAR2, using the %TYPE keyword, and more. This is a improvement over indexing everything by an integer then having to shuffle through entries to find what you're looking for. Now, if we want to remove the book A Farewell to Arms, we can use an Associative Array:
DECLARE TYPE book_title_tab IS TABLE OF book.title%TYPE INDEX BY book.title%TYPE; book_titles book_title_tab; BEGIN book_titles.DELETE('A Farewell to Arms'); END;
By using an Associative Array of book.title%TYPE, we accomplish our mission in one line, without the need to loop through the set. The main drawback to the Associative Array type is that, like the PL/SQL Table type before it, you are not able to store them in the database. They are strictly for internal use in PL/SQL applications. If this is all you require of a collection, the Associative Array's indexing flexibility make it a good choice.
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. Table 2 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 indidually in database||Yes||Yes||--|
|have elements updated indidually 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.
Oracle PL/SQL is not a difficult language to learn. However, like all good programming languages, there are many things we can do to maximize efficiency and minimize complexity. Given PL/SQL's power to interact with the database, it can be tempting to simply to fall into the habit of making excessive database calls to do our work. Collections can help you build simpler, faster Oracle database applications, the goal of every good PL/SQL developer.
If you are a member of the Oracle Technology Network, you can read the following chapter in the PL/SQL User's Guide and Reference for additional information about PL/SQL Collections.
About the Author
Michael Klaene is a Senior Consultant with Sogeti LLC. He has spent over 9 years in Information Technology and is an experienced Systems Analyst, delivering solutions that involve numerous technologies, such as J2EE and .NET.