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
SET OPERATIONS IN NESTED
TABLES
You can perform set
operations in the nested tables. You can also perform equality comparisions
between nested tables.
Possible operations are
Ø UNION
Ø UNION DISTINCT
Ø INTERSECT
Ø EXCEPT ( act like MINUS)
Ex:
DECLARE
type t is table of varchar(2);
nt1 t := t('a','b','c');
nt2 t := t('c','b','a');
nt3 t := t('b','c','a','c');
nt4 t := t('a','b','d');
nt5 t;
BEGIN
nt5 := set(nt1);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i ||
' ] = ' || nt5(i));
end loop;
nt5 := set(nt3);
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i || '
] = ' || nt5(i));
end loop;
nt5 := nt1 multiset union nt4;
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i || '
] = ' || nt5(i));
end
loop;
nt5 := nt1 multiset union nt3;
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i || '
] = ' || nt5(i));
end loop;
nt5 := nt1 multiset union distinct nt3;
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i || '
] = ' || nt5(i));
end loop;
nt5 := nt1 multiset except nt4;
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[ ' || i || '
] = ' || nt5(i));
end loop;
nt5 := nt4 multiset except nt1;
dbms_output.put_line('NESTED TABLE
ELEMENTS');
for i in nt5.first..nt5.last loop
dbms_output.put_line('nt5[
' || i || ' ] = ' || nt5(i));
end loop;
END;
Output:
NESTED TABLE ELEMENTS
nt5[ 1 ] = a
nt5[ 2 ] = b
nt5[ 3 ] = c
NESTED TABLE ELEMENTS
nt5[ 1 ] = b
nt5[ 2 ] = c
nt5[ 3 ] = a
NESTED TABLE ELEMENTS
nt5[ 1 ] = a
nt5[ 2 ] = b
nt5[ 3 ] = c
nt5[ 4 ] = a
nt5[ 5 ] = b
nt5[ 6 ] = d
NESTED TABLE ELEMENTS
nt5[ 1 ] = a
nt5[ 2 ] = b
nt5[ 3 ] = c
nt5[ 4 ] = b
nt5[ 5 ] = c
nt5[ 6 ] = a
nt5[ 7 ] = c
NESTED TABLE ELEMENTS
nt5[ 1 ] = a
nt5[ 2 ] = b
nt5[ 3 ] = c
NESTED TABLE ELEMENTS
nt5[ 1 ] = c
NESTED TABLE ELEMENTS
nt5[ 1 ] = d
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
No comments:
Post a Comment