1/28/16

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;




0 comments:

Post a Comment