PLSQL-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
Share on Google Plus

About Mindsforest

    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment