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;
/
