1/28/16

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;





DATA MODEL

Ø  ALL_COLL_TYPES

Ø  ALL_TYPES

Ø  DBA_COLL_TYPES

Ø  DBA_TYPES

Ø  USER_COLL_TYPES


Ø  USER_TYPES




0 comments:

Post a Comment