Object Relational Features in Oracle Database Cyrus Shahabi Computer Science Department University of Southern California shahabi usc edu C Shahabi 1 View of the Database World C Shahabi 2 Oracle Database History of Oracle database In 1979 Oracle Version 2 introduced An early commercial relational database system In 1997 Oracle version 8 released Support for object oriented development and multimedia applications Object Relational DBMS In 1999 Oracle 8i released Tuned with the needs of the Internet Web In 2001 Oracle 9i released Query intensive data warehouses and demanding Internet applications XML Text In 2003 Oracle 10g released Support for Grid Computing In 2007 Oracle 11g released Automatic memory disk and memory management Extended features e g 3D capabilities C Shahabi 3 Object Relational Elements in Oracle 10g Object Oriented Concepts Objects Methods Object Tables Type Inheritance Collections Object Types and References C Shahabi 4 Object Oriented Concepts Abstraction and Encapsulation Provided by Abstract Data Types ADT Abstraction is the process of identifying the essential aspects of an entity and ignoring the unimportant properties Focus on what an object is and what it does rather than how it should be implemented Encapsulation or information hiding provides data independence by separating the external aspects of an object from its internal details which is hidden from the outside world Classes C Shahabi Classes A class is a blueprint or prototype from which objects are created A group of objects with the same attributes and methods Hence the attributes and the associated methods are defined once for the class rather than separately for each object Attributes or instance variables describe the current state of an object the notation for attribute object name attribute name 5 Object Oriented Concepts Methods define the behavior of the object They can be used to change the object s state by modifying its attribute values or to query the value of the selected attributes A method consists of a name and a body that performs the behavior associated with the method name notation objectname method name Attributes The instances of a class are those objects belonging to a class C Shahabi 6 Advantages of ORDBMS Enables reuse and sharing u Ability to extend the DBMS server to perform standard functionality centrally rather than have it coded in each application Example Embedded Functions it saves having to define it in each application that needs it Ability and support for complex objects and rich data types termed abstract data types ADTs u Complex applications such as Oracle Spatial Support for Inheritance u C Shahabi Inherent attributes and behavior of the pre existing classes hence ease of definition and programming 7 Oracle Object Types User Defined data types classes Consist of 2 parts attributes methods CREATE TYPE person type AS OBJECT name VARCHAR2 30 phone VARCHAR2 20 attributes declared MEMBER FUNCTION get areacode RETURN VARCHAR2 method This slash needed to get Oracle process this statement Defining an object type does not allocate any storage The body of method is defined in a separate CREATE TYPE BODY statement written in PL SQL or any other languages DROP TYPE person type First drop all tables and other types using person type C Shahabi 8 Oracle Objects Definition u Actual instance of the defined object type u Storages are allocated to an object and values are assigned to the attributes of an object CREATE TABLE contacts contact person type c date DATE object type can be used like any other built in data types INSERT INTO contacts VALUES person type Tommy Trojan 213 740 1114 instance 24 Jan 2004 person type is instantiated and values are assigned to the attributes of the object instance C Shahabi 9 Oracle Methods Definition u Functions procedures declared in the object type definition to implement behavior of the object of that type u Written in PL SQL or virtually any other languages Java C Method types u Member method Defined on object instance s data u Static method Invoked on the object type not its instances Can be used to the operations that are global to the type e g initialization u Constructor method Built in constructor function like in C C Shahabi 10 Member Method Member methods are used to access an object instance s values CREATE OR REPLACE TYPE BODY person type AS MEMBER FUNCTION get areacode RETURN VARCHAR2 IS BEGIN RETURN SUBSTR phone 1 3 END get areacode END Define the body of the method using CREATE OR REPLACE TYPE BODY SELECT c contact get areacode FROM contacts c Invoke a member method C CONTACT GET AREACODE 213 C Shahabi 11 Constructor Method Every object type has a constructor method implicitly defined by system Returns a new instance of the user defined object type and sets up the values of its attributes The name of constructor method is the same as the name of the object type p person type Scott Tiger 321 123 1234 Built in constructor method person type att1 att2 is invoked to create a new object instance of person type specify values for its attributes name phone and set the object into a variable p INSERT INTO contacts VALUES person type Scott Tiger 321 123 1234 10 Feb 2004 Same thing occurs here C Shahabi 12 Oracle Object Tables Object Table special type of table each row represents an object CREATE TYPE person type AS OBJECT name VARCHAR2 30 phone VARCHAR2 20 CREATE TABLE person table OF person type INSERT INTO person table VALUES person type Scott Tiger 321 123 1234 SELECT VALUE p FROM person table p WHERE p name Scott Tiger Single column table each row is a person type object Perform object oriented operations Comparing to a relational table CREATE TABLE person table name phone VARCHAR2 30 VARCHAR2 20 INSERT INTO person table VALUES Tommy Trojan 213 740 1212 SELECT name phone FROM person table Multi column table treat person table as a relational table C Shahabi 13 Methods to Compare Objects 1 Define a special kind of member methods to compare objects Define either a map method or an order method in an object type u Map Method Map object instances into one of the scalar types DATE CHAR NUMBER CREATE TYPE circle type AS OBJECT x NUMBER y NUMBER r NUMBER MAP MEMBER FUNCTION get area RETURN NUMBER CREATE TYPE BODY circle type AS MAP MEMBER FUNCTION get area RETURN NUMBER IS BEGIN RETURN 3 14 r r END get area END SELECT FROM circles c ORDER BY VALUE c Result should be ordered by circles area C Shahabi 14 Methods to Compare Objects 2 u