COLLECTIONS
Collections are also
composite types, in that they allow you to treat several variables as a unit. A
collection combines variables of the same type.
TYPES
Ø Varrays
Ø Nested tables
Ø Index - by tables
(Associate arrays)
VARRAYS
A varray is datatype
very similar to an array. A varray has a fixed limit on its size, specified as
part of the declaration. Elements are inserted into varray starting at index 1,
up to maximum lenth declared in the varray type. The maximum size of the varray
is 2 giga bytes.
Syntax:
Type <type_name>
is varray | varying array (<limit>)
of <element_type>;
Ex1:
DECLARE
type t is varray(10) of varchar(2);
va t := t('a','b','c','d');
flag boolean;
BEGIN
dbms_output.put_line('Limit = ' ||
va.limit);
dbms_output.put_line('Count = ' ||
va.count);
dbms_output.put_line('First Index = ' ||
va.first);
dbms_output.put_line('Last Index = ' ||
va.last);
dbms_output.put_line('Next Index = ' ||
va.next(2));
dbms_output.put_line('Previous Index = '
|| va.prior(3));
dbms_output.put_line('VARRAY ELEMENTS');
for i in va.first..va.last loop
dbms_output.put_line('va[' || i ||
'] = ' || va(i));
end loop;
flag := va.exists(3);
if flag = true then
dbms_output.put_line('Index 3 exists
with an element ' || va(3));
else
dbms_output.put_line('Index 3 does
not exists');
end if;
va.extend;
dbms_output.put_line('After extend of one
index, Count = ' || va.count);
flag := va.exists(5);
if
flag = true then
dbms_output.put_line('Index 5 exists
with an element ' || va(5));
else
dbms_output.put_line('Index 5 does
not exists');
end if;
flag := va.exists(6);
if flag = true then
dbms_output.put_line('Index 6 exists
with an element ' || va(6));
else
dbms_output.put_line('Index 6 does
not exists');
end if;
va.extend(2);
dbms_output.put_line('After extend of two
indexes, Count = ' || va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in va.first..va.last loop
dbms_output.put_line('va[' || i ||
'] = ' || va(i));
end loop;
va(5) := 'e';
va(6) := 'f';
va(7) := 'g';
dbms_output.put_line('AFTER ASSINGNING VALUES
TO EXTENDED ELEMENTS,
VARRAY ELEMENTS');
for i in va.first..va.last loop
dbms_output.put_line('va[' || i ||
'] = ' || va(i));
end loop;
va.extend(3,2);
dbms_output.put_line('After extend of three indexes, Count = ' ||
va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in va.first..va.last loop
dbms_output.put_line('va[' || i ||
'] = ' || va(i));
end loop;
va.trim;
dbms_output.put_line('After trim of one
index, Count = ' || va.count);
va.trim(3);
dbms_output.put_line('After trim of three
indexs, Count = ' || va.count);
dbms_output.put_line('AFTER TRIM, VARRAY
ELEMENTS');
for i in va.first..va.last loop
dbms_output.put_line('va[' || i ||
'] = ' || va(i));
end loop;
va.delete;
dbms_output.put_line('After delete of
entire varray, Count = ' || va.count);
END;
Output:
Limit = 10
Count = 4
First Index
= 1
Last Index =
4
Next Index =
3
Previous
Index = 2
VARRAY
ELEMENTS
va[1] = a
va[2] = b
va[3] = c
va[4] = d
Index 3
exists with an element c
After extend
of one index, Count = 5
Index 5
exists with an element
Index 6 does
not exists
After extend
of two indexes, Count = 7
VARRAY
ELEMENTS
va[1] = a
va[2] = b
va[3] = c
va[4] = d
va[5] =
va[6] =
va[7] =
AFTER
ASSINGNING VALUES TO EXTENDED ELEMENTS, VARRAY ELEMENTS
va[1] = a
va[2] = b
va[3] = c
va[4] = d
va[5] = e
va[6] = f
va[7] = g
After extend
of three indexes, Count = 10
VARRAY
ELEMENTS
va[1] = a
va[2] = b
va[3] = c
va[4] = d
va[5] = e
va[6] = f
va[7] = g
va[8] = b
va[9] = b
va[10] = b
After trim
of one index, Count = 9
After trim
of three indexs, Count = 6
AFTER TRIM,
VARRAY ELEMENTS
va[1] = a
va[2] = b
va[3] = c
va[4] = d
va[5] = e
va[6] = f
After delete
of entire varray, Count = 0
Ex2:
DECLARE
type t is varray(4) of student%rowtype;
va t := t(null,null,null,null);
BEGIN
for i in 1..va.count loop
select * into va(i) from student
where sno = i;
dbms_output.put_line('Sno = ' ||
va(i).sno || ' Sname = ' || va(i).sname);
end loop;
END;
Output:
Sno = 1
Sname = saketh
Sno = 2
Sname = srinu
Sno = 3
Sname = divya
Sno = 4
Sname = manogni
Ex3:
DECLARE
type t is varray(4) of
student.smarks%type;
va t := t(null,null,null,null);
BEGIN
for i in 1..va.count loop
select smarks into va(i) from
student where sno = i;
dbms_output.put_line('Smarks = '
|| va(i));
end loop;
END;
Output:
Smarks = 100
Smarks = 200
Smarks = 300
Smarks = 400
Ex4:
DECLARE
type r is record(c1
student.sname%type,c2 student.smarks%type);
type t is varray(4) of r;
va t := t(null,null,null,null);
BEGIN
for i in 1..va.count loop
select sname,smarks into va(i) from
student where sno = i;
dbms_output.put_line('Sname = ' ||
va(i).c1 || ' Smarks = ' || va(i).c2);
end loop;
END;
Output:
Sname =
saketh Smarks = 100
Sname =
srinu Smarks = 200
Sname =
divya Smarks = 300
Sname =
manogni Smarks = 400
Ex5:
DECLARE
type t is varray(1) of addr;
va t := t(null);
cursor c is select * from employ;
i number := 1;
BEGIN
for v in c loop
select address into va(i) from
employ where ename = v.ename;
dbms_output.put_line('Hno = ' ||
va(i).hno || ' City = ' || va(i).city);
end loop;
END;
Output:
Hno = 11
City = hyd
Hno = 22
City = bang
Hno = 33
City = kochi
Ex6:
DECLARE
type t is varray(5) of varchar(2);
va1 t;
va2 t := t();
BEGIN
if va1 is null then
dbms_output.put_line('va1 is null');
else
dbms_output.put_line('va1 is not
null');
end if;
if va2 is null then
dbms_output.put_line('va2 is null');
else
dbms_output.put_line('va2 is not
null');
end if;
END;
Output:
va1 is null
va2 is not
null
NESTED TABLES
A nested table is
thought of a database table which has no limit on its size. Elements are inserted
into nested table starting at index 1. The maximum size of the varray is 2 giga
bytes.
Syntax:
Type <type_name>
is table of <table_type>;
Ex1:
DECLARE
type t is table of varchar(2);
nt t := t('a','b','c','d');
flag boolean;
BEGIN
if nt.limit is null then
dbms_output.put_line('No limit to
Nested Tables');
else
dbms_output.put_line('Limit = ' ||
nt.limit);
end if;
dbms_output.put_line('Count = ' ||
nt.count);
dbms_output.put_line('First Index = ' ||
nt.first);
dbms_output.put_line('Last Index = ' ||
nt.last);
dbms_output.put_line('Next Index = ' ||
nt.next(2));
dbms_output.put_line('Previous Index = '
|| nt.prior(3));
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
flag := nt.exists(3);
if flag = true then
dbms_output.put_line('Index 3 exists
with an element ' || nt(3));
else
dbms_output.put_line('Index 3 does
not exists');
end if;
nt.extend;
dbms_output.put_line('After extend of
one index, Count = ' || nt.count);
flag := nt.exists(5);
if flag = true then
dbms_output.put_line('Index 5 exists with
an element ' || nt(5));
else
dbms_output.put_line('Index 5 does
not exists');
end if;
flag := nt.exists(6);
if flag = true then
dbms_output.put_line('Index 6 exists
with an element ' || nt(6));
else
dbms_output.put_line('Index 6 does
not exists');
end if;
nt.extend(2);
dbms_output.put_line('After extend of
two indexes, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
nt(5) := 'e';
nt(6) := 'f';
nt(7) := 'g';
dbms_output.put_line('AFTER ASSINGNING VALUES
TO EXTENDED ELEMENTS, NESTED TABLE
ELEMENTS');
for i in 1..nt.count loop
dbms_output.put_line('nt[' || i
|| '] = ' || nt(i));
end loop;
nt.extend(5,2);
dbms_output.put_line('After extend of five
indexes, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
nt.trim;
dbms_output.put_line('After trim of one
index, Count = ' || nt.count);
nt.trim(3);
dbms_output.put_line('After trim of
three indexs, Count = ' || nt.count);
dbms_output.put_line('AFTER TRIM, NESTED
TABLE ELEMENTS');
for i in 1..nt.count loop
dbms_output.put_line('nt[' || i
|| '] = ' || nt(i));
end loop;
nt.delete(1);
dbms_output.put_line('After delete of
first index, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 2..nt.count+1 loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
nt.delete(4);
dbms_output.put_line('After delete of
fourth index, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 2..3 loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
for i in 5..nt.count+2 loop
dbms_output.put_line('nt[' || i ||
'] = ' || nt(i));
end loop;
nt.delete;
dbms_output.put_line('After delete of
entire nested table, Count = ' || nt.count);
END;
Output:
No limit to
Nested Tables
Count = 4
First Index
= 1
Last Index =
4
Next Index =
3
Previous
Index = 2
NESTED TABLE
ELEMENTS
nt[1] = a
nt[2] = b
nt[3] = c
nt[4] = d
Index 3
exists with an element c
After extend
of one index, Count = 5
Index 5
exists with an element
Index 6 does
not exists
After extend
of two indexes, Count = 7
NESTED TABLE
ELEMENTS
nt[1] = a
nt[2] = b
nt[3] = c
nt[4] = d
nt[5] =
nt[6] =
nt[7] =
AFTER
ASSINGNING VALUES TO EXTENDED ELEMENTS, NESTED TABLE ELEMENTS
nt[1] = a
nt[2] = b
nt[3] = c
nt[4] = d
nt[5] = e
nt[6] = f
nt[7] = g
After extend
of five indexes, Count = 12
NESTED TABLE
ELEMENTS
nt[1] = a
nt[2] = b
nt[3] = c
nt[4] = d
nt[5] = e
nt[6] = f
nt[7] = g
nt[8] = b
nt[9] = b
nt[10] = b
nt[11] = b
nt[12] = b
After trim
of one index, Count = 11
After trim
of three indexs, Count = 8
AFTER TRIM,
NESTED TABLE ELEMENTS
nt[1] = a
nt[2] = b
nt[3] = c
nt[4] = d
nt[5] = e
nt[6] = f
nt[7] = g
nt[8] = b
After delete
of first index, Count = 7
NESTED TABLE
ELEMENTS
nt[2] = b
nt[3] = c
nt[4] = d
nt[5] = e
nt[6] = f
nt[7] = g
nt[8] = b
After delete
of fourth index, Count = 6
NESTED TABLE
ELEMENTS
nt[2] = b
nt[3] = c
nt[5] = e
nt[6] = f
nt[7] = g
nt[8] = b
After delete
of entire nested table, Count = 0
Ex2:
DECLARE
type t is table of student%rowtype;
nt t := t(null,null,null,null);
BEGIN
for i in 1..nt.count loop
select * into nt(i) from student
where sno = i;
dbms_output.put_line('Sno = ' ||
nt(i).sno || ' Sname = ' || nt(i).sname);
end loop;
END;
Output:
Sno = 1
Sname = saketh
Sno = 2
Sname = srinu
Sno = 3
Sname = divya
Sno = 4
Sname = manogni
Ex3:
DECLARE
type t is table of student.smarks%type;
nt t := t(null,null,null,null);
BEGIN
for i in 1..nt.count loop
select smarks into nt(i) from
student where sno = i;
dbms_output.put_line('Smarks = '
|| nt(i));
end loop;
END;
Output:
Smarks = 100
Smarks = 200
Smarks = 300
Smarks = 400
Ex4:
DECLARE
type r is record(c1
student.sname%type,c2 student.smarks%type);
type t is table of r;
nt t := t(null,null,null,null);
BEGIN
for i in 1..nt.count loop
select sname,smarks into nt(i)
from student where sno = i;
dbms_output.put_line('Sname = ' ||
nt(i).c1 || ' Smarks = ' || nt(i).c2);
end loop;
END;
Output:
Sname =
saketh Smarks = 100
Sname =
srinu Smarks = 200
Sname =
divya Smarks = 300
Sname =
manogni Smarks = 400
Ex5:
DECLARE
type t is table of addr;
nt t := t(null);
cursor c is select * from employ;
i number := 1;
BEGIN
for v in c loop
select address into nt(i) from
employ where ename = v.ename;
dbms_output.put_line('Hno = ' ||
nt(i).hno || ' City = ' || nt(i).city);
end loop;
END;
Output:
Hno = 11
City = hyd
Hno = 22
City = bang
Hno = 33
City = kochi
Ex6:
DECLARE
type t is varray(5) of varchar(2);
nt1 t;
nt2 t := t();
BEGIN
if nt1 is null then
dbms_output.put_line('nt1 is null');
else
dbms_output.put_line('nt1 is not
null');
end if;
if nt2 is null then
dbms_output.put_line('nt2 is null');
else
dbms_output.put_line('nt2 is not
null');
end if;
END;
Output:
nt1 is null
nt2 is not
null
INDEX-BY TABLES
An index-by table has no
limit on its size. Elements are inserted into index-by table whose index may
start non-sequentially including negative integers.
Syntax:
Type <type_name>
is table of <table_type> index
by binary_integer;
Ex:
DECLARE
type t is table of varchar(2) index by
binary_integer;
ibt t;
flag boolean;
BEGIN
ibt(1) := 'a';
ibt(-20) := 'b';
ibt(30) := 'c';
ibt(100) := 'd';
if ibt.limit is null then
dbms_output.put_line('No limit to
Index by Tables');
else
dbms_output.put_line('Limit = ' ||
ibt.limit);
end if;
dbms_output.put_line('Count = ' ||
ibt.count);
dbms_output.put_line('First Index = ' ||
ibt.first);
dbms_output.put_line('Last Index = ' ||
ibt.last);
dbms_output.put_line('Next Index = ' ||
ibt.next(2));
dbms_output.put_line('Previous Index = '
|| ibt.prior(3));
dbms_output.put_line('INDEX BY TABLE
ELEMENTS');
dbms_output.put_line('ibt[-20] = ' ||
ibt(-20));
dbms_output.put_line('ibt[1] = ' ||
ibt(1));
dbms_output.put_line('ibt[30] = ' ||
ibt(30));
dbms_output.put_line('ibt[100] = ' ||
ibt(100));
flag := ibt.exists(30);
if flag = true then
dbms_output.put_line('Index 30 exists
with an element ' || ibt(30));
else
dbms_output.put_line('Index 30 does not
exists');
end if;
flag := ibt.exists(50);
if flag = true then
dbms_output.put_line('Index 50 exists
with an element ' || ibt(30));
else
dbms_output.put_line('Index 50 does
not exists');
end if;
ibt.delete(1);
dbms_output.put_line('After delete of
first index, Count = ' || ibt.count);
ibt.delete(30);
dbms_output.put_line('After delete of
index thirty, Count = ' || ibt.count);
dbms_output.put_line('INDEX BY TABLE ELEMENTS');
dbms_output.put_line('ibt[-20] = ' ||
ibt(-20));
dbms_output.put_line('ibt[100] = ' ||
ibt(100));
ibt.delete;
dbms_output.put_line('After delete of
entire index-by table, Count = ' || ibt.count);
END;
Output:
No limit to
Index by Tables
Count = 4
First Index
= -20
Last Index =
100
Next Index =
30
Previous
Index = 1
INDEX BY
TABLE ELEMENTS
ibt[-20] = b
ibt[1] = a
ibt[30] = c
ibt[100] = d
Index 30
exists with an element c
Index 50
does not exists
After delete
of first index, Count = 3
After delete
of index thirty, Count = 2
INDEX BY
TABLE ELEMENTS
ibt[-20] = b
ibt[100] = d
After delete
of entire index-by table, Count = 0
DIFFERENCES AMONG
COLLECTIONS
Ø Varrays has limit,
nested tables and index-by tables has no limit.
Ø Varrays and nested
tables must be initialized before assignment of elements, in index-by tables we
can directly assign elements.
Ø Varrays and nested
tables stored in database, but index-by tables can not.
Ø Nested tables and
index-by tables are PL/SQL tables, but varrays can not.
Ø Keys must be positive in
case of nested tables and varrays, in case of index-by tables keys can be
positive or negative.
Ø Referencing nonexistent
elements raises SUBSCRIPT_BEYOND_COUNT in both nested tables and varrays, but in case of index-by
tables NO_DATA_FOUND raises.
Ø Keys are sequential in
both nested tables and varrays, non-sequential in index-by tables.
Ø Individual indexes can
be deleted in both nested tables and index-by tables, but in varrays can not.
Ø Individual indexes can
be trimmed in both nested tables and varrays, but in index-by tables can not.
Ø Individual indexes can
be extended in both nested tables and varrays, but in index-by tables can not.
No comments:
Post a Comment