In PL/SQL, there are 3 types of collections: Nested Table, Varray and Associative Array. Associative Array is like HashMap in Java which is easy to understand. Nested Table and Varray are like array in java. They are confusing for java developers. Below are their differences:
Varray
It has max size which is defined in type. It can’t extend over than max size defined in type.
Element in it can be changed, but can’t be deleted. It will have exception if visit an OutofBound position.
Nested Table
An array without limit size. We can extend as much as we want. An element can be deleted. But will raise exception if we visit a deleted element.
It will have exception if visit an OutofBound position.
Try below code in PL/SQL:
Varray
DECLARE
TYPE varray_type IS VARRAY(5) OF NUMBER;
p_level varray_type;
Test 1
p_level := varray_type(1, 2, 3, 4, 5);
DBMS_OUTPUT.PUT_LINE(p_level.COUNT); — 5
DBMS_OUTPUT.PUT_LINE(p_level(2)); — 2
p_level.DELETE(2); — won’t pass compilation. Not allow to delete.
Test 2
p_level := varray_type(1, 2, 3, 4, 5, 6); — error because greater than limit.
Test 3
p_level := varray_type(1, 2, 3, 4);
p_level(5) := 5; — error because reach the limit.
Test 4
p_level := varray_type(1, 2, 3, 4);
P_level.EXTEND;
p_level(5) := 5; — 5
DBMS_OUTPUT.PUT_LINE(p_level(5)); — 5
Test 5
p_level := varray_type(1, 2, 3, 4, 5);
DBMS_OUTPUT.PUT_LINE(p_level.COUNT); — 5
p_level.EXTEND; — error. Doesn’t allow to extend. Because already reach the limit 5.
DBMS_OUTPUT.PUT_LINE(p_level.COUNT); — won’t reach here.
Nested Table
DECLARE
TYPE varray_type IS TABLE OF NUMBER;
p_level varray_type;
p_level := varray_type(1, 2, 3, 4, 5);
— Test 1
DBMS_OUTPUT.PUT_LINE(p_level.COUNT); — 5
DBMS_OUTPUT.PUT_LINE(p_level(2)); — 2
p_level.DELETE(2);
DBMS_OUTPUT.PUT_LINE(‘3nd element: ‘||p_level(3)); — 3
DBMS_OUTPUT.PUT_LINE(p_level.LAST); — 5
— Test 2
DBMS_OUTPUT.PUT_LINE(p_level.COUNT); — 5
DBMS_OUTPUT.PUT_LINE(p_level(2)); — 2
p_level.DELETE(2);
DBMS_OUTPUT.PUT_LINE(‘2nd element: ‘||p_level(2)); — silent error and won’t show anything
DBMS_OUTPUT.PUT_LINE(‘3nd element: ‘||p_level(3)); — won’t show anything. Because above clause has an error
DBMS_OUTPUT.PUT_LINE(p_level.LAST); — won’t show anything.
— Test 3
DBMS_OUTPUT.PUT_LINE(p_level.COUNT); — 5
DBMS_OUTPUT.PUT_LINE(p_level(2)); — 2
p_level.DELETE(2);
DBMS_OUTPUT.PUT_LINE(‘3nd element: ‘||p_level(3)); — 3
DBMS_OUTPUT.PUT_LINE(p_level.LAST); — 5
p_level.EXTEND;
DBMS_OUTPUT.PUT_LINE(p_level.LAST); — 6
p_level(2) := 22;
DBMS_OUTPUT.PUT_LINE(p_level(2)); — 22
Test 4
p_level := varray_type(1, 2, 3, 4);
p_level(5) := 5; — error. Because IndexOutofbound