Collection Methods
Collection Methods:-Oracle provides the built-in collection methods that make collections easier to use.
Below is the list of collection Methods
1) EXISTS(n) :-Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
2) COUNT :-Returns the number of elements that a collection currently contains.
3) LIMIT :-Checks the Maximum Size of a Collection.
4) FIRST :-Returns the first (smallest) index numbers in a collection that uses integer subscripts.
5) LAST :-Returns the last (largest) index numbers in a collection that uses integer subscripts.
6) PRIOR(n) :-Returns the index number that precedes index n in a collection.
7) NEXT(n) :-Returns the index number that succeeds index n.
8) EXTEND :-Appends one null element to a collection.
9) EXTEND(n) :-Appends n null elements to a collection.
10)EXTEND(6,5):-Appends n copies of the ith element to a collection.
11)TRIM :-Removes one element from the end of a collection.
12)TRIM(n) :-Removes n elements from the end of a collection.
13)DELETE :-Removes all elements from a collection, setting COUNT to 0.
14)DELETE(n) :-Removes the nth element from an associative array with a numeric key or a nested table. If the associative array
has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
15)DELETE(m,n):-Removes all elements in the range m..n from an associative array or nested table.
Note:-If m is larger than n or if m or n is null, DELETE(m,n) does nothing.
Collection exceptions:-Below are the Exceptions that are used for collection programming
1) COLLECTION_IS_NULL :-You try to operate on an atomically null collection.
2) NO_DATA_FOUND :-A subscript designates an element that was deleted, or a nonexistent element of an associative array.
3) SUBSCRIPT_BEYOND_COUNT :-A subscript exceeds the number of elements in a collection.
4) SUBSCRIPT_OUTSIDE_LIMIT:-A subscript is outside the allowed range.
5) VALUE_ERROR :-A subscript is null or not convertible to the key type.
This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.
Example1:-
declare
type tbl_ename is table of varchar2(50);
enames tbl_ename;
i number;
tot_emp number;
begin
enames:=tbl_ename('Ramesh','Suresh','Mahesh','Ganesh','Dinesh','Rajesh');
tot_emp:=enames.count;
i:=enames.first;
while i<=enames.last loop
dbms_output.put_line('Employee in '||i||' position in '||enames(i));
i:=enames.next(i);
end loop;
end;
/
Example2:-
declare
type tbl_ename is table of varchar2(50);
enames tbl_ename;
i number;
tot_emp number;
begin
enames:=tbl_ename('Ramesh','Suresh','Mahesh','Ganesh','Dinesh','Rajesh');
tot_emp:=enames.count;
dbms_output.put_line('Total Employee Count is :-'||tot_emp );
i:=enames.first;
while i<=enames.last loop
dbms_output.put_line('Employee in '||i||' position in '||enames(i));
i:=enames.next(i);
end loop;
dbms_output.put_line('Employee in 9 position exists'||enames(9));
end;
/
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 15
Example3:-
declare
type tbl_ename is table of varchar2(50);
enames tbl_ename;
i number;
tot_emp number;
begin
enames:=tbl_ename('Ramesh','Suresh','Mahesh','Ganesh','Dinesh','Rajesh');
enames(1):='Ratnesh'
tot_emp:=enames.count;
dbms_output.put_line('Total Employee Count is :-'||tot_emp );
i:=enames.first;
while i<=enames.last loop
dbms_output.put_line('Employee in '||i||' position in '||enames(i));
i:=enames.next(i);
end loop;
if enames.exists(9) then
dbms_output.put_line('Employee in 9 position exists');
else
dbms_output.put_line('Employee in 9 position does not exists');
end if;
end;
/
Example4:-
DECLARE
CURSOR name_cur IS SELECT ename FROM emp;
TYPE name_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
name_tab name_type;
v_counter INTEGER := 0;
BEGIN
FOR i IN name_cur LOOP
v_counter := v_counter + 1;
name_tab(v_counter) := i.ename;
DBMS_OUTPUT.PUT_LINE ('ename('||v_counter||'): '||name_tab(v_counter));
END LOOP;
END;
/
Example5:-
DECLARE
CURSOR name_cur IS SELECT ename FROM emp;
TYPE name_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
name_tab name_type;
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
name_tab(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('Ename('||v_counter||'): '||name_tab(v_counter));
END LOOP;
DBMS_OUTPUT.PUT_LINE ('ename(11): '||name_tab(11));
END;
/
Ename(1): WARD
Ename(2): MARTIN
Ename(3): BLAKE
Ename(4): TURNER
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 12
Example6:-
DECLARE
CURSOR name_cur IS SELECT ename FROM emp ;
TYPE name_type IS TABLE OF emp.ename%TYPE;
name_tab name_type;
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
name_tab(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('Ename('||v_counter||'): '||name_tab(v_counter));
END LOOP;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 9
Example7:-
DECLARE
CURSOR name_cur IS SELECT ename FROM emp ;
TYPE name_type IS TABLE OF emp.ename%TYPE;
name_tab name_type:=name_type();
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
name_tab(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('Ename('||v_counter||'): '||name_tab(v_counter));
END LOOP;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 9
Example8:-
DECLARE
CURSOR name_cur IS SELECT ename FROM emp ;
TYPE name_type IS TABLE OF emp.ename%TYPE;
name_tab name_type:=name_type();
v_counter INTEGER := 0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter := v_counter + 1;
name_tab.extend;
name_tab(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('Ename('||v_counter||'): '||name_tab(v_counter));
END LOOP;
END;
/
Ename(1): WARD
Ename(2): MARTIN
Ename(3): BLAKE
Ename(4): TURNER
PL/SQL procedure successfully completed.
Note:-EXTEND and TRIM methods cannot be used with index-by tables.
Example9:-
DECLARE
TYPE index_by_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
index_by_table index_by_type;
TYPE nested_type IS TABLE OF NUMBER;
nested_table nested_type:=nested_type(1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
BEGIN
-- Populate index by table
FOR i IN 1..10 LOOP
index_by_table(i) := i;
END LOOP;
IF index_by_table.EXISTS(3) THEN
DBMS_OUTPUT.PUT_LINE ('index_by_table(3) ='||index_by_table(3));
END IF;
-- delete 10th element from a collection
nested_table.DELETE(10);
-- delete elements 1 through 3 from a collection
nested_table.DELETE(1,3);
index_by_table.DELETE(10);
DBMS_OUTPUT.PUT_LINE('nested_table.COUNT =' ||nested_table.COUNT);
DBMS_OUTPUT.PUT_LINE('index_by_table.COUNT = ' ||index_by_table.COUNT);
DBMS_OUTPUT.PUT_LINE('nested_table.FIRST =' ||nested_table.FIRST);
DBMS_OUTPUT.PUT_LINE('nested_table.LAST = ' ||nested_table.LAST);
DBMS_OUTPUT.PUT_LINE('index_by_table.FIRST = ' ||index_by_table.FIRST);
DBMS_OUTPUT.PUT_LINE('index_by_table.LAST =' ||index_by_table.LAST);
DBMS_OUTPUT.PUT_LINE('nested_table.PRIOR(2) = ' ||nested_table.PRIOR(2));
DBMS_OUTPUT.PUT_LINE('nested_table.NEXT(2) = ' ||nested_table.NEXT(2));
DBMS_OUTPUT.PUT_LINE('index_by_table.PRIOR(2) = '||index_by_table.PRIOR(2));
DBMS_OUTPUT.PUT_LINE('index_by_table.NEXT(2) = ' ||index_by_table.NEXT(2));
-- Trim last two elements
nested_table.TRIM(2);
-- Trim last element
nested_table.TRIM;
DBMS_OUTPUT.PUT_LINE('nested_table.LAST = '||nested_table.LAST);
END;
/
Example for user of Varray :-
DECLARE
CURSOR name_cur IS SELECT ename FROM emp ;
TYPE name_type IS VARRAY(20) OF emp.ename%TYPE;
name_varray name_type:=name_type();
v_counter INTEGER:=0;
BEGIN
FOR name_rec IN name_cur LOOP
v_counter:=v_counter + 1;
name_varray.EXTEND;
name_varray(v_counter) := name_rec.ename;
DBMS_OUTPUT.PUT_LINE ('Ename('||v_counter||'): '||name_varray(v_counter));
END LOOP;
END;
/
DECLARE
TYPE varray_type IS VARRAY(10) OF NUMBER;
varray varray_type := varray_type(1, 2, 3, 4, 5, 6);
BEGIN
DBMS_OUTPUT.PUT_LINE ('varray.COUNT = '||varray.COUNT);
DBMS_OUTPUT.PUT_LINE ('varray.LIMIT = '||varray.LIMIT);
DBMS_OUTPUT.PUT_LINE ('varray.FIRST = '||varray.FIRST);
DBMS_OUTPUT.PUT_LINE ('varray.LAST = '||varray.LAST);
varray.EXTEND(2, 4);
DBMS_OUTPUT.PUT_LINE ('varray.LAST = '||varray.LAST);
DBMS_OUTPUT.PUT_LINE ('varray('||varray.LAST||') = '||varray(varray.LAST));
-- Trim last two elements
varray.TRIM(2);
DBMS_OUTPUT.PUT_LINE('varray.LAST = '||varray.LAST);
END;
/
DECLARE
TYPE varray_type IS VARRAY(3) OF CHAR(1);
varray varray_type := varray_type('A', 'B', 'C');
BEGIN
varray.DELETE(3);
END;
/
varray.DELETE(3);
*
ERROR at line 6:
ORA-06550: line 6, column 4:
PLS-00306: wrong number or types of arguments in call to
'DELETE'
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored
Multilevel Collections:-
DECLARE
TYPE varray_type1 IS VARRAY(4) OF INTEGER;
TYPE varray_type2 IS VARRAY(3) OF varray_type1;
varray1 varray_type1 := varray_type1(2, 4, 6, 8);
varray2 varray_type2 := varray_type2(varray1);
BEGIN
DBMS_OUTPUT.PUT_LINE ('Varray of integers');
FOR i IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE ('varray1('||i||'): '||varray1(i));
END LOOP;
varray2.EXTEND;
varray2(2) := varray_type1(1, 3, 5, 7);
DBMS_OUTPUT.PUT_LINE (chr(10)||'Varray of varrays of integers');
FOR i IN 1..2 LOOP
FOR j IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE
('varray2('||i||')('||j||'): '||varray2(i)(j));
END LOOP;
END LOOP;
END;
/
DECLARE
TYPE table_type1 IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
TYPE table_type2 IS TABLE OF TABLE_TYPE1 INDEX BY BINARY_INTEGER;
table_tab1 table_type1;
table_tab2 table_type2;
BEGIN
FOR i IN 1..2 LOOP
FOR j IN 1..3 LOOP
IF i = 1 THEN
table_tab1(j) := j;
ELSE
table_tab1(j) := 4 - j;
END IF;
table_tab2(i)(j) := table_tab1(j);
DBMS_OUTPUT.PUT_LINE ('table_tab2('||i||')('||j||'): '||table_tab2(i)(j));
END LOOP;
END LOOP;
END;
/
