Abstract datatype and Object view & methods
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.