ABSTRACT DATA TYPES
Some times you may want type which holds all types of data
including numbers, chars and special characters something like this. You can
not achieve this using pre-defined types.
You can define custom types which holds your desired data.
Ex:
Suppose in a
table we have address column which holds hno and city information.
We will define a
custom type which holds both numeric as well as char data.
CREATING ADT
SQL> create type addr as object(hno
number(3),city varchar(10)); /
CREATING TABLE BASED ON ADT
SQL> create table student(no
number(2),name varchar(2),address addr);
INSERTING DATA INTO ADT TABLES
SQL> insert into student
values(1,'a',addr(111,'hyd'));
SQL> insert into student values(2,'b',addr(222,'bang'));
SQL> insert into student
values(3,'c',addr(333,'delhi'));
SELECTING DATA FROM ADT TABLES
SQL> select * from student;
NO NAME
ADDRESS(HNO, CITY)
--- -------
-------------------------
1 a
ADDR(111, 'hyd')
2 b
ADDR(222, 'bang')
3 c
ADDR(333, 'delhi')
SQL> select
no,name,s.address.hno,s.address.city from student s;
NO NAME ADDRESS.HNO ADDRESS.CITY
---- -------
----------------- ----------------
1 a 111 hyd
2 b 222 bang
3 c 333 delhi
UPDATE WITH ADT TABLES
SQL> update student s set s.address.city = 'bombay' where
s.address.hno = 333;
SQL> select no,name,s.address.hno,s.address.city from student s;
NO NAME ADDRESS.HNO ADDRESS.CITY
---- -------
----------------- ----------------
1 a 111 hyd
2 b 222 bang
3 c 333 bombay
DELETE WITH ADT TABLES
SQL> delete student s where s.address.hno = 111;
SQL> select no,name,s.address.hno,s.address.city from student s;
NO NAME ADDRESS.HNO ADDRESS.CITY
---- -------
----------------- ----------------
2 b 222 bang
3 c 333 bombay
DROPPING ADT
SQL> drop type addr;
OBJECT VIEWS AND METHODS
OBJECT VIEWS
If you want to implement objects with the existing table,
object views come into picture.
You define the object and create a view which relates this
object to the existing table nothing but object view.
Object views are used to relate the user defined objects to
the existing table.
Ex:
1) Assume that
the table student has already been created with the following columns
SQL> create table student(no number(2),name varchar(10),hno
number(3),city
varchar(10));
2) Create the
following types
SQL> create type addr as object(hno
number(2),city varchar(10));/
SQL> create type stud as object(name
varchar(10),address addr);/
3) Relate the
objects to the student table by creating the object view
SQL> create view student_ov(no,stud_info)
as select no,stud(name,addr(hno,city))
from student;
4) Now you can
insert data into student table in two ways
a) By regular insert
SQL> Insert into student
values(1,’sudha’,111,’hyd’);
b) By using object view
SQL> Insert into student_ov
values(1,stud(‘sudha’,addr(111,’hyd’)));
METHODS
You can define methods which are nothing but functions in
types and apply in the tables which holds the types;
Ex:
1) Defining methods
in types
SQL> Create type stud as object(name
varchar(10),marks number(3),
Member function makrs_f(marks in number) return number,
Pragma restrict_references(marks_f,wnds,rnds,wnps,fnps));/
2) Defining type
body
SQL> Create type body stud as
Member function marks_f(marks in number) return number is
Begin
Return (marks+100);
End marks_f;
End;/
3) Create a
table using stud type
SQL> Create table student(no
number(2),info stud);
4) Insert some
data into student table
SQL> Insert into student
values(1,stud(‘sudha’,100));
5) Using method
in select
SQL> Select s.info.marks_f(s.info.marks)
from student s;
-- Here we are using the pragma restrict_references to avoid
the writes to the
Database.
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
1 ALL_COLL_TYPES
2 ALL_TYPES
3 DBA_COLL_TYPES
4 DBA_TYPES
5 USER_COLL_TYPES
6 USER_TYPES
No comments:
Post a Comment