Oracle數(shù)據(jù)庫(kù)中的OOP概念
Oracle 中的OOP概念：對(duì)象類型，可變數(shù)組，嵌套表，對(duì)象表，對(duì)象視圖。
對(duì)象類型：
優(yōu)點(diǎn)：
1) 更容易與Java, C++編寫(xiě)的對(duì)象應(yīng)用程序交互
2) 獲取便捷。一次對(duì)象類型請(qǐng)求就可以從多個(gè)關(guān)系表中獲取信息，通過(guò)一次網(wǎng)絡(luò)往復(fù)即可返回
語(yǔ)法：
CREATE [OR REPLACE] TYPE type_name
{{AS| IS } OBJECT | UNDER super_type}
{
attribute_name datatype[,attribute_name datatype]… ---成員變量
[{MAP | ORDER} MEMBER function_name,] ---排序函數(shù)
[{FINAL | NOT FINAL} MEMBER function_name,] ---可否繼承的成員函數(shù)
[{INSTANTIABLE | NOT INSTANTIABLE } MEMBER function_name,] ---可否實(shí)例化的成員函數(shù)
[{MEMBER | STATIC } function_name,] ---靜態(tài)、非靜態(tài)成員函數(shù)
}[{FINAL | NOT FINAL}] ---對(duì)象可否繼承
[{INSTANTIABLE | NOT INSTANTIABLE }] ---對(duì)象可否實(shí)例化
/
對(duì)象類型的主體部分(即函數(shù)的實(shí)現(xiàn)部分，可選的)：
CREATE [OR REPLACE]
TYPE BODY type_name {AS| IS }
[{MAP | ORDER} MEMBER function_body,] ---排序函數(shù)
[{MEMBER | STATIC } function_name,] ---靜態(tài)、非靜態(tài)成員函數(shù)
END;
/
例如：
create or replace
type person as object(
first_name varchar2(100),
last_name varchar2(100))
/
屬性類型可以是任何oracle 數(shù)據(jù)類型(包括自定義)，除了如下：
LONG和LONG RAW
NCHAR、NCLOB 、NVARCHAR2
ROWID、UROWID
PL/SQL的特定類型：%TYPE %ROWTYPE
查看：
Desc person
構(gòu)造函數(shù)：
set serveroutput on
declare
l_person person
begin
l_person := person(‘Donny’,’Chen’);
dbms_output.putline(l_person.first_name);
end;
/
構(gòu)造函數(shù)要接受對(duì)象類型的所有屬性作為參數(shù)。因?yàn)檫@些參數(shù)沒(méi)有默認(rèn)值，即使是null，也要提供。
舉例：
表中的對(duì)象類型：
對(duì)象類型可以作為數(shù)據(jù)庫(kù)中的列，所以稱為列對(duì)象
create table person_table
(
name person,
age number)
/
set desc depth all
desc person_table
set desc depth 1
插入數(shù)據(jù)：
insert into person_table
values(person(‘Donny’,’Chen’),30);
declare
l_person person
begin
l_person := person(‘Hua’,’Li’);
insert into person_table values(l_person,33);
end;
/
查詢數(shù)據(jù)：
select * from person_table
訪問(wèn)對(duì)象類型的各個(gè)屬性：
select p.name.first_name
from person_table p
/
為避免名稱解析問(wèn)題，要求查詢對(duì)象類型的屬性的時(shí)候，使用表別名。否則報(bào)錯(cuò)，
舉例：對(duì)象中的對(duì)象(合成)：create or replace
type employee as object(
name person,
empno number,
hiredate date)
#p#
修改和刪除對(duì)象：
9i之前，當(dāng)建立的對(duì)象類型，以及依賴于此類型的對(duì)象或表之后，就無(wú)法再修改此對(duì)象類型了(增加刪除屬性和成員函數(shù))。唯一的辦法是撤銷所有以來(lái)，即刪除依賴于此類型的對(duì)象或表。9i新特性，可以修改被以來(lái)的對(duì)象類型，成為類型演化。
有兩種方法：INVALIDATE 和 CASCADE
INVALIDATE比如：desc person_table
改變person類型，增加新屬性ssn
alter type person
add attribute ssn varchar2(11) INVALIDATE;
desc person (bug可能需要新開(kāi)一個(gè)session)
INVALIDATE選項(xiàng)使的所有依賴于person類型的對(duì)象和表標(biāo)記為INVALID,比如：
Desc person_table
需要手工驗(yàn)證person_table:
alter table person_table upgrade including data;
desc person_table
upgrade including data表示根據(jù)新類型，物理上更新現(xiàn)有的數(shù)據(jù)的結(jié)構(gòu)，ssn 置為null。
也可以u(píng)pgrade not including data，不更新原有數(shù)據(jù)的結(jié)構(gòu)。Dml訪問(wèn)person實(shí)例數(shù)據(jù)的時(shí)候再更新。
Select * from person_table
CASCADE比如：
alter type person
add attribute dob date
cascade not including table data
/不用手工驗(yàn)證依賴此對(duì)象類型的表，由數(shù)據(jù)庫(kù)自動(dòng)驗(yàn)證。
Desc person
Desc person_table
因?yàn)閚ot including table data，沒(méi)有更新原有數(shù)據(jù)：
select * from person_table
刪除類型：force
方法：即對(duì)象中的過(guò)程和函數(shù),3種類型：
STATIC: 只能夠在對(duì)象類型上調(diào)用，不專屬于某個(gè)實(shí)例。
MEMBER: 專屬于某個(gè)特定的實(shí)例
CONSTRUCTOR: 構(gòu)造函數(shù)
create or replace
type employee as object(
name person,
empno number,
hiredate date,
sal number,
commission number,
member function total_compensation return number,
static function new(p_empno number,
p_person person) return employee)
/
desc employee
在類型主體實(shí)現(xiàn)這兩個(gè)方法：
create or replace
type body employee as
member function total_compensation return number is
begin
return nvl(self.sal,0) + nvl(self.commission, 0);
end;
static function new(p_empno number,
p_person person) return employee is
begin
return employee(p_person,p_empno,sysdate,10000,null);
end;
end;
/
比較抽象數(shù)據(jù)類型的數(shù)據(jù)：
declare
l_employee1 employee;
l_employee2 employee;
begin
l_employee1 :=employee.new(12345,null);
l_employee2 :=employee.new(67890,null);
if l_employee1= l_employee2 then
dbms_output.line_put(“They are equal”);
end if;
end;
/
使用map指定具體比較哪些屬性：
create or replace
type employee as object(
name person,
empno number,
hiredate date,
sal number,
commission number,
map member function convert return number)
/
create or replace
type body employee as
map member function convert return number is
begin
return self.empno;
end;
end;
/
再比較：
declare
l_employee1 employee;
l_employee2 employee;
begin
l_employee1 :=employee.new(12345,null);
l_employee2 :=employee.new(67890,null);
if l_employee1= l_employee2 then
dbms_output.line_put(“They are equal”);
end if;
if l_employee1> l_employee2 then
dbms_output.line_put(“employee1 is greater”);
end if;
if l_employee1< l_employee2 then
dbms_output.line_put(“employee2 is greater”);
end if;
end;
#p#Order 方法：
create or replace
type employee as object(
name person,
empno number,
hiredate date,
sal number,
commission number,
order member function match(p_employee employee) return integer)
/
create or replace
type body employee as
order member function match(p_employee employee) return integer is
begin
if self.empno> p_employee.empno then
return 1;
elseif self.empno< p_employee.empno then
return -1;
else
return 0;
end if;
end;
end;
/
繼承：
FINAL / NOT FINAL
對(duì)象默認(rèn)FINAL，表示不可以被繼承;
MEMBER方法也能指定是否FINAL，表示能否在子類中對(duì)他進(jìn)行覆寫(xiě)。默認(rèn)NOT FINAL
Create or replace type super_type as object(
N number,
Final member procedure cannot_override
)
not final
/create or replace type sub_type under super_type(
overriding member procedure cannot_override
)
/
show error
若super_type 改成final
INSTANTIABLE / NOT INSTANTIABLE
可否被實(shí)例化，后者類似于抽象類
create or replace type shape as object(
number_of_sides number,
not instantiable member function calculate_area return number
)
not instantiable not final
/
實(shí)例化該類型對(duì)象：
declare
l_shape shape;
begin
l_shape:=shape(2);
end;
/
可變數(shù)組(VARRAYS)：
create type employee_type as object(
employee_id number,
first_name varchar2(30),
last_name varchar2(30)
)
/
create type employee_list_type as varray(50) of employee_type
/
create table departments(
department_id number,
department_name varchar2(30),
manager employee_type,
employees employee_list_type)
/
insert into departments values
(10,
‘HR’,
employee_type(1,’Dony’,’Chen’),
employee_list_type(
employee_type(2,’Hua’,’Li’)
employee_type(3,’Wu’,’Wang’)
employee_type(4,’San’,’Zhang’))
)
/
column department_name format a13
column employee_type format a63 word_wrapped
select * from departments
/
嵌套表(Nested table):
create type order_item_type as object(
line_item_id number(3),
product_id number(6),
unit_price numbe(8,2),
quantity number(4)
)
/
create type order_item_list_type as table of order_item_type
/
create table orders(
order_id number(12) not null,
order_date date,
customer_id number(6),
order_items order_item_list_type)
nested table order_items store as order_items_tab
/
insert into orders values(
(1, sysdate, 10,
order_item_list_type(
order_item_type(1,2,3,4),
order_item_type(2,3,4,5)
))
/
多少個(gè)order_item_type ，無(wú)限制。
#p#
對(duì)象表：每行都代表一個(gè)對(duì)象，行對(duì)象。
創(chuàng)建對(duì)象：
create or replace
type address as object(
id number,
street varchar2(100),
state varchar2(2),
zipcode varchar2(11)
)
/
創(chuàng)建對(duì)象表：
create table address_table of address
/
desc address_table
插入數(shù)據(jù)：
可以像關(guān)系表一樣插入
insert into address_table values(1,’Oracle way’,’CA’,’90001’)
/也可以用默認(rèn)構(gòu)造函數(shù)插入對(duì)象：
insert into address_table
values(address(2,’Oracle way2’,’CA’,’90011’)
select * from address_ table
/VALUE()
以對(duì)象表別名作為參數(shù)，返回對(duì)象實(shí)例：
select value(a) from address_table a
/
REF數(shù)據(jù)類型：
在關(guān)系表中關(guān)聯(lián)對(duì)象
create table employee_location(
empno number,
loc_ref ref address scope is address_table)
/
loc_ref是個(gè)列，類型是指向address對(duì)象類型的ref, 即引用，或者指向address實(shí)例的指針。
scope is address_table 是可選的，表示ref指向的對(duì)象實(shí)例的位置，即只能指向address_table對(duì)象表中的address對(duì)象實(shí)例。
REF():
REF()函數(shù)可以建立指向?qū)ο蟊碇袑?duì)象實(shí)例的REF對(duì)象數(shù)據(jù)類型，以對(duì)象表的別名作為參數(shù)
插入數(shù)據(jù)：
insert into employee_location
select 12345, ref(a)
from address_table a
where id=1
/
insert into employee_location
select 45678, ref(a)
from address_table a
where id=2/
OID:
對(duì)象表中每一行對(duì)象都對(duì)應(yīng)一個(gè)唯一的OID,對(duì)象標(biāo)示符
Select * from employee_location
DEREF()
解析REF數(shù)據(jù)類型，返回真正指向的實(shí)例。以REF數(shù)據(jù)類型作為參數(shù)
select empno, deref(loc_ref)
from employee_location
懸空REF:
REF指向的對(duì)象實(shí)例被刪掉了，此時(shí)稱REF懸空(dangling)，說(shuō)明ref指向不存在的實(shí)例
Delete from address_table where id=1;
Select * from employee_location;
Select empno, deref(rec_loc) from employee_location;
懸空的ref會(huì)返回null，使用is dangling 確定哪些ref懸空：
select empno from employee_location
where loc_ref is dangling;
清除懸空的ref, 將ref更新為null:
update employee_location
set loc_ref =null
where loc_ref is dangling;
Select * from employee_location;
對(duì)象視圖：
在已有的關(guān)系型表上，建立對(duì)象模型：
關(guān)系表：
create table item
( itemcode varchar2(10),
item_on_hand number(10),
item_sode number(10)
);建立對(duì)象，使用相同的列：
create or replace type item_type as object
( itemcode varchar2(10),
item_on_hand number(10),
item_sode number(10)
);
建立對(duì)象視圖：
create view item_view of item_type
with object oid (itemcode)
as
select * from item
/
of item_type 說(shuō)明基于對(duì)象
with object oid (itemcode) 明確生成OID
通過(guò)視圖操作數(shù)據(jù)：
insert into item_view values(item_type(‘i102’,15,50));
MAKE_REF()
關(guān)系主表1：
create table itmefile(
itemcode varchar2(5) primary key,
itemdesc varchar2(20),
p_category varchar2(20),
qty_hand number(5),
re_level number(5),
max_level number(5),
itemrate number(9,2));
關(guān)系從表2：
create table order_detail(
orderno varchar2(5),
itemcode varchar2(5) foreign key references itemfile(itemcode)
qty_ord number(5),
qty_deld number(5)
)
【編輯推薦】
