Transcript 4/9/02 Oracle Objects

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 xyz
instead of update on view
for 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