Friday, May 2, 2014

VARRAYS AND NESTED TABLES

VARRAYS

A varying array allows you to store repeating attributes of a record in a single row but with limit.

Ex:
    1) We can create varrays using oracle types as well as user defined types.
         a) Varray using pre-defined types
              SQL> Create type va as varray(5) of varchar(10);/
         b) Varrays using user defined types
              SQL> Create type addr as object(hno number(3),city varchar(10));/
              SQL> Create type va as varray(5) of addr;/
    2) Using varray in table
         SQL> Create table student(no number(2),name varchar(10),address va);
    3) Inserting values into varray table
         SQL> Insert into student values(1,’sudha’,va(addr(111,’hyd’)));
         SQL> Insert into student values(2,’jagan’,va(addr(111,’hyd’),addr(222,’bang’)));
    4) Selecting data from varray table
         SQL> Select * from student;
         -- This will display varray column data along with varray and adt;
         SQL> Select no,name, s.* from student s1, table(s1.address) s;
         -- This will display in general format
    5) Instead of s.* you can specify the columns in varray
         SQL> Select no,name, s.hno,s.city from student s1,table(s1.address) s;

    -- Update and delete not possible in varrays.
    -- Here we used table function which will take the varray column as input for producing
        output excluding varray and types.

      
       




       NESTED TABLES

A nested table is, as its name implies, a table within a table. In this case it is a table that is represented as a column within another table.
Nested table has the same effect of varrays but has no limit.

Ex:
    1) We can create nested tables using oracle types and user defined types which has no limit
         a) Nested tables using pre-defined types
              SQL> Create type nt as table of varchar(10);/
         b) Nested tables using user defined types
              SQL> Create type addr as object(hno number(3),city varchar(10));/
              SQL> Create type nt as table of addr;/
    2) Using nested table in table
         SQL> Create table student(no number(2),name varchar(10),address nt) nested table  
                  address store as student_temp;
    3) Inserting values into table which has nested table
         SQL> Insert into student values (1,’sudha’,nt(addr(111,’hyd’)));
         SQL> Insert into student values (2,’jagan’,nt(addr(111,’hyd’),addr(222,’bang’)));
    4) Selecting data from table which has nested table
         SQL> Select * from student;
         -- This will display nested table column data along with nested table and adt;
         SQL> Select no,name, s.* from student s1, table(s1.address) s;
         -- This will display in general format
    5) Instead of s.* you can specify the columns in nested table
         SQL> Select no,name, s.hno,s.city from student s1,table(s1.address) s;
    6) Inserting nested table data to the existing row
         SQL> Insert into table(select address from student where no=1)
                  values(addr(555,’chennai’));
    7) Update in nested tables
         SQL> Update table(select address from student where no=2) s set s.city=’bombay’ where
                 s.hno = 222;
    8) Delete in nested table

         SQL> Delete table(select address from student where no=3) s where s.hno=333;

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.