December 17, 2014
Hot Topics:

Oracle Programming with PL/SQL Collections

  • July 12, 2004
  • By Michael Klaene
  • Send Email »
  • More Articles »

Associative Array

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.

Figure 2. Collection Capabilities
Has Ability ToVarrayNested TableAssociative Array
be indexed by non-integerNoNoYes
preserve element orderYesNoNo
be stored in databaseYesYesNo
have elements selected indidually in databaseYesYes--
have elements updated indidually in databaseYesNo--

In addition, the following bullet points can be referred to when deciding what collection best suits a particular solution.

Varray

  • 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

Nested Table

  • 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

Associative Array

  • 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.

Conclusion

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.





Page 4 of 4



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel