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
No comments:
Post a Comment