SQL-ABSTRACT DATA TYPES

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;