Thursday, September 15, 2011

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

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













at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Oracle Escape Characters

Oracle allows the assignment of special escape characters to tell Oracle that the character is interpreted literally.  Certain characters ...

  • Columns to Rows
                        UNPIVOT (11g) Transforming columns into rows is easily accomplished with the SELECT command's UNPIVOT clause, intro...
  • Rows to Strings in Different Methods
    In this section show how to collect values from multiple rows into a single, delimited string. This process is commonly known as "strin...
  • Oracle Optimizer hint tips
    With each new release of Oracle, there are more tuning controls and opportunities for improving the execution speed of SQL. In an  article ...