ABSTRACT DATA TYPES

ABSTRACT DATA TYPES

Sometimes we may want type which holds all types of data including numbers, chars and special characters something like this. we cannot achieve this using pre-defined types.


we can define custom types which hold 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;

Leave a Reply