Collections

Collection -- is an Ordered group of elements, all of the same type.  Same as Single Dimensional arrays in other languages. Can define Multi-Dimensional arrays using Nested Collections.

USE ...
1) Collection manipulation is generally much faster than using SQL to modify the contents of tables.
2) Enable to do BULK operations (BULK COLLECT, FORALL).
3) Memory for collections is allocated from PGA rather than SGA. Accessing PGA is faster than SGA.

Types......
Associative Arrays  -- or PLSQL Tables, or Index-by Tables. This is the First collection introduced in Oracle 7.
An unbounded set of key-value pairs.
Can access elements without knowing their positions in array and without traversing the array.
can be used in only in PLSQL.
TYPE col IS TABLE OF ele_type INDEX BY index_type;
It can be Sparse (Can have gaps) : Can populate elements in non-consecutive index values.
You can have any type including, scalar types, boolean, Object Types, Other Collections.
Cannot have a TABLE OF cursor variables or exceptions.
NO index limit, and can have any value.
MULTISET operators connot be used against Associative Arrays.
Nested Tables       --
It can be unorder or duplicate. Integer index.
can be used in PLSQL, and in SQL for a column type. As part of a TABLE(collection) fuction and join with database tables.
Must Initialize it before using it with constructor. No need to initialize if you populate by BULK COLLECT query.
x nes_col := nes_col();
x nes_col := nes_col(10,20,30);
No Limit on number of elements
Always dense initially, but can become sparse after deletes.
MULTISET operators allow set-level operations on Nested Tables.
Must EXTEND to make room for new elements.
TYPE col IS TABLE OF ele_type;

Varrys              -- can be used in PLSQL, SQL.  As part of a TABLE(collection) fuction and join with database tables.
Are always Dense, so No gaps in between, so you cannot delete middle values, only last values can be TRIM
TYPE vary IS VARRAY (10) OF ele_type;
Variable Size Array, which has upper bound. That upper bound can be adjusted.
Must Initialize it before using it with constructor. No need to initialize if you populate by BULK COLLECT query.
Can be defined as Schema level.
Ordered elements

Schema-level Types :  Defined in the Schema and independent of any PLSQL program unit. It will work for Nested tables and Varrys.
CREATE OR REPLACE TYPE col IS TABLE OF VARCHAR2(100);
Defining Types : l_names pkg.col_type;
l_names col_type;
l_name Schema.col_type;

Collection Methods :

COUNT   : Number of rows currently defined in collection.
EXISTS   : TRUE if the specified row is defined.
FIRST/LAST :
LIMIT      : Max number of elements allowed in a varray.
DELETE  : Deletes one or more rows from Associative Arrays or Nested Tables. We cannot use for VARRAY.
EXTEND  : Adds rows to the end of a Nested table or VARRAY.
TRIM        : Removes 1 or multiple elements from a VARRAY and Nested Tables.

Loops :

FOR ....use when iterate through every element between the low and high index values. Not to use with sparse collections.
WHOLE ..... Best to fit for Sparse collections.

ASSOCIATIVE ARRAY
Declared only in PL/SQL code - no "CREATE OR REPLACE TYPE". SQL doesn't know anything about them.
No initialisation or extending required - just assign values to any arbitrary element, doesn't even have to be consecutive.
You can choose what to "index by" - PLS_INTEGER,  BINARY_INTEGER2 or VARCHAR2.
No constructor - you must assign values explicitly.
Can't treat as a table in queries, e.g. you cannot SELECT * FROM TABLE(myarray)

NESTED TABLE & VARRAY
Declared either in PL/SQL code or with "CREATE OR REPLACE TYPE".
Must be initialised before use, e.g. myarray mytype := mytype();
Have constructors - you can assign values using mytype('x','y','z');
Must be extended as required, e.g. myarray.EXTEND; to add each array element.
Can treat as a table in queries e.g. SELECT * FROM TABLE(myarray) (if created in SQL with CREATE TYPE).

varrays are a good choice when:
The number of elements is known in advance.
The elements are usually all accessed in sequence.

Nested tables are a good choice when:
The index values are not consecutive.
There is no set number of index values. However, a maximum limit is imposed. See "Referencing Collection Elements".
You need to delete or update some elements, but not all the elements at once.
You would usually create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

Dense    -- No Gap between index values.
Sparse   -- Gaps between index values.

Translate between a collection and relational table :
TABLE : Collection --> Relational Table
MULTISET : Relational Table --> Collection