VARRAYS AND NESTED TABLES

VARRAYS AND NESTED TABLES

VARRAYS
A varying array allows us 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

Related posts:

Leave a Reply