be happy and make others to be happy: PLSQL-DIFFERENCES AMONG COLLECTIONS,MULTILEVEL COLLECTIONS

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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')