COLLECTIONS

COLLECTIONS

Collections are also composite types, in that they allow you to treat several variables as a unit. A collection combines variables of the same type.

TYPES

  • Varrays
  • Nested tables
  • Index – by tables (Associate arrays)

VARRAYS
A varray is datatype very similar to an array. A varray has a fixed limit on its size, specified as part of the declaration. Elements are inserted into varray starting at index 1, up to maximum lenth declared in the varray type. The maximum size of the varray is 2 giga bytes.
Syntax:
Type <type_name> is varray | varying array (<limit>) of <element_type>;

Ex:

 NESTED TABLES

A nested table is thought of a database table which has no limit on its size. Elements are inserted into nested table starting at index 1. The maximum size of the varray is 2 giga bytes.
Syntax:
Type <type_name> is table of <table_type>;

 SET OPERATIONS IN NESTED TABLES

You can perform set operations in the nested tables. You can also perform equality comparisions between nested tables.
Possible operations are

  • UNION
  • UNION DISTINCT
  • INTERSECT
  • EXCEPT ( act like MINUS)

INDEX-BY TABLES

An index-by table has no limit on its size. Elements are inserted into index-by table whose index may start non-sequentially including negative integers.
Syntax:
Type <type_name> is table of <table_type> index by binary_integer;

 DIFFERENCES AMONG COLLECTIONS

1 Varrays has limit, nested tables and index-by tables has no limit.
2 Varrays and nested tables must be initialized before assignment of elements, in index-by tables we can directly assign elements.
3 Varrays and nested tables stored in database, but index-by tables can not.
4 Nested tables and index-by tables are PL/SQL tables, but varrays can not.
5 Keys must be positive in case of nested tables and varrays, in case of index-by tables keys can be positive or negative.
6 Referencing nonexistent elements raises SUBSCRIPT_BEYOND_COUNT in both nested tables and varrays, but in case of index-by tables NO_DATA_FOUND raises.
7 Keys are sequential in both nested tables and varrays, non-sequential in index-by tables.
8 Individual indexes can be deleted in both nested tables and index-by tables, but in varrays cannot.
9 Individual indexes can be trimmed in both nested tables and varrays, but in index-by tables cannot.
10 Individual indexes can be extended in both nested tables and varrays, but in index-by tables cannot.

MULTILEVEL COLLECTIONS
Collections of more than one dimension which is a collection of collections, known as multilevel collections.
Syntax:
Type <type_name1> is table of <table_type> index by binary_integer;
Type <type_name2> is varray(<limit>) | table | of <type_name1> | index by
binary_integer;

 OBJECTS USED IN THE EXAMPLES

SQL> select * from student;
SNO SNAME SMARKS
———- ————– ———-
1 saketh 100
2 srinu 200
3 divya 300
4 manogni 400
SQL> create or replace type addr as object(hno number(2),city varchar(10));/
SQL> select * from employ;
ENAME JOB ADDRESS(HNO, CITY)
———- ———- —————————–
Ranjit clerk ADDR(11, ‘hyd’)
Satish manager ADDR(22, ‘bang’)
Srinu engineer ADDR(33, ‘kochi’)

Leave a Reply