Oracle Objects Object Oriented Database Approach Overview Oracle

Oracle Objects Object Oriented Database Approach

Overview Oracle supported concepts - features are implemented as extensions on relational engine n Defining Oracle objects n Using Oracle objects n Object views n

Oracle Supported OO Concepts An object has a name, a standard representation, and a standard collection of operations that affect it (methods) n Abstract data types model classes of data within the database n Abstract data types inherit the representation of their parents n u hierarchies of abstract data types u implementation inheritance - inherit behavior of parents

Oracle Supported OO Concepts con’t n Approximate encapsulation u encapsulation - data structure can only be accessed via a defined set of methods u relational system cannot have means of accessing data limited n No polymorphism u ability of same instruction to be interpreted different ways by different objects

Abstract Data types that consist of one or more subtypes n Can be nested n Can reference other abstract datatypes n Example n CREATE TYPE ADDR_TY AS OBJECT (STREET VARCHAR 2(50), CITY VARCHAR 2(25), STATE CHAR(2), ZIP VARCHAR(9)) /

Embedded Objects - Collectors n Nested table u table within a table u collection of rows represented as a column within main table u stores 1: M relationships n Varying arrays u set of objects each with the same data type u nested table with a limited set of rows (size limited when created) u Store repeating attributes in table n Cannot be indexed

Large Objects - LOB BLOB - binary data that can be extended to 4 GB n CLOB - character data up to 4 GB n NCLOB - stores CLOB data for multibyte character sets Stored inside database, can be single row n BFILE - pointer to external file. File exists on OS n

Example of Simple Object CREATE TYPE PERSON_TY AS OBJECT (NAME VARCHAR 2(25), ADDRESS ADDR_TY) / Note: data described not stored, cannot store data in types CREATE TABLE STUDENT (STUDENT_ID VARCHAR 2(9), 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 n insert into student values (100, person_ty(‘Mary Ann Robbert’, addr_ty(‘ 122 North St. ’, ’Watham’, ’MA’, ‘ 02579’))) n constructor methods

Querying n Select student_id, person. name. . . u COLUMN. ATTRIBUTE n Select person. addr. street … u COLUMN. ATTRIBUTE n SELECT STUDENT_ID, S. PERSON. NAME, S. PERSON. ADDRESS. STATE FROM STUDENT S

Object Views Bridge between relational and object n Allows adding OO concepts on top of relational tables n Gives benefit of relational storage with OO structures n Benefits n u create abstract data types within tables that already exist u 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 n

Updating Through Object View n Instead of Triggers u use on object views or relational views u change values through views n Use with PL/SQL code u create trigger xyz instead of update on view for each row ….

Methods CREATE TYPE PERSON_TY 3 AS OBJECT (NAME VARCHAR 2(25), ADDRESS ADDR_TY, BIRTHDATE, MEMBER FUNCTION AGE(BIRTHDATE IN DATE) RETURN NUMBER)

Methods n CREATE TYPE PERSON_TY 4 AS OBJECT (NAME VARCHAR 2(25), ADDRESS ADDR_TY, BIRTHDATE, 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 n create or replace type body person_ty 4 as member function AGE(birthdate) return number is BEGIN RETURN ROUND(SYSDATE BIRTHDATE); END

Example n CREATE TABLE STUDENT 4 (SID NUMBER, PERSON_TY 4); n SELECT S. AGE(STUDENT 4. BIRTHDATE) FROM STUDENT 4 S

Managing Methods Cannot drop or recreate type that is in use by a table n Use ALTER TYPE to add new methods n Grant execute on type gives priviledges to methods n

OO ANALYSIS AND DESIGN Goes beyond normalization (relating each attribute to primary key), seeks groups of columns that define a common object representation n Uses types that are: n u reused u will always behave in the same manner