122. 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

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

Subscribe to: Post Comments (Atom)