Abstract data types in sql


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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s