SQL-OBJECT VIEWS AND METHODS

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.