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