190 likes | 388 Views
Oracle Objects. Object Oriented Database Approach. Overview. Oracle supported concepts - features are implemented as extensions on relational engine Defining Oracle objects Using Oracle objects Object views. Oracle Supported OO Concepts.
E N D
Oracle Objects Object Oriented Database Approach
Overview • Oracle supported concepts - features are implemented as extensions on relational engine • Defining Oracle objects • Using Oracle objects • Object views
Oracle Supported OO Concepts • An object has a name, a standard representation, and a standard collection of operations that affect it (methods) • Abstract data types model classes of data within the database • Abstract data types inherit the representation of their parents • hierarchies of abstract data types • implementation inheritance - inherit behavior of parents
Oracle Supported OO Concepts con’t • Approximate encapsulation • encapsulation - data structure can only be accessed via a defined set of methods • relational system cannot have means of accessing data limited • No polymorphism • ability of same instruction to be interpreted different ways by different objects
Abstract Data types • Data types that consist of one or more subtypes • Can be nested • Can reference other abstract datatypes • Example CREATE TYPE ADDR_TY AS OBJECT (STREET VARCHAR2(50), CITY VARCHAR2(25), STATE CHAR(2), ZIP VARCHAR(9)) /
Embedded Objects - Collectors • Nested table - • table within a table • collection of rows represented as a column within main table • stores 1:M relationships • Varying arrays • set of objects each with the same data type • nested table with a limited set of rows (size limited when created) • Store repeating attributes in table • Cannot be indexed
Large Objects - LOB • BLOB - binary data that can be extended to 4 GB • CLOB - character data up to 4 GB • NCLOB - stores CLOB data for multibyte character sets Stored inside database, can be single row • BFILE - pointer to external file. File exists on OS
Example of Simple Object CREATE TYPE PERSON_TY AS OBJECT (NAME VARCHAR2(25), ADDRESS ADDR_TY) / Note: data described not stored, cannot store data in types CREATE TABLE STUDENT (STUDENT_ID VARCHAR2(9), PERSON PERSON_TY) / Must own data type or be granted access. Need execute access for methods including constructor methods. Avoid synonyms
Inserting Records • Constructor methods - program named after the data type, parameters names of attributes defined for datatype • insert into student values (100, person_ty(‘Mary Ann Robbert’, addr_ty(‘122 North St.’, ’Watham’,’MA’, ‘02579’))) constructor methods
Querying • Select student_id, person.name ... • COLUMN.ATTRIBUTE • Select person.addr.street … • COLUMN.COLUMN.ATTRIBUTE • SELECT STUDENT_ID, S.PERSON.NAME, S.PERSON.ADDRESS.STATE FROM STUDENT S
Object Views • Bridge between relational and object • Allows adding OO concepts on top of relational tables • Gives benefit of relational storage with OO structures • Benefits • create abstract data types within tables that already exist • flexibility to treat base table as relational table or object table
Creating Object View based on Existing Table • Assume person, and address types plus a relational student table CREATE VIEW STUDENT_OV (STUDENT_ID, PERSON) AS SELECT STUDENT_ID,PERSON_TY(NAME,ADDR_TY(STREET,CITY,STATE,ZIP))FROM STUDENT
Updating Through Object View • Instead of Triggers • use on object views or relational views • change values through views • Use with PL/SQL code • create trigger xyzinstead of update on viewfor each row ….
Methods CREATE TYPE PERSON_TY3 AS OBJECT (NAME VARCHAR2(25), ADDRESS ADDR_TY, BIRTHDATE DATE, MEMBER FUNCTION AGE(BIRTHDATE IN DATE) RETURN NUMBER)
Methods • CREATE TYPE PERSON_TY4 AS OBJECT (NAME VARCHAR2(25), ADDRESS ADDR_TY, BIRTHDATE DATE, MEMBER FUNCTION AGE(BIRTHDATE IN DATE) RETURN NUMBER, PRAGMA RESTRICT_REFERENCES(AGE,WNDS)) WNDS – Write No Database State RNDS - Read (no queries) WNPS – No packaged variables changed RNPS – no packg var referenced
Methods • create or replace type body person_ty4 as member function AGE(birthdate date) return number is BEGIN RETURN ROUND(SYSDATE - BIRTHDATE); END; END
Example • CREATE TABLE STUDENT4 (SID NUMBER, PERSON PERSON_TY4); • SELECT S.AGE(STUDENT4.BIRTHDATE) FROM STUDENT4 S
Managing Methods • Cannot drop or recreate type that is in use by a table • Use ALTER TYPE to add new methods • Grant execute on type gives priviledges to methods
OO ANALYSIS AND DESIGN • Goes beyond normalization (relating each attribute to primary key), seeks groups of columns that define a common object representation • Uses types that are: • reused • will always behave in the same manner