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.
MULTILEVEL COLLECTIONS
Collections of more than
one dimension which is a collection of collections, known as multilevel
collections.
Syntax:
Type <type_name1>
is table of <table_type> index
by binary_integer;
Type <type_name2>
is varray(<limit>) | table
| of <type_name1> | index by
binary_integer;
Ex1:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is varray(5) of t1;
va t2 := t2();
c number := 97;
flag boolean;
BEGIN
va.extend(4);
dbms_output.put_line('Count = ' ||
va.count);
dbms_output.put_line('Limit
= ' || va.limit);
for i in 1..va.count loop
for j in 1..va.count loop
va(i)(j) := chr(c);
c := c + 1;
end loop;
end loop;
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
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));
flag := va.exists(2);
if
flag = true then
dbms_output.put_line('Index 2
exists');
else
dbms_output.put_line('Index 2
exists');
end if;
va.extend;
va(1)(5) := 'q';
va(2)(5) := 'r';
va(3)(5) := 's';
va(4)(5)
:= 't';
va(5)(1) := 'u';
va(5)(2) := 'v';
va(5)(3) := 'w';
va(5)(4) := 'x';
va(5)(5) := 'y';
dbms_output.put_line('After extend of
one index, Count = ' || va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
va.trim;
dbms_output.put_line('After trim of one
index, Count = ' || va.count);
va.trim(2);
dbms_output.put_line('After trim of two
indexes, Count = ' || va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
va.delete;
dbms_output.put_line('After delete of
entire varray, Count = ' || va.count);
END;
Output:
Count = 4
Limit = 5
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[1][3] = c
va[1][4] = d
va[2][1] = e
va[2][2] = f
va[2][3] = g
va[2][4] = h
va[3][1] = i
va[3][2] = j
va[3][3] = k
va[3][4] = l
va[4][1] = m
va[4][2] = n
va[4][3] = o
va[4][4] = p
First index =
1
Last index = 4
Next index = 3
Previous index
= 2
Index 2 exists
After extend
of one index, Count = 5
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[1][3] = c
va[1][4] = d
va[1][5] = q
va[2][1] = e
va[2][2] = f
va[2][3] = g
va[2][4] = h
va[2][5] = r
va[3][1] = i
va[3][2] = j
va[3][3] = k
va[3][4] = l
va[3][5] = s
va[4][1] = m
va[4][2] = n
va[4][3] = o
va[4][4] = p
va[4][5] = t
va[5][1] = u
va[5][2] = v
va[5][3] = w
va[5][4] = x
va[5][5] = y
After trim of
one index, Count = 4
After trim of
two indexes, Count = 2
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[2][1] = e
va[2][2] = f
After delete
of entire varray, Count = 0
Ex2:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is table of t1;
nt
t2 := t2();
c number := 65;
v number := 1;
flag boolean;
BEGIN
nt.extend(4);
dbms_output.put_line('Count = ' ||
nt.count);
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;
for i in 1..nt.count loop
for j in 1..nt.count loop
nt(i)(j) := chr(c);
c := c + 1;
if c = 91 then
c := 97;
end if;
end loop;
end loop;
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
dbms_output.put_line('nt['
|| i || '][' || j || '] = ' || nt(i)(j));
end loop;
end loop;
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));
flag := nt.exists(2);
if flag = true then
dbms_output.put_line('Index 2
exists');
else
dbms_output.put_line('Index 2
exists');
end if;
nt.extend(2);
nt(1)(5) := 'Q';
nt(1)(6) := 'R';
nt(2)(5) := 'S';
nt(2)(6) := 'T';
nt(3)(5) := 'U';
nt(3)(6) := 'V';
nt(4)(5) := 'W';
nt(4)(6)
:= 'X';
nt(5)(1) := 'Y';
nt(5)(2) := 'Z';
nt(5)(3) := 'a';
nt(5)(4) := 'b';
nt(5)(5) := 'c';
nt(5)(6) := 'd';
nt(6)(1) := 'e';
nt(6)(2) := 'f';
nt(6)(3) := 'g';
nt(6)(4) := 'h';
nt(6)(5) := 'i';
nt(6)(6) := 'j';
dbms_output.put_line('After extend of
one index, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
dbms_output.put_line('nt['
|| i || '][' || j || '] = ' || nt(i)(j));
end loop;
end loop;
nt.trim;
dbms_output.put_line('After trim of one
indexe, Count = ' || nt.count);
nt.trim(2);
dbms_output.put_line('After trim of two
indexes, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
dbms_output.put_line('nt['
|| i || '][' || j || '] = ' || nt(i)(j));
end loop;
end loop;
nt.delete(2);
dbms_output.put_line('After delete of
second index, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
loop
exit when v = 4;
for j in 1..nt.count+1 loop
dbms_output.put_line('nt['
|| v || '][' || j || '] = ' || nt(v)(j));
end loop;
v := v + 1;
if v= 2 then
v := 3;
end if;
end loop;
nt.delete;
dbms_output.put_line('After delete of
entire nested table, Count = ' ||
nt.count);
END;
Output:
Count = 4
No limit to
Nested Tables
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[1][4] = D
nt[2][1] = E
nt[2][2] = F
nt[2][3] = G
nt[2][4] = H
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
nt[3][4] = L
nt[4][1] = M
nt[4][2] = N
nt[4][3] = O
nt[4][4] = P
First index =
1
Last index = 4
Next index = 3
Previous index
= 2
Index 2 exists
After extend
of one index, Count = 6
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[1][4] = D
nt[1][5] = Q
nt[1][6] = R
nt[2][1] = E
nt[2][2] = F
nt[2][3] = G
nt[2][4] = H
nt[2][5] = S
nt[2][6] = T
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
nt[3][4] = L
nt[3][5] = U
nt[3][6] = V
nt[4][1] = M
nt[4][2] = N
nt[4][3] = O
nt[4][4] = P
nt[4][5] = W
nt[4][6] = X
nt[5][1] = Y
nt[5][2] = Z
nt[5][3] = a
nt[5][4] = b
nt[5][5] = c
nt[5][6] = d
nt[6][1] = e
nt[6][2] = f
nt[6][3] = g
nt[6][4] = h
nt[6][5] = i
nt[6][6] = j
After trim of
one indexe, Count = 5
After trim of
two indexes, Count = 3
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[2][1] = E
nt[2][2] = F
nt[2][3] = G
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
After delete
of second index, Count = 2
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
After delete of entire nested table, Count = 0
Ex3:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is table of t1 index by
binary_integer;
ibt t2;
flag boolean;
BEGIN
dbms_output.put_line('Count = ' ||
ibt.count);
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;
ibt(1)(1) := 'a';
ibt(4)(5) := 'b';
ibt(5)(1) := 'c';
ibt(6)(2) := 'd';
ibt(8)(3) := 'e';
ibt(3)(4) := 'f';
dbms_output.put_line('INDEX-BY TABLE ELEMENTS');
dbms_output.put_line('ibt([1][1] = ' ||
ibt(1)(1));
dbms_output.put_line('ibt([4][5] = ' ||
ibt(4)(5));
dbms_output.put_line('ibt([5][1] = ' ||
ibt(5)(1));
dbms_output.put_line('ibt([6][2] = ' ||
ibt(6)(2));
dbms_output.put_line('ibt([8][3] = ' ||
ibt(8)(3));
dbms_output.put_line('ibt([3][4] = ' ||
ibt(3)(4));
dbms_output.put_line('First Index = ' ||
ibt.first);
dbms_output.put_line('Last Index = ' ||
ibt.last);
dbms_output.put_line('Next Index = ' ||
ibt.next(3));
dbms_output.put_line('Prior Index = ' ||
ibt.prior(8));
ibt(1)(2) := 'g';
ibt(1)(3) := 'h';
ibt(1)(4) := 'i';
ibt(1)(5) := 'k';
ibt(1)(6) := 'l';
ibt(1)(7) := 'm';
ibt(1)(8) := 'n';
dbms_output.put_line('Count = ' ||
ibt.count);
dbms_output.put_line('INDEX-BY TABLE ELEMENTS');
for i in 1..8 loop
dbms_output.put_line('ibt[1][' ||
i || '] = ' || ibt(1)(i));
end loop;
dbms_output.put_line('ibt([4][5] = ' ||
ibt(4)(5));
dbms_output.put_line('ibt([5][1] = ' ||
ibt(5)(1));
dbms_output.put_line('ibt([6][2] = ' ||
ibt(6)(2));
dbms_output.put_line('ibt([8][3] = ' ||
ibt(8)(3));
dbms_output.put_line('ibt([3][4] = ' ||
ibt(3)(4));
flag := ibt.exists(3);
if flag = true then
dbms_output.put_line('Index 3
exists');
else
dbms_output.put_line('Index 3 exists');
end if;
ibt.delete(1);
dbms_output.put_line('After delete of
first index, Count = ' || ibt.count);
ibt.delete(4);
dbms_output.put_line('After delete of
fourth index, Count = ' || ibt.count);
dbms_output.put_line('INDEX-BY TABLE ELEMENTS');
dbms_output.put_line('ibt([5][1] = ' ||
ibt(5)(1));
dbms_output.put_line('ibt([6][2] = ' ||
ibt(6)(2));
dbms_output.put_line('ibt([8][3] = ' ||
ibt(8)(3));
dbms_output.put_line('ibt([3][4] = ' ||
ibt(3)(4));
ibt.delete;
dbms_output.put_line('After delete of
entire index-by table, Count = ' ||
ibt.count);
END;
Output:
Count = 0
No limit to
Index-by Tables
INDEX-BY TABLE
ELEMENTS
ibt([1][1] = a
ibt([4][5] = b
ibt([5][1] = c
ibt([6][2] = d
ibt([8][3] = e
ibt([3][4] = f
First Index =
1
Last Index = 8
Next Index = 4
Prior Index =
6
Count = 6
INDEX-BY TABLE
ELEMENTS
ibt[1][1] = a
ibt[1][2] = g
ibt[1][3] = h
ibt[1][4] = i
ibt[1][5] = k
ibt[1][6] = l
ibt[1][7] = m
ibt[1][8] = n
ibt([4][5] = b
ibt([5][1] = c
ibt([6][2] = d
ibt([8][3] = e
ibt([3][4] = f
Index 3 exists
After delete
of first index, Count = 5
After delete
of fourth index, Count = 4
INDEX-BY TABLE
ELEMENTS
ibt([5][1] = c
ibt([6][2] = d
ibt([8][3] = e
ibt([3][4] = f
After delete
of entire index-by table, Count = 0
Ex4:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is table of t1 index by
binary_integer;
type t3 is table of t2;
nt t3 := t3();
c number := 65;
BEGIN
nt.extend(2);
dbms_output.put_line('Count = ' ||
nt.count);
for i in 1..nt.count loop
for j in 1..nt.count loop
for k in 1..nt.count loop
nt(i)(j)(k) := chr(c);
c := c + 1;
end loop;
end loop;
end loop;
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
for k in 1..nt.count loop
dbms_output.put_line('nt[' || i ||
'][' || j || '][' || k || '] = ' ||
nt(i)(j)(k));
end loop;
end loop;
end loop;
END;
Output:
Count = 2
NESTED TABLE
ELEMENTS
nt[1][1][1] =
A
nt[1][1][2] =
B
nt[1][2][1] =
C
nt[1][2][2] =
D
nt[2][1][1] =
E
nt[2][1][2] =
F
nt[2][2][1] =
G
nt[2][2][2] =
H
OBJECTS USED IN THE
EXAMPLES
SQL> select * from student;
SNO
SNAME SMARKS
---------- -------------- ----------
1
saketh 100
2
srinu 200
3
divya 300
4
manogni 400
SQL> create or replace type
addr as object(hno number(2),city varchar(10));/
SQL> select * from employ;
ENAME JOB
ADDRESS(HNO, CITY)
----------
---------- -----------------------------
Ranjit clerk ADDR(11, 'hyd')
Satish manager
ADDR(22, 'bang')
Srinu engineer ADDR(33, 'kochi')
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.
MULTILEVEL COLLECTIONS
Collections of more than
one dimension which is a collection of collections, known as multilevel
collections.
Syntax:
Type <type_name1>
is table of <table_type> index
by binary_integer;
Type <type_name2>
is varray(<limit>) | table
| of <type_name1> | index by
binary_integer;
Ex1:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is varray(5) of t1;
va t2 := t2();
c number := 97;
flag boolean;
BEGIN
va.extend(4);
dbms_output.put_line('Count = ' ||
va.count);
dbms_output.put_line('Limit
= ' || va.limit);
for i in 1..va.count loop
for j in 1..va.count loop
va(i)(j) := chr(c);
c := c + 1;
end loop;
end loop;
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
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));
flag := va.exists(2);
if
flag = true then
dbms_output.put_line('Index 2
exists');
else
dbms_output.put_line('Index 2
exists');
end if;
va.extend;
va(1)(5) := 'q';
va(2)(5) := 'r';
va(3)(5) := 's';
va(4)(5)
:= 't';
va(5)(1) := 'u';
va(5)(2) := 'v';
va(5)(3) := 'w';
va(5)(4) := 'x';
va(5)(5) := 'y';
dbms_output.put_line('After extend of
one index, Count = ' || va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
va.trim;
dbms_output.put_line('After trim of one
index, Count = ' || va.count);
va.trim(2);
dbms_output.put_line('After trim of two
indexes, Count = ' || va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
va.delete;
dbms_output.put_line('After delete of
entire varray, Count = ' || va.count);
END;
Output:
Count = 4
Limit = 5
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[1][3] = c
va[1][4] = d
va[2][1] = e
va[2][2] = f
va[2][3] = g
va[2][4] = h
va[3][1] = i
va[3][2] = j
va[3][3] = k
va[3][4] = l
va[4][1] = m
va[4][2] = n
va[4][3] = o
va[4][4] = p
First index =
1
Last index = 4
Next index = 3
Previous index
= 2
Index 2 exists
After extend
of one index, Count = 5
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[1][3] = c
va[1][4] = d
va[1][5] = q
va[2][1] = e
va[2][2] = f
va[2][3] = g
va[2][4] = h
va[2][5] = r
va[3][1] = i
va[3][2] = j
va[3][3] = k
va[3][4] = l
va[3][5] = s
va[4][1] = m
va[4][2] = n
va[4][3] = o
va[4][4] = p
va[4][5] = t
va[5][1] = u
va[5][2] = v
va[5][3] = w
va[5][4] = x
va[5][5] = y
After trim of
one index, Count = 4
After trim of
two indexes, Count = 2
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[2][1] = e
va[2][2] = f
After delete
of entire varray, Count = 0
Ex2:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is table of t1;
nt
t2 := t2();
c number := 65;
v number := 1;
flag boolean;
BEGIN
nt.extend(4);
dbms_output.put_line('Count = ' ||
nt.count);
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;
for i in 1..nt.count loop
for j in 1..nt.count loop
nt(i)(j) := chr(c);
c := c + 1;
if c = 91 then
c := 97;
end if;
end loop;
end loop;
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
dbms_output.put_line('nt['
|| i || '][' || j || '] = ' || nt(i)(j));
end loop;
end loop;
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));
flag := nt.exists(2);
if flag = true then
dbms_output.put_line('Index 2
exists');
else
dbms_output.put_line('Index 2
exists');
end if;
nt.extend(2);
nt(1)(5) := 'Q';
nt(1)(6) := 'R';
nt(2)(5) := 'S';
nt(2)(6) := 'T';
nt(3)(5) := 'U';
nt(3)(6) := 'V';
nt(4)(5) := 'W';
nt(4)(6)
:= 'X';
nt(5)(1) := 'Y';
nt(5)(2) := 'Z';
nt(5)(3) := 'a';
nt(5)(4) := 'b';
nt(5)(5) := 'c';
nt(5)(6) := 'd';
nt(6)(1) := 'e';
nt(6)(2) := 'f';
nt(6)(3) := 'g';
nt(6)(4) := 'h';
nt(6)(5) := 'i';
nt(6)(6) := 'j';
dbms_output.put_line('After extend of
one index, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
dbms_output.put_line('nt['
|| i || '][' || j || '] = ' || nt(i)(j));
end loop;
end loop;
nt.trim;
dbms_output.put_line('After trim of one
indexe, Count = ' || nt.count);
nt.trim(2);
dbms_output.put_line('After trim of two
indexes, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
dbms_output.put_line('nt['
|| i || '][' || j || '] = ' || nt(i)(j));
end loop;
end loop;
nt.delete(2);
dbms_output.put_line('After delete of
second index, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
loop
exit when v = 4;
for j in 1..nt.count+1 loop
dbms_output.put_line('nt['
|| v || '][' || j || '] = ' || nt(v)(j));
end loop;
v := v + 1;
if v= 2 then
v := 3;
end if;
end loop;
nt.delete;
dbms_output.put_line('After delete of
entire nested table, Count = ' ||
nt.count);
END;
Output:
Count = 4
No limit to
Nested Tables
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[1][4] = D
nt[2][1] = E
nt[2][2] = F
nt[2][3] = G
nt[2][4] = H
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
nt[3][4] = L
nt[4][1] = M
nt[4][2] = N
nt[4][3] = O
nt[4][4] = P
First index =
1
Last index = 4
Next index = 3
Previous index
= 2
Index 2 exists
After extend
of one index, Count = 6
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[1][4] = D
nt[1][5] = Q
nt[1][6] = R
nt[2][1] = E
nt[2][2] = F
nt[2][3] = G
nt[2][4] = H
nt[2][5] = S
nt[2][6] = T
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
nt[3][4] = L
nt[3][5] = U
nt[3][6] = V
nt[4][1] = M
nt[4][2] = N
nt[4][3] = O
nt[4][4] = P
nt[4][5] = W
nt[4][6] = X
nt[5][1] = Y
nt[5][2] = Z
nt[5][3] = a
nt[5][4] = b
nt[5][5] = c
nt[5][6] = d
nt[6][1] = e
nt[6][2] = f
nt[6][3] = g
nt[6][4] = h
nt[6][5] = i
nt[6][6] = j
After trim of
one indexe, Count = 5
After trim of
two indexes, Count = 3
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[2][1] = E
nt[2][2] = F
nt[2][3] = G
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
After delete
of second index, Count = 2
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
After delete of entire nested table, Count = 0
Ex3:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is table of t1 index by
binary_integer;
ibt t2;
flag boolean;
BEGIN
dbms_output.put_line('Count = ' ||
ibt.count);
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;
ibt(1)(1) := 'a';
ibt(4)(5) := 'b';
ibt(5)(1) := 'c';
ibt(6)(2) := 'd';
ibt(8)(3) := 'e';
ibt(3)(4) := 'f';
dbms_output.put_line('INDEX-BY TABLE ELEMENTS');
dbms_output.put_line('ibt([1][1] = ' ||
ibt(1)(1));
dbms_output.put_line('ibt([4][5] = ' ||
ibt(4)(5));
dbms_output.put_line('ibt([5][1] = ' ||
ibt(5)(1));
dbms_output.put_line('ibt([6][2] = ' ||
ibt(6)(2));
dbms_output.put_line('ibt([8][3] = ' ||
ibt(8)(3));
dbms_output.put_line('ibt([3][4] = ' ||
ibt(3)(4));
dbms_output.put_line('First Index = ' ||
ibt.first);
dbms_output.put_line('Last Index = ' ||
ibt.last);
dbms_output.put_line('Next Index = ' ||
ibt.next(3));
dbms_output.put_line('Prior Index = ' ||
ibt.prior(8));
ibt(1)(2) := 'g';
ibt(1)(3) := 'h';
ibt(1)(4) := 'i';
ibt(1)(5) := 'k';
ibt(1)(6) := 'l';
ibt(1)(7) := 'm';
ibt(1)(8) := 'n';
dbms_output.put_line('Count = ' ||
ibt.count);
dbms_output.put_line('INDEX-BY TABLE ELEMENTS');
for i in 1..8 loop
dbms_output.put_line('ibt[1][' ||
i || '] = ' || ibt(1)(i));
end loop;
dbms_output.put_line('ibt([4][5] = ' ||
ibt(4)(5));
dbms_output.put_line('ibt([5][1] = ' ||
ibt(5)(1));
dbms_output.put_line('ibt([6][2] = ' ||
ibt(6)(2));
dbms_output.put_line('ibt([8][3] = ' ||
ibt(8)(3));
dbms_output.put_line('ibt([3][4] = ' ||
ibt(3)(4));
flag := ibt.exists(3);
if flag = true then
dbms_output.put_line('Index 3
exists');
else
dbms_output.put_line('Index 3 exists');
end if;
ibt.delete(1);
dbms_output.put_line('After delete of
first index, Count = ' || ibt.count);
ibt.delete(4);
dbms_output.put_line('After delete of
fourth index, Count = ' || ibt.count);
dbms_output.put_line('INDEX-BY TABLE ELEMENTS');
dbms_output.put_line('ibt([5][1] = ' ||
ibt(5)(1));
dbms_output.put_line('ibt([6][2] = ' ||
ibt(6)(2));
dbms_output.put_line('ibt([8][3] = ' ||
ibt(8)(3));
dbms_output.put_line('ibt([3][4] = ' ||
ibt(3)(4));
ibt.delete;
dbms_output.put_line('After delete of
entire index-by table, Count = ' ||
ibt.count);
END;
Output:
Count = 0
No limit to
Index-by Tables
INDEX-BY TABLE
ELEMENTS
ibt([1][1] = a
ibt([4][5] = b
ibt([5][1] = c
ibt([6][2] = d
ibt([8][3] = e
ibt([3][4] = f
First Index =
1
Last Index = 8
Next Index = 4
Prior Index =
6
Count = 6
INDEX-BY TABLE
ELEMENTS
ibt[1][1] = a
ibt[1][2] = g
ibt[1][3] = h
ibt[1][4] = i
ibt[1][5] = k
ibt[1][6] = l
ibt[1][7] = m
ibt[1][8] = n
ibt([4][5] = b
ibt([5][1] = c
ibt([6][2] = d
ibt([8][3] = e
ibt([3][4] = f
Index 3 exists
After delete
of first index, Count = 5
After delete
of fourth index, Count = 4
INDEX-BY TABLE
ELEMENTS
ibt([5][1] = c
ibt([6][2] = d
ibt([8][3] = e
ibt([3][4] = f
After delete
of entire index-by table, Count = 0
Ex4:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is table of t1 index by
binary_integer;
type t3 is table of t2;
nt t3 := t3();
c number := 65;
BEGIN
nt.extend(2);
dbms_output.put_line('Count = ' ||
nt.count);
for i in 1..nt.count loop
for j in 1..nt.count loop
for k in 1..nt.count loop
nt(i)(j)(k) := chr(c);
c := c + 1;
end loop;
end loop;
end loop;
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
for k in 1..nt.count loop
dbms_output.put_line('nt[' || i ||
'][' || j || '][' || k || '] = ' ||
nt(i)(j)(k));
end loop;
end loop;
end loop;
END;
Output:
Count = 2
NESTED TABLE
ELEMENTS
nt[1][1][1] =
A
nt[1][1][2] =
B
nt[1][2][1] =
C
nt[1][2][2] =
D
nt[2][1][1] =
E
nt[2][1][2] =
F
nt[2][2][1] =
G
nt[2][2][2] =
H
OBJECTS USED IN THE
EXAMPLES
SQL> select * from student;
SNO
SNAME SMARKS
---------- -------------- ----------
1
saketh 100
2
srinu 200
3
divya 300
4
manogni 400
SQL> create or replace type
addr as object(hno number(2),city varchar(10));/
SQL> select * from employ;
ENAME JOB
ADDRESS(HNO, CITY)
----------
---------- -----------------------------
Ranjit clerk ADDR(11, 'hyd')
Satish manager
ADDR(22, 'bang')
Srinu engineer ADDR(33, 'kochi')
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.
MULTILEVEL COLLECTIONS
Collections of more than
one dimension which is a collection of collections, known as multilevel
collections.
Syntax:
Type <type_name1>
is table of <table_type> index
by binary_integer;
Type <type_name2>
is varray(<limit>) | table
| of <type_name1> | index by
binary_integer;
Ex1:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is varray(5) of t1;
va t2 := t2();
c number := 97;
flag boolean;
BEGIN
va.extend(4);
dbms_output.put_line('Count = ' ||
va.count);
dbms_output.put_line('Limit
= ' || va.limit);
for i in 1..va.count loop
for j in 1..va.count loop
va(i)(j) := chr(c);
c := c + 1;
end loop;
end loop;
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
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));
flag := va.exists(2);
if
flag = true then
dbms_output.put_line('Index 2
exists');
else
dbms_output.put_line('Index 2
exists');
end if;
va.extend;
va(1)(5) := 'q';
va(2)(5) := 'r';
va(3)(5) := 's';
va(4)(5)
:= 't';
va(5)(1) := 'u';
va(5)(2) := 'v';
va(5)(3) := 'w';
va(5)(4) := 'x';
va(5)(5) := 'y';
dbms_output.put_line('After extend of
one index, Count = ' || va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
va.trim;
dbms_output.put_line('After trim of one
index, Count = ' || va.count);
va.trim(2);
dbms_output.put_line('After trim of two
indexes, Count = ' || va.count);
dbms_output.put_line('VARRAY ELEMENTS');
for i in 1..va.count loop
for j in 1..va.count loop
dbms_output.put_line('va['
|| i || '][' || j || '] = ' || va(i)(j));
end loop;
end loop;
va.delete;
dbms_output.put_line('After delete of
entire varray, Count = ' || va.count);
END;
Output:
Count = 4
Limit = 5
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[1][3] = c
va[1][4] = d
va[2][1] = e
va[2][2] = f
va[2][3] = g
va[2][4] = h
va[3][1] = i
va[3][2] = j
va[3][3] = k
va[3][4] = l
va[4][1] = m
va[4][2] = n
va[4][3] = o
va[4][4] = p
First index =
1
Last index = 4
Next index = 3
Previous index
= 2
Index 2 exists
After extend
of one index, Count = 5
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[1][3] = c
va[1][4] = d
va[1][5] = q
va[2][1] = e
va[2][2] = f
va[2][3] = g
va[2][4] = h
va[2][5] = r
va[3][1] = i
va[3][2] = j
va[3][3] = k
va[3][4] = l
va[3][5] = s
va[4][1] = m
va[4][2] = n
va[4][3] = o
va[4][4] = p
va[4][5] = t
va[5][1] = u
va[5][2] = v
va[5][3] = w
va[5][4] = x
va[5][5] = y
After trim of
one index, Count = 4
After trim of
two indexes, Count = 2
VARRAY
ELEMENTS
va[1][1] = a
va[1][2] = b
va[2][1] = e
va[2][2] = f
After delete
of entire varray, Count = 0
Ex2:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is table of t1;
nt
t2 := t2();
c number := 65;
v number := 1;
flag boolean;
BEGIN
nt.extend(4);
dbms_output.put_line('Count = ' ||
nt.count);
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;
for i in 1..nt.count loop
for j in 1..nt.count loop
nt(i)(j) := chr(c);
c := c + 1;
if c = 91 then
c := 97;
end if;
end loop;
end loop;
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
dbms_output.put_line('nt['
|| i || '][' || j || '] = ' || nt(i)(j));
end loop;
end loop;
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));
flag := nt.exists(2);
if flag = true then
dbms_output.put_line('Index 2
exists');
else
dbms_output.put_line('Index 2
exists');
end if;
nt.extend(2);
nt(1)(5) := 'Q';
nt(1)(6) := 'R';
nt(2)(5) := 'S';
nt(2)(6) := 'T';
nt(3)(5) := 'U';
nt(3)(6) := 'V';
nt(4)(5) := 'W';
nt(4)(6)
:= 'X';
nt(5)(1) := 'Y';
nt(5)(2) := 'Z';
nt(5)(3) := 'a';
nt(5)(4) := 'b';
nt(5)(5) := 'c';
nt(5)(6) := 'd';
nt(6)(1) := 'e';
nt(6)(2) := 'f';
nt(6)(3) := 'g';
nt(6)(4) := 'h';
nt(6)(5) := 'i';
nt(6)(6) := 'j';
dbms_output.put_line('After extend of
one index, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
dbms_output.put_line('nt['
|| i || '][' || j || '] = ' || nt(i)(j));
end loop;
end loop;
nt.trim;
dbms_output.put_line('After trim of one
indexe, Count = ' || nt.count);
nt.trim(2);
dbms_output.put_line('After trim of two
indexes, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
dbms_output.put_line('nt['
|| i || '][' || j || '] = ' || nt(i)(j));
end loop;
end loop;
nt.delete(2);
dbms_output.put_line('After delete of
second index, Count = ' || nt.count);
dbms_output.put_line('NESTED TABLE ELEMENTS');
loop
exit when v = 4;
for j in 1..nt.count+1 loop
dbms_output.put_line('nt['
|| v || '][' || j || '] = ' || nt(v)(j));
end loop;
v := v + 1;
if v= 2 then
v := 3;
end if;
end loop;
nt.delete;
dbms_output.put_line('After delete of
entire nested table, Count = ' ||
nt.count);
END;
Output:
Count = 4
No limit to
Nested Tables
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[1][4] = D
nt[2][1] = E
nt[2][2] = F
nt[2][3] = G
nt[2][4] = H
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
nt[3][4] = L
nt[4][1] = M
nt[4][2] = N
nt[4][3] = O
nt[4][4] = P
First index =
1
Last index = 4
Next index = 3
Previous index
= 2
Index 2 exists
After extend
of one index, Count = 6
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[1][4] = D
nt[1][5] = Q
nt[1][6] = R
nt[2][1] = E
nt[2][2] = F
nt[2][3] = G
nt[2][4] = H
nt[2][5] = S
nt[2][6] = T
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
nt[3][4] = L
nt[3][5] = U
nt[3][6] = V
nt[4][1] = M
nt[4][2] = N
nt[4][3] = O
nt[4][4] = P
nt[4][5] = W
nt[4][6] = X
nt[5][1] = Y
nt[5][2] = Z
nt[5][3] = a
nt[5][4] = b
nt[5][5] = c
nt[5][6] = d
nt[6][1] = e
nt[6][2] = f
nt[6][3] = g
nt[6][4] = h
nt[6][5] = i
nt[6][6] = j
After trim of
one indexe, Count = 5
After trim of
two indexes, Count = 3
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[2][1] = E
nt[2][2] = F
nt[2][3] = G
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
After delete
of second index, Count = 2
NESTED TABLE
ELEMENTS
nt[1][1] = A
nt[1][2] = B
nt[1][3] = C
nt[3][1] = I
nt[3][2] = J
nt[3][3] = K
After delete of entire nested table, Count = 0
Ex3:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is table of t1 index by
binary_integer;
ibt t2;
flag boolean;
BEGIN
dbms_output.put_line('Count = ' ||
ibt.count);
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;
ibt(1)(1) := 'a';
ibt(4)(5) := 'b';
ibt(5)(1) := 'c';
ibt(6)(2) := 'd';
ibt(8)(3) := 'e';
ibt(3)(4) := 'f';
dbms_output.put_line('INDEX-BY TABLE ELEMENTS');
dbms_output.put_line('ibt([1][1] = ' ||
ibt(1)(1));
dbms_output.put_line('ibt([4][5] = ' ||
ibt(4)(5));
dbms_output.put_line('ibt([5][1] = ' ||
ibt(5)(1));
dbms_output.put_line('ibt([6][2] = ' ||
ibt(6)(2));
dbms_output.put_line('ibt([8][3] = ' ||
ibt(8)(3));
dbms_output.put_line('ibt([3][4] = ' ||
ibt(3)(4));
dbms_output.put_line('First Index = ' ||
ibt.first);
dbms_output.put_line('Last Index = ' ||
ibt.last);
dbms_output.put_line('Next Index = ' ||
ibt.next(3));
dbms_output.put_line('Prior Index = ' ||
ibt.prior(8));
ibt(1)(2) := 'g';
ibt(1)(3) := 'h';
ibt(1)(4) := 'i';
ibt(1)(5) := 'k';
ibt(1)(6) := 'l';
ibt(1)(7) := 'm';
ibt(1)(8) := 'n';
dbms_output.put_line('Count = ' ||
ibt.count);
dbms_output.put_line('INDEX-BY TABLE ELEMENTS');
for i in 1..8 loop
dbms_output.put_line('ibt[1][' ||
i || '] = ' || ibt(1)(i));
end loop;
dbms_output.put_line('ibt([4][5] = ' ||
ibt(4)(5));
dbms_output.put_line('ibt([5][1] = ' ||
ibt(5)(1));
dbms_output.put_line('ibt([6][2] = ' ||
ibt(6)(2));
dbms_output.put_line('ibt([8][3] = ' ||
ibt(8)(3));
dbms_output.put_line('ibt([3][4] = ' ||
ibt(3)(4));
flag := ibt.exists(3);
if flag = true then
dbms_output.put_line('Index 3
exists');
else
dbms_output.put_line('Index 3 exists');
end if;
ibt.delete(1);
dbms_output.put_line('After delete of
first index, Count = ' || ibt.count);
ibt.delete(4);
dbms_output.put_line('After delete of
fourth index, Count = ' || ibt.count);
dbms_output.put_line('INDEX-BY TABLE ELEMENTS');
dbms_output.put_line('ibt([5][1] = ' ||
ibt(5)(1));
dbms_output.put_line('ibt([6][2] = ' ||
ibt(6)(2));
dbms_output.put_line('ibt([8][3] = ' ||
ibt(8)(3));
dbms_output.put_line('ibt([3][4] = ' ||
ibt(3)(4));
ibt.delete;
dbms_output.put_line('After delete of
entire index-by table, Count = ' ||
ibt.count);
END;
Output:
Count = 0
No limit to
Index-by Tables
INDEX-BY TABLE
ELEMENTS
ibt([1][1] = a
ibt([4][5] = b
ibt([5][1] = c
ibt([6][2] = d
ibt([8][3] = e
ibt([3][4] = f
First Index =
1
Last Index = 8
Next Index = 4
Prior Index =
6
Count = 6
INDEX-BY TABLE
ELEMENTS
ibt[1][1] = a
ibt[1][2] = g
ibt[1][3] = h
ibt[1][4] = i
ibt[1][5] = k
ibt[1][6] = l
ibt[1][7] = m
ibt[1][8] = n
ibt([4][5] = b
ibt([5][1] = c
ibt([6][2] = d
ibt([8][3] = e
ibt([3][4] = f
Index 3 exists
After delete
of first index, Count = 5
After delete
of fourth index, Count = 4
INDEX-BY TABLE
ELEMENTS
ibt([5][1] = c
ibt([6][2] = d
ibt([8][3] = e
ibt([3][4] = f
After delete
of entire index-by table, Count = 0
Ex4:
DECLARE
type t1 is table of varchar(2) index by
binary_integer;
type t2 is table of t1 index by
binary_integer;
type t3 is table of t2;
nt t3 := t3();
c number := 65;
BEGIN
nt.extend(2);
dbms_output.put_line('Count = ' ||
nt.count);
for i in 1..nt.count loop
for j in 1..nt.count loop
for k in 1..nt.count loop
nt(i)(j)(k) := chr(c);
c := c + 1;
end loop;
end loop;
end loop;
dbms_output.put_line('NESTED TABLE ELEMENTS');
for i in 1..nt.count loop
for j in 1..nt.count loop
for k in 1..nt.count loop
dbms_output.put_line('nt[' || i ||
'][' || j || '][' || k || '] = ' ||
nt(i)(j)(k));
end loop;
end loop;
end loop;
END;
Output:
Count = 2
NESTED TABLE
ELEMENTS
nt[1][1][1] =
A
nt[1][1][2] =
B
nt[1][2][1] =
C
nt[1][2][2] =
D
nt[2][1][1] =
E
nt[2][1][2] =
F
nt[2][2][1] =
G
nt[2][2][2] =
H
OBJECTS USED IN THE
EXAMPLES
SQL> select * from student;
SNO
SNAME SMARKS
---------- -------------- ----------
1
saketh 100
2
srinu 200
3
divya 300
4
manogni 400
SQL> create or replace type
addr as object(hno number(2),city varchar(10));/
SQL> select * from employ;
ENAME JOB
ADDRESS(HNO, CITY)
----------
---------- -----------------------------
Ranjit clerk ADDR(11, 'hyd')
Satish manager
ADDR(22, 'bang')
Srinu engineer ADDR(33, 'kochi')
No comments:
Post a Comment