What is a Collection?
A collection just like an array is an ordered group of elements of the same type. Each elements position is determined by a unique subscript.
Explain collection types. i.e. Index-by tables, Nested tables, Varrays. Simple example for each
Index by tables:- They are similar to hash arrays that allows to search for subscript values using arbitrary numbers and strings.
They can be declared as:
TYPE type_name IS TABLE OF element_type [NOT NULL]
INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];
INDEX BY key_type;
Example:
TYPE studenttyp IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
stud_tab studenttyp;
Nested tables:- they hold random number of elements and use sequential numbers as sub scripts.
They can be declared as:
TYPE type_name IS TABLE OF element_type [NOT NULL];
Example:
TYPE employee_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
Varrays: Holds a fixed number of elements which can be changed in run time.
They can be declared as:
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
Example:
TYPE Calendar IS VARRAY(366) OF DATE;
Nested Tables vs. Associative Arrays
Arrays have an upper bound while nested tables don’t have a bound. Arrays must have consecutive subscripts while nested tables can have both consecutive and non consecutive scripts.
Nested Tables vs. Varrays
Nested tables hold random number of elements while varrays holds fix number of elements. Each varray is stored as a single object while Nested table data is stored out-of-line in a store table.
Explain the methods exist for collections. - EXISTS , COUNT , LIMIT , FIRST and LAST , PRIOR and NEXT , EXTEND, TRIM , DELETE
EXISTS:- if nth element is present in a collection, it returns true.
Example:
IF subject.EXISTS(i) THEN subject(i) := new_subject; END IF;
COUNT:- counts the number of elements in a collection.
Example:
IF subjects.COUNT = 25 THEN ...
LIMIT:-Used in varrays, LIMIT returns the bound value or maximum number of elements the varray can contain.
Example:
IF subjects.LIMIT = 25 THEN ...
FIRST:- Returns the first member of the collection.
Example:
IF subject.FIRST = subject.LAST THEN ...
LAST:- Returns the last member of the collection.
Example:
IF subject.FIRST = subject.LAST THEN ...
PRIOR:-Returns the preceding index number of nth element.
Example:
n := subjects.PRIOR(subjects.FIRST);
PRIOR:-Returns the next or succeeding index number of nth element.
Example:
i := subjects.NEXT(i);
EXTEND:- Used to increase the size of nested table or varray. It either appends one null element to a collection, n null elements or n copies of ith element of a collection.
Example:
subjects.EXTEND(5,1); //appends 5 copies of element 1.
TRIM:- Used to decrease the size of collection by removing one element from end of collection or n elements.
Example:
subjects.TRIM(3);
DELETE:- Deletes the collection elements. It either deletes all elements, nth element from an array, all elements in the range m, n.
Example:
subjects.DELETE(2);
