PLSQL Collections

A collection in PLSQL helps to achieve the Array kind of Programming. Its an ordered group of elements, all of the same type. In a collection, an element has a unique subscript that determines its position in the collection.

PLSQL has 3 types of collections
    Index-by tables
    Varrays
    Nested Tables

Declaration: 

Nested tables
TYPE type_name IS TABLE OF element_type ;
e.g.
TYPE Books IS TABLE OF VARCHAR2(60);

Varrays
TYPE type_name IS VARRAY(size_limit) OF element_type;
e.g.
TYPE Books IS VARRAY(100) OF VARCHAR2(60);

Index-by tables
TYPE type_name IS TABLE OF element_type
   INDEX BY BINARY_INTEGER ;
  
TYPE Books IS TABLE OF VARCHAR2(60)
   INDEX BY VARCHAR2(60) ;
  
Varrays size is fixed at the time of declaration, however the size of Nested tables and Index-by tables is dynamic.


Initialization:
Nested Tables
Declare
    TYPE Books IS TABLE OF VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Varrays
Declare
    TYPE Books IS varray(3) OF VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Index by Table
Declare
    TYPE Books IS table OF VARCHAR2(60)
    inde by VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Using collections in PLSQL.
The best way to implement collections is to use them in For Loops or Bulk collects.
FORALL and BULK COLLECTS can be used for implementing collections in better way

BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses. Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ...

e.g.
DECLARE
   TYPE empntab IS TABLE OF emp.empno%TYPE;
   TYPE enameTab IS TABLE OF emp.ename%TYPE;
   empnum empntab;  -- no need to initialize
   enames enameTab;
BEGIN
   SELECT empno, ename BULK COLLECT INTO empnum, enames FROM emp;
   ...
END;

FORALL
Its used along with collections to perform SQL operations such as Insert, Updates and Deletes.
We can say its spec ail loop to perform Mass operations, a shortcut to implement FOR Loops

e.g.
DECLARE
   TYPE BOOKS IS VARRAY(10) OF VARCHAR2(60);
   var_book BOOKS := BOOKS('Book1','Book2','Book3','Book4','Book5');
BEGIN
   FORALL i IN 1..7  -- bulk-bind only part of varray
      UPDATE BOOK_TAB SET CODE = 1111+1 WHERE NAME = var_book(i);
END;

Other types
RECORD:
TYPE type_name IS RECORD (colname type1, colname2 type2);

e.g.
DECLARE
   TYPE EMPREC IS RECORD (
      EMPNUM    VARCHAR2(60),
      NAME      VARCHAR2(120));
     
      EMP1 EMPREC;
BEGIN
   ...
END;

Collection Methods:

A variety of methods/Functions exist for collections, these can be used to make the implementation
of collection more effective

    EXISTS(n) - Returns TRUE if the specified element exists.

    COUNT - Returns the number of elements in the collection.

    LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.

    FIRST - Returns the index of the first element in the collection.

    LAST - Returns the index of the last element in the collection.

    PRIOR(n) - Returns the index of the element prior to the specified element.

    NEXT(n) - Returns the index of the next element after the specified element.

    EXTEND - Appends a single NULL element to the collection.

    EXTEND(n) - Appends n NULL elements to the collection.

    EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.

    TRIM - Removes a single element from the end of the collection.

    TRIM(n) - Removes n elements from the end of the collection.

    DELETE - Removes all elements from the collection.

    DELETE(n) - Removes element n from the collection.

    DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.