PL/SQL collections

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);
PL/SQL record data type
PL/SQL record data type - What is a PL/SQL Record data type?, Define and declare Records, Different Types of Records - Table-based, Cursor-based, Programmer-defined, Benefits of using Records, Guidelines for using Records...
PL/SQL cursors
PL/SQL cursors - What are Cursors?, Types of cursors in PL/SQL - Implicit cursors, Explicit cursors, What are cursor variables?, Significance of SELECT FOR UPDATE clause, Significance of WHERE CURRENT OF clause...
PL/SQL error handling
PL/SQL error handling - Overview of PL/SQL Error Handling, Advantages of PL/SQL Exceptions, commonly used Predefined PL/SQL Exceptions, What is user-defined exception?, how PL/SQL exceptions are raised, reraising a PL/SQL Exception, how to handle raised PL/SQL exceptions...
Post your comment

You may like