Fundamentals of SQL - Basics with example - 28

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

1     Varrays

2     Nested tables

3     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