PL/SQL Collections

This is one of those topics i used to dread when i started out as a database developer, not anymore. But still from time to time i need to refresh my memory when it comes to collections:

I recently started reading a book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" and it describes collections in such a way that i have to make a post of it. It is precise, with an example and perfect read for me:

Below are a few pointers that i usually like to remember:

Associative Arrays (TABLE OF)

• AKA Index by tables
• Of course use an index by BINARY_INTEGER or PLS_INTEGER or VARCHAR2
• Sparse collection
• Need not be consecutive
• Unbounded - no upper boundary
• Collection is extended by assigning values to non existing index values
• Data can be deleted
• Start with FIRST method to access data

Nested Tables (TABLE OF)

• No index
• Unbounded colleciton
• Dense collection when creating collection
• Data can be deleted
• NEXT and PRIOR (?) helps access collection elements

VARRAYS (VARRAY OF)

• No index
• Bounded collection so cannot be extended above the specified limit
• Data cannot be deleted
• Dense data collection
• Consecutive data
• Start with FIRST method to access data

Methods that can be used with collections. Not all methods can be used with all collections:

• EXISTS - exists(v) - returns boolean
• DELETE - delete, delete(v1) and delete(v1,v8) - removes data
• COUNT - returns number
• TRIM - trim and trim (v) - removes data
• LIMIT - returns number
• EXTEND - extend, extend(v1) and extend(v1,v8) - adds NULL elements
• FIRST - returns index of first element
• LAST - returns index of last element
• PRIOR - prior(v) - returns index of prior element
• NEXT - next(v) - returns index of next element

Hope this helps....