PL/SQL Collection : Nested Table and it's Collection Methods

PL/SQL Collection : Nested Table and it's Collection Methods

Here I have created a procedure to see how to declare,initialize and manipulate a Nested Table.

Collection Methods on a Nested Table are :
FIRST,LAST,COUNT,
DELETE,DELETE(m,n),DELETE(n),
NEXT,PRIOR,
EXTENDS(n,i),EXTENDS(n),
EXISTS(n)

CREATE OR REPLACE PROCEDURE xx_nesttab
as

TYPE VarTab IS TABLE OF NUMBER;

V1 varTab;
--Initialize at declaration itself
v2 varTab := varTab(5001,5002,5003);
i NUMBER;
j NUMBER;

BEGIN

/*
Initialize the nestedTable using constructor
VarTab() is constructor which takes
same name as collection
*/

v1 := varTab();

v1.extend();

dbms_output.put_line(v1.COUNT);

v1(1):=1001;

dbms_output.put_line('First element in NestedTable : '||v1(1));

--assign 9 elements to existing Nested Table v1
v1.EXTEND(9);

FOR i in 2..10 LOOP
v1(i):=1000+i;
END LOOP;

--First and Last collection Methods
dbms_output.put_line('First Element Index : '||v1.FIRST);
dbms_output.put_line('Last Element Index  : '||v1.LAST);

--Print NestTab elements using First and Last methods

dbms_output.put_line('Elements in the NestTab are : ');

FOR i in v1.FIRST..v1.LAST LOOP
dbms_output.put_line('Element at index # '||i||' '||v1(i));
END LOOP;

--DELETE Method
v1.DELETE(10); -- Deletes element at index # 10

dbms_output.put_line('Elements in the NestTab are : ');

FOR i in v1.FIRST..v1.LAST LOOP
dbms_output.put_line('Element at index # '||i||' '||v1(i));
END LOOP;

--Say if we delete an element at index # 5
v1.DELETE(5); -- Deletes element at index # 5
--Now we cannot print using first and last methods

--Here we use NEXT and PRIOR Methods
dbms_output.put_line('Next index of an element : '||v1.NEXT(4));
dbms_output.put_line('Previous index of an element : '||v1.PRIOR(4));

dbms_output.put_line(' ');
dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

v1.DELETE(6,8); -- Deletes elements from 6th to 8th indexes

dbms_output.put_line(' ');
dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

v1.DELETE; -- Deletes all elements

dbms_output.put_line(v1.COUNT);

--Once we delete an element we need to assign and extending

--assign elements to existing Nested Table v1
v1.EXTEND(5);

FOR i in 1..5 LOOP
v1(i):=5000+i;
END LOOP;

dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

--EXTEND(n,i) assign element at index # 2 to 3 elements after last element
v1.EXTEND(3,2);

dbms_output.put_line('Elements in the NestTab are : ');

j:=v1.FIRST;

WHILE j is NOT NULL LOOP
dbms_output.put_line('Element at index # '||j||' '||v1(j));
        j:=v1.NEXT(j);
END LOOP;

dbms_output.put_line('Count before trim : '||v1.COUNT);
--TRIM Function 
v1.TRIM(2);
dbms_output.put_line('Count after trim : '||v1.COUNT);

--EXISTS(n) returns True if element exists at index # n

v1.DELETE(4);

if (v1.EXISTS(4)) THEN
dbms_output.put_line('Element Exists');
ELSE
dbms_output.put_line('Element doest not exist');
END IF;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line(SQLERRM);

END;
/