PL/SQL - Collections

Collection:

• Collection is a composite datatype like Record.
• An Oracle PL/SQL collection is a single dimensional array
• It consist of one more elements accessible through an index value.
• All the elements have the same data type.

Advantages:

Collections are used in some of the important performance optimization features og PL/SQL,

• BULK collect : select statements that retrieve multiple rows with a single fetch.
• FORALL : inserts, updates and deletes that use collections to change multiple rows of data very quickly
• Table functions : functions that return collections and can be called in the FORM clause of a select statement.

Sparse vs dense collection:

If a collection contains all the elements is called dense. And a collection is sparse if there is atleast one index value between the lowest and highest defined index values that is not defined.

Collection methods:

A collection method is a procedure or function that either provide information about the collection or changes the contents of the collection. Methods are attached to the collection variable with dot notation, for example, my_collection.FIRST.

Types of collections:

• Associative Array or index by tables
• Nested tables
• VARRAY

Associative array:

This type of collection is indexed using BINARY_INTEGER values or VARCHAR2 values, which dont need to be consecutive. This were originally called as PL/SQL tables.

DECLARE

TYPE customer_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;

customer_table customer_type;

v_idx number;

BEGIN

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

customer_table(6):= 'Sri';

--delete 3rd element

customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

Nested Tables:

• Nested table can be stored in a database.
• It can be sparse but are almost always dense.
• They can be indexed only by integer.
• We can use the MULTISET operator to perfoem set operations and to perform equality comparisons on nested tables.
• The elements entry must be sequential.

DECLARE

TYPE customer_type IS TABLE OF VARCHAR2(100);

-- initialize the collection

customer_table customer_type := customer_type();

v_idx number;

BEGIN

--you have to extend before using the table

customer_table.extend(4);

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

--customer_table(6):= 'Sri'; -- throws an error

customer_table(4):='Sri'; -- it must be sequential.

--delete 3rd element

customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

/

VARRAY:

• A VARRAY is similar to a nested table except we must specify an uppet bound in the declaration.
• Like nested table, we can store in the tadabase.
• Unlike nested tables, individual elements cant be deleted so they remain same.

DECLARE

TYPE customer_type IS VARRAY(4) OF VARCHAR2(100);

-- initialize the collection

customer_table customer_type := customer_type();

v_idx number;

BEGIN

--you have to extend before using the table

customer_table.extend(4);

customer_table(1):= 'John';

customer_table(2):= 'Rudy';

customer_table(3):= 'Rub';

--customer_table(6):= 'Sri'; -- throws an error

customer_table(4):='Sri'; -- it must be sequential.

--can not delete an item. 

--customer_table.delete(3);

--traverse sparse collection

v_idx := customer_table.first;

WHILE v_idx IS NOT NULL 

LOOP

Dbms_output.put_line('customer name: ' || customer_table(v_idx));

v_idx := customer_table.next(v_idx);

END LOOP display_loop;

END;

/

Collection methods:

Multiset operator:

We can do following operations

• MULTISET UNION
• MULTISET UNION DISTINCT
• MULTISET EXCEPT
• MULTISET INTERSECT

DECLARE

TYPE t_tab IS TABLE OF NUMBER;

l_tab1 t_tab := t_tab(1,2,3,4,5,6);

l_tab2 t_tab := t_tab(5,6,7,8,9,10);

BEGIN

l_tab1 := l_tab1 MULTISET INTETSET l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP

Dbms_output.put_line(l_tab1(i));

END LOOP;

END;

/