Object-Relational Features - Department of Computer and
Download
Report
Transcript Object-Relational Features - Department of Computer and
Department of Computer and Information Science,
School of Science, IUPUI
Object-Relational Features
Dale Roberts, Lecturer
Computer Science, IUPUI
E-mail: [email protected]
Dale Roberts
1
Object-Relational Databases
Types, Object Views, Methods
Nested Tables and Varying Arrays
Large Objects
Advanced OO Concepts
Dale Roberts
2
Types
Abstract datatypes groups related columns/data .
create type ADDRESS_TY as object
( Street VARCHAR2(50),
City
VARCHAR2(25),
State
CHAR(2),
Zip
NUMBER);
Abstract datatypes can be nested .
security – must have EXECUTE privilege to use a type.
set describe depth – see attributes within an object.
Dale Roberts
3
Types: indexing, selecting, manipulating
Create type PERSON_TY (
Name VARCHAR2(25),
Address ADDRESS_TY);
Create table CUSTOMER (
Cust_Id NUMBER,
Person PERSON_TY );
Select:
select c.Name, c.Person.Address.City
from CUSTOMER c
where c.Person.Address.City like ‘I%’;
Indexing:
create index IDX_CUST on CUSTOMER
(Person.Address.City);
Insert:
insert into CUSTOMER values
(1, PERSON_TY(‘xxxx’,
ADDRESS_TY(‘aaaa’, ‘bbb’, ccc’)));
Dale Roberts
4
Object Views
create table CUSTOMER
(Cust_id
NUMBER PRIMARY KEY,
Name
VARCHAR2(25),
Street
VARCHAR2(50),
City
VARCHAR2(25),
State
CHAR(2),
Zip
NUMBER);
create type ADDRESS_TY as object
( Street
VARCHAR2(50),
City
VARCHAR2(25),
State
CHAR(2),
Zip
NUMBER);
Inserting into original table:
insert into CUSTOMER values
( 123,
‘name’,
‘Street’, ‘City’,
‘ST’, 99999 );
create type PERSON_TY (
Name
VARCHAR2(25),
Address ADDRESS_TY);
create or replace view CUSTOMER_OV
(Cust_id, person) as
select Cust_id,
PERSON_TY (Name,
ADDRESS_TY(street, city, state, zip))
from CUSTOMER;
Dale Roberts
Inserting into object view:
insert into CUSTOMER_OV values
( 123,
PERSON_TY(‘name’,
ADDRESS_TY(
‘Street’, ‘City’,
‘ST’, 99999 )));
5
INSTEAD OF Triggers
Views usually cannot use standard table-based PL/SQL Triggers,
but they can use INSTEAD OF triggers. Works with either normal views or
object views.
create trigger AUTHOR_PUBLISHER_UPDATE
instead of UPDATE on AUTHOR_PUBLISHER
for each row
begin
…
end;
Dale Roberts
6
Methods
Methods allow types to have “behavior”.
Methods are declared inside CREATE TYPE,
they are defined with CREATE TYPE BODY.
create type PERSON_TY (
Name
VARCHAR2(25),
Address ADDRESS_TY,
Birthday DATE,
member function AGE (BirthDate IN DATE) return NUMBER);
create or replace type body PERSON_TY as
member function AGE (Birthdate DATE) return NUMBER is
begin
return ROUND(sysdate – Birthdate);
end;
end;
Dale Roberts
7
Collections: Varying Arrays
Definition – a varying array is a set of objects, each of same type, seen as a
column within a table. The size is limited when created.
Creating a Varying Array
1. Create type TOOL_TY as object (…);
2. Create type TOOLS_VA as varray(5) of VARCHAR2(25);
3. Create table TOOL_BORROWER (…, Tools TOOLS_VA);
Describing – requires queries on data dictionary to show varray structure.
Selecting – requires PL/SQL, see p. 571-572
Inserting –
insert into TOOL_BORROWER values (‘name’,
TOOLS_VA(‘tool1’, ‘tool2’, ‘tool3’));
Storage – stored in-line, ie. within table data segment.
Dale Roberts
8
Collections: Nested Tables
Definition – a nested table is a table within a table.
Their size is not limited as with varying arrays.
Creating a Nested Table
1. Create type TOOL_TY as object (…);
2. Create type TOOLS_NT as table of TOOL_TY;
3. Create table TOOL_BORROWER (…,
nested table Tools TOOLS_NT);
Describing – set describe depth will allow the describe command to show nested tables.
Selecting – requires the TABLE() function (previous to 9i use THE() function).
This “flatten’s” or de-normalizes the nested table’s relationship.
Select BreederName, N.Name, N.birthdate
from BREEDER, TABLE(breeder.Animals) N
Inserting –
Storage – Nested tables can be stored in or out-of-line. Recommend they be stored
out-of-line with a storage clause to improves query performance.
insert into TABLE(select Animals
from BREEDER where Breedername = ‘x’)
values (ANIMAL_TY (‘x’,’y’,’z’));
Dale Roberts
9
Large Object Datatypes
BLOB – binary field up to 4Gb.
CLOB – character field up to 4Gb.
NCLOB – CLOB that supports MBCS (multi-byte character set).
BFILE – read-only binary data stored in a file outside of the database.
Dale Roberts
10
LOB Storage, Initialization
LOB Storage – BLOB and (N)CLOB columns are stored out-of-line.
They require the CREATE TABLE statement include a LOB
STORAGE clause to tell where the LOB segment is stored. BFILE
columns are by their nature stored out-of-line.
LOBs use at least 1 entire data block per row in the LOB segment.
Normal DATA segments store multiple rows per block (up to 100% PCTFREE).
Initializing LOBs – use EMPTY_BLOB or EMPTY_CLOB instead of
NULL to initialize a LOB.
• NULL will create a LOB with no data.
• EMPTY_BLOB/CLOB will create an empty “LOB locater” (pointer)
without allocating the empty block.
Dale Roberts
11
DBMS_LOB
LOB data is manipulated with the DBMS_LOB package.
For all LOBs:
•
•
•
•
•
•
•
•
•
•
•
•
•
•
APPEND
COMPARE
COPY
ERASE
GETCHUNKSIZE
GETLENGTH
INSTR
ISOPEN
OPEN
READ
SUBSTR
TRIM
WRITE
WRITEAPPEND
BFILE specific functions:
•
•
•
•
•
•
•
FILEOPEN
FILECLOSE
FILECLOSEALL
FILEEXISTS
FILEGETNAME
FILEISOPEN
LOADFROM FILE
Temporary LOB functions:
•
•
•
CREATETEMPORARY
FREETEMPORARY
ISTEMPORARY
Dale Roberts
12
BFILE
Read-only binary data stored in a file outside of the database.
CREATE DIRECTORY PROPOSAL_DIR AS ‘C:\My Documents’;
Insert into thetable ( col1, bfilecol ) values
( ‘value1’, BFILENAME(‘proposal_dir’, ‘myfile.doc’);
Deleting LOBs – internal LOBs when deleted have both their data and
LOB locater deleted. Deleting a BFILE only deletes the locater, the file
is read only and thus not changed.
Dale Roberts
13
row objects vs. column objects
Row objects
Column objects
Dale Roberts
14
REF, DEREF, VALUE
REF
DEREF
VALUE
Dale Roberts
15
Acknowledgements
Loney, Oracle Database 10g The Complete Reference
4/2/2016
Dale Roberts
16