oracle之oop相關

# FileName :oracle_oop.txt
# Write By : hgc 整理
# Create Date : 2007-04-26
# Last Modify : 2007-05-10
# Description :介紹Oracle中的對類型操作
# source: 北大青鳥音頻教程+互聯網google

OOP中涉及的概念﹕
對象﹕
     可重用的應用程序組件
屬性﹕
     有助于標識對象
方法﹕
     用于執行操作的過程和函數

OOP的特性﹕
封裝:
     將數據和函數包裝到一個單元中
繼承:
     在設計新功能對象時﹐繼承已經創建的對象的功能
多態:
     一個對象可以呈現多種形式的能力


OOP優點﹕
對象重用﹑維持標准﹑定義訪問路徑。


OOP中使用下列對象類型
抽象數據類型﹑可變數組﹑嵌套數組﹑對象表﹑對象視圖。


抽象數據類型﹕
    包含一個或多個子類型的數據類型
    不局限于oracle的標准數據類型
     可以用于其它的數據類型中
語法﹕
   Create type type_name as object(columns...)
  NOT FINAL關鍵字限制派生子類型
example:

1. 創建格式
  Create [Or Replace] Type 類型名 AS Object
   (屬性1 類型1...屬性n 類型n);
--類型可以是以前已經建的類型,除如部分
   long ﹑long raw﹑nchar﹑nclob﹑nvarchar2﹑rowid﹑urowid
   PL/SQL的特定類型%TYPE %ROWTYPE
   


2. 創建類型﹐類型可嵌套
--創建地址類型 
  Create Or Replace Type AddressType AS Object
   (  Province varchar(20),
      City     varchar(30),
      Details  varchar(40)
   ) 
/*創建基于前一個抽象類型的新的抽象類型--供應商類型
  Create or Replace Type ProviderType as Object
     (  ProvName varchar(40),
        Address   AddressType
      )
--注意 not final/final ,not instantiable /instantiable 及under用法.
  desc user_type_attrs

4. 創建抽象類型關系表
  Create table Provider --創建供應商表
    ( ProvID varchar(10),
      provDetail Providertype
     );

5. 基于抽象類型創建對象表
格式:Create Table 對象名稱 of 抽象對象類型﹔
實例:Create Table providerobj of Providertype;

desc user_tab_columns
desc user_dependencies

 
6. 使用構造類方法在關系對象表中插入記錄。
 insert into provider values('10001',
      providertype('吉林大學',AddressType('吉林省','長春','asdasd')))
7. 查看表中記錄
(1) select * from provider;
    --顯示結果不是平常的關系表形式
(2) 查看對象類型字段﹕查看(ProvDetail)
     select provdetail from provider;
(3) 查看對象類型中的屬性---也是一個對象類型﹐必須用別名
     查看供應商地址
    select P.ProvDetail.Address From Provider p;
(4) 查看對象類型中的屬性---也是一個對象類型﹐必須用別名
     如查看省份為吉林省的供應商編號﹐名稱:
  select ProvId,P.ProvDetail.ProvName from provider p
    where P.ProvDetail.Address.Province='吉林省'﹔

8. 更新關系對象表中的記錄
   Update Provider p
      set P.provDetail.ProvName='吉林大學醫大',
          P.ProvDetail.Address.Details='新民廣場15號'
       Where p.ProvId='10001';
--還可以用對象類型的Constructor構造方法進行更新           
  Update Provider Set ProvDetail=providerType('白求因醫大一院',
         AddressType('吉林省','長春市','新民廣場1號')) where ProvId='10001';
9. 刪除關系對象表中的記錄
   Delete From Provider P
     where P.provDetail.Address.Province='吉林省';
10. 對象類型的繼承
    Create type AddressType as object
    (
     privince varchar2(30),
     city     varchar2(40),
     street   varchar2(40)
     ) not final;
     Create type Myaddress under addresstype
     (
      state    varchar2(20)            
      )
11. 創建帶方法的對象類型
    Create OR Replace Type StudentType AS Object
     (--聲名屬性
       StuID       varchar(10),
       StuName     varchar(10),
       HOmeAddress Address,
      --聲名方法
      Member Function getAddress Return Address,
      Member Procedure setAddress(newAddress Address)
      );
     
      Create OR Replace Type Body StudentType AS
      Member Function getAddress return Address is
         Begin
            Return HomeAddress;
         End;
      Member Procedure setAddress(newAddress Address) is
         Begin
            HOmeAddress :=newAddress;
          End;       
       End;


<<集----------合----------部----------分>>

1. 創建帶有可變數組的表

 A. 
--格式: Create OR Replace type 基類型名 AS Object
        (字段清單)
--實例
   Create OR Replace Type MIngXiType AS Object
     (goodsID  varchar(15),
      InCount  int,
      ProviderID varchar(10)
      );
 B. 建立嵌套項類型的可變數組
  格式:Create OR Replace 可變數組名樂 AS Varray(最大行數) of 可變數組基類型
  實例:Create OR Replace Type arrMingXiType AS Varray(100) of MIngXiType;
 C. 創建一個主表
   Create Table InStockOrder
   (
    OrderId Varchar(15) not null Primary key,
    InDate  Date,
    OperatorID varchar(10),
    MingXI     arrMingXitype
    );           
2. 操作可變數組

 A.插入數據
 Insert into InStockOrder values(
 '200705050001',to_date('2007-05-05','YYYY-MM-DD'),'0002',
  arrMingXiType(MingXiType('S001',100,'10009'),
                MingXiType('T002',400,'10003')
                )
   );

 B. 查詢數據
 使用普通的select不能顯示varray中的數據.要使用帶有游標的PL/Sql塊來
 操作varray.
 可使用table()函數來查詢集合列
 select * from table(select t.mingxi from instockorder t
                         where t.orderid='200705050001')
 select goodsid from table(select t.mingxi from instockorder t
                         where t.orderid='200705050001')                        

 C. 修改數據
 實例:把編號為200705050001的入庫單號為S001的入庫改為200
  Update InStockorder
  Set MingXi=arrMingXitype(mingXiType('S001',200,'10009'),
                           mingXiType('T001',400,'10003'))                          
          where orderid='200705050001';
  注意﹕不能使用varray的單個元素﹐必須更新整個varray.


 D. 刪除數據與實通刪除一樣。
 


<<嵌----------套----------表----------部----------分>>       

嵌套表
   表中之表
   與可變數數組類似﹐不同這處是嵌套表對數據項數目沒有限制
   一個表表示為另一個表中的列。
使用嵌套表
   使用as object創建類型。
   然后使用as table of根據已經建立的類型新建一個類型
   創建表并指定嵌套表的存儲規范

1. 創建嵌套表
 A. 創建嵌套表的基類型
   格式:Create OR Replace Type 嵌套表的基類型名 AS  Object
    實例:
         Create OR Replace Type MingXiType AS Object
         (
          GoodsID  varchar(15), --貨物編號
          InCount  int         ,--入庫數量
          ProviderID varchar(10)
          ) not final;
 B. 創建嵌套表類型
   格式:Create Type 嵌套表類型名 AS Table of 類型名;
   實例:create or replace type nestMingxiType AS Table of mingxiType;
 C. 創建主表﹐其中的一個列是嵌套表類型的
   格式: create table table_name
        (字段1 類型及長度....字段n 類型及長度,
         嵌套類型字段 嵌套表類型名
         )
         Nested Table 嵌套類型字段名Store as 真正存儲的嵌套表;
   實例:(創建入庫表)
    Create table InstockTable
    (
      orderid varchar(13) not null primary key,
      indate date,
      operator varchar(12),
      mingxi nestmingxitype
      )nested table mingxi store as mingxitable;

2. 操作嵌套表
 A. 向嵌套表中插入記錄﹐
    格式: insert into 主表名(字段列表)]
    values(主表字段1的值....主表字段n的值,
           嵌套表類型名(嵌套表類型的基類型名(屬性1的值,....屬性n的值)
                        )
            );
   實例﹕                      
   insert into InstockTable values(
    '200705050001',to_date('2007-05-05','YYYY-MM-DD'),'0001',
    nestmingxitype(mingxitype('J0001',200,'1001'),
                   mingxitype('S0001',1000,'1002'),
                   mingxitype('T0005',500,'1003')
                   ));
    insert into InstockTable values(
    '200705050002',to_date('2007-05-05','YYYY-MM-DD'),'0001',
    nestmingxitype(mingxitype('J0001',200,'1001'),
                   mingxitype('S0001',1000,'1002'),
                   mingxitype('T0005',500,'1003')
                   ));                  
   --給訂單編號為200705050001新增一筆
     insert into table(select mingxi from instocktable where orderid='200705050001')
     values('H0004',200,'1004');

                   
 B. 更新嵌套表中的數據
    --正常1:
    update table(select t.mingxi from instocktable t where orderid='200705050001') mx
    set mx.incount=500
      where mx.goodsid='J0001';
    --正常2:
    update table(select t.mingxi from instocktable t where orderid='200705050001') mx
    set mx.incount=500
      where mx.goodsid='J0010';     

    --異常1﹕     
     update table(select t.mingxi from instocktable t where orderid='200705050003') mx
    set mx.incount=500
      where mx.goodsid='J0001';
     ORA-22908: reference to NULL table value :因為沒有訂單編號為200705050003的資料.
     --異常2﹕     
     update table(select t.mingxi from instocktable t where orderid like '20070505000%') mx
      set mx.incount=500
      where mx.goodsid='J0001';
      ORA-01427: single-row subquery returns more than one row :返回超過1行.
 C. 刪除嵌套表中的數據
     delete from table(
      select t.mingxi from instocktable t where orderid='200705050001') t
      where t.goodsid='J0001';
 D. 查詢
 
  select * from table(select p.mingxi from instocktable p where orderid='200705050001');
 
  SELECT D.ORDERID,MINGXI.* FROM INSTOCKTABLE D,TABLE(D.mingxi) MINGXI;

  select name from col$ where obj#=(select object_id from dba_objects where owner='SCOTT' AND OBJECT_NAME='MINGXITABLE');
  返回﹕GOODSID﹑INCOUNT﹑NESTED_TABLE_ID﹑PROVIDERID﹑SYS_NC_ROWINFO$
  select name from col$ where obj#=(select object_id from dba_objects where owner='SCOTT' AND OBJECT_NAME='INSTOCKTABLE');
 
  select /*+NESTED_TABLE_GET_REFS+*/NESTED_TABLE_ID,SYS_NC_ROWINFO$ FROM MINGXITABLE
 
  select /*+NESTED_TABLE_GET_REFS+*/ goodsid,incount,PROVIDERID from mingxitable; ----這個不推荐使用。
 


<<對----------象----------表----------部----------分>>        
 說明1﹕
   1: 一種特殊類型的表﹐表中的每一行都代表一個對象
   2:行對象
      在對象表中擁有整個行的對象
   3:列對象
    在較大行中擁有某些表列的對象﹐或是其它對象的屬性   
 說明2﹕
   1: 對象表中的每一行都是一個行對象
   2:包含對象標識符(OID)
   3:REF操作符用于引用行對象
   4:deref操作符返回行對象的值
  
1. ref函數的使用
--創建科室類型
  create or replace type officetype as object
  (
    id varchar(10),
    typename varchar(10)
    );
--創建對應對象表----科室表
  create table office of officetype;
--向對象表中插入記錄
  insert into office values('0001','財務科');
  insert into office values('0002','人事科');        
  insert into office values('0003','伙食科');
  insert into office values('0004','手勤科');
--使用ref函數查看行對象oid值以及表中科室編號﹑名稱
  格式﹕select ref(表別名) 對象表 對象表別名﹔
  實列﹕select ref(f),id,typename from office f;
    查出偽列值﹑編號﹑類型名
 2. ref 類型
   ref類型的使用: 通過ref和deref運算符,可以將oid用于創建外鍵關系。
 --創建具有外鍵列的關系表----人事表
   create table worker
   (workerid varchar(10) primary key,
    workername varchar(10),
    workeroffice ref officetype scope is office,--引用officetype外鍵﹐關聯的是oid值
    phone varchar(16)
    );
  --向表中插入數據﹐此表將從上面創建的尋象表中引用數據
   insert into worker select 'C001','張小時',ref(O),'010-1234567' from
   office O where id='0001'; 
 3. 使用deref查看oid指向的行中的數據
    格式﹕select deref(表別名.引用類型列名) from 表名 表的別名
    實例﹕
        select workerid,workername,deref(w.workeroffice),phone from worker w where workerid='C001';
 4. value函數﹕
  --使用value()返回表中的對象
    select value(O) from office O;
 5. 對象視圖
    create view officeview of officetype with object oid(id)
     as select * from office;
 6. 創建對象視圖
   A. 創建基于關系表父表的對象類型
    create or replace type depttype as object
    (
      deptno number(2),
      dname  varchar2(14),
      loc    varchar2(13)
      );        
      --創建基于關系表的對象視圖
      create view deptview of depttype with object oid(deptno) as select * from dept;
   B. 創建引用視圖(類似于關系創建一個從表)
     create view emp_view as select make_ref(deptview,deptno) deptoid,empno,ename from emp;
  ---對象視圖實際上是將關系包裝成對象表﹐通過構造關系表每條記錄的oid來實現類似于關系表的主外鍵約束
 
 
 



<<----------補----------充----------部----------分 1>>      
#以下部分來自網頁http://oracle.chinaitlab.com/exploiture/350520.html
#以補充其部分不完整。
對象類型：
優點：
1）  更容易與Java, C++編寫的物件應用程式交互
2）  獲取便捷。一次物件類型請求就可以從多個關係表中獲取資訊，通過一次網路往復即可返回
語法：
CREATE [OR REPLACE] TYPE type_name
{{AS| IS } OBJECT | UNDER super_type}
{
       attribute_name datatype[,attribute_name datatype]… ---成員變數
       [{MAP | ORDER} MEMBER function_name,]   ---排序函數
       [{FINAL | NOT FINAL} MEMBER function_name,]  ---可否繼承的成員函數
       [{INSTANTIABLE | NOT INSTANTIABLE } MEMBER function_name,]       ---可否實例化的成員函數
       [{MEMBER | STATIC } function_name,]     ---靜態、非靜態成員函數
}
[{FINAL | NOT FINAL}]     ---物件可否繼承
[{INSTANTIABLE | NOT INSTANTIABLE }]    ---物件可否實例化
/
物件類型的主體部分（即函數的實現部分，可選的）：
CREATE [OR REPLACE]
TYPE BODY type_name {AS| IS }                   
       [{MAP | ORDER} MEMBER function_body,]   ---排序函數
       [{MEMBER | STATIC } function_name,]     ---靜態、非靜態成員函數
END;
/

例如：
create or replace
type person as object(
       first_name varchar2(100),
       last_name varchar2(100))
/

屬性類型可以是任何oracle 資料類型（包括自定義），除了如下：
LONG和LONG RAW
NCHAR、NCLOB 、NVARCHAR2
ROWID、UROWID
PL/SQL的特定類型：%TYPE  %ROWTYPE

查看：
Desc person
 
構造函數：
set serveroutput on

declare
       l_person person
begin
       l_person := person('Donny','Chen');
       dbms_output.putline(l_person.first_name);
end;
/
構造函數要接受物件類型的所有屬性作為參數。因為這些參數沒有預設值，即使是null，也要提供。
舉例：
表中的物件類型：

物件類型可以作為資料庫中的列，所以稱為列物件
create table person_table
(
       name person,
       age number)
/
set desc depth all
desc person_table
set desc depth 1

插入資料：

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;
/

查詢資料：

select * from person_table
訪問物件類型的各個屬性：
select p.name.first_name
       from person_table p
/
為避免名稱解析問題，要求查詢物件類型的屬性的時候，使用表別名。否則報錯，舉例：
 
物件中的物件(合成)：

create or replace
type employee as object(
       name person,
       empno number,
       hiredate date)
/

修改和刪除對象：

9i之前，當建立的物件類型，以及依賴於此類型的物件或表之後，就無法再修改此物件類型了（增加刪除屬性和成員函數）。唯一的辦法是撤銷所有以來，即刪除依賴於此類型的物件或表。

9i新特性，可以修改被以來的物件類型，成為類型演化。有兩種方法：
INVALIDATE 和 CASCADE

INVALIDATE比如：
desc person_table
改變person類型，增加新屬性ssn
alter type person
       add attribute ssn varchar2(11) INVALIDATE;
 
desc person   (bug可能需要新開一個session)

INVALIDATE選項使的所有依賴於person類型的物件和表標記為INVALID,比如：
Desc person_table

需要手工驗證person_table:
alter table person_table upgrade including data;
desc person_table
upgrade including data表示根據新類型，物理上更新現有的資料的結構，ssn 置為null。
也可以upgrade not including data，不更新原有資料的結構。Dml訪問person實例資料的時候再更新。
Select * from person_table

CASCADE比如：
alter type person
add attribute dob date
cascade not including table data
/
不用手工驗證依賴此物件類型的表，由資料庫自動驗證。
Desc person
Desc person_table
因為not including table data，沒有更新原有資料：
select * from person_table
刪除類型：

force

方法：

即物件中的過程和函數,3種類型：
STATIC: 只能夠在物件類型上調用，不專屬於某個實例。
MEMBER: 專屬於某個特定的實例
CONSTRUCTOR: 構造函數

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

在類型主體實現這兩個方法：
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;
/

比較抽象資料類型的資料：

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;
/
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
物件默認FINAL，表示不可以被繼承；
MEMBER方法也能指定是否FINAL，表示能否在子類中對他進行覆寫。默認NOT FINAL

Create or replace type super_type as object(
       N number,
       Final member procedure cannot_override
)
not final
/