A collection is an ordered group of elements which have the same data type and we use subscripts to actually access those elements.
There are three types of collections provided by Oracle:
- Index-by Tables / Associative Arrays
- Nested Table
- Variable size array / Varray
Index by Table / Associative Array
Each stored data unit is in the form of KEY-VALUE pair. The keys are unique and it can be either a string or an integer.
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY sucscript_type;
table_name type_name;
Below is an example showing how to use associatibe array:
TYPE salary IS TABLE OF NUMBER INDEX BY varchar2(20);
salary_list salary;
--Adding elements to this ass. array
salary_list('ram') = 15000;
salary_list('lakshman') = 7050;
salary_list('bharat') = 35000;
salary_list('shatrugan') = 5000;
--Now using the collection elements
name := salary_list.FIRST;
WHILE name IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE ('Salary of '||name||' is '||TO_CHAR(salary_list(name));
name := salary_list.NEXT(name);
END LOOP;
END;
/
Elements of a index-by table can be %ROWTYPE data of a database table; or it could be %TYPE of a single column of the table.
NESTED TABLES
Its like a one-dimensional array where the elements are stored with their subscript.
But its not conventional type of array as the no. of elements in the nested table is unbound compared to normal array. And its also not as dense as arrays are because the elements keep on changing making it sparse.
The syntax of nested tables is similar to that of the index-by collection except the fact that nested tables do not use INDEX BY clause.
Nested tables can be stored in the database tables where as index-by doesn't.
