ORACLE 对象类型

优点：

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;

              elsif self.empno< p_employee.empno then

                    return -1;

              else

                    return 0;

              end if;

       end;

end;

/

继承：

FINAL / NOT FINAL

对象默认FINAL，表示不可以被继承；

MEMBER方法也能指定是否FINAL，表示能否在子类中对他进行覆写。默认NOT FINAL

--对象的继承,注意：UNDER

DROP TYPE HUMAN

CREATE TYPE HUMAN AS OBJECT(

    NAME VARCHAR2(20),

    SEX VARCHAR2(1), -- M :MALE   F:FEMALE

    BIRTHDAY DATE

) NOT FINAL;

CREATE TYPE EMPLOYEE_TYPE UNDER HUMAN(

    EMP_NO VARCHAR2(20),

    DEPT_NO VARCHAR2(20),

    JOB_TITLE VARCHAR2(60)
)

----------------------------------------------------------
--下面体现 NOT FINAL 的作用：　

DROP TYPE HUMAN

DROP TYPE EMPLOYEE_TYPE

CREATE TYPE HUMAN AS OBJECT(

    NAME VARCHAR2(20),

    SEX VARCHAR2(1),

    BIRTHDAY DATE
)

CREATE TYPE EMPLOYEE_TYPE UNDER HUMAN(

    EMP_NO VARCHAR2(20),

    DEPT_NO VARCHAR2(20),

    JOB_TITLE VARCHAR2(60)
)

/*　
对象类型默认为 FINAL ,即不能派生子类．

由于HUMAN 没有加：NOT FINAL ，所以，它不能作为另一个类型的超类，

在创建EMPLOYEE_TYPE 的时候，产生一个错误：PLS-00590: 正在尝试创建一个最终类型的子类型
*/

----------------------------------------------------------
-- 对象类型的方法也是可以继承的．

DROP TYPE EMPLOYEE_TYPE

DROP TYPE HUMAN

CREATE OR REPLACE TYPE HUMAN AS OBJECT(

    NAME VARCHAR2(20),

    SEX VARCHAR2(1),

    BIRTHDAY DATE,
   
    MEMBER FUNCTION GET_AGE RETURN NUMBER

) NOT FINAL;

CREATE OR REPLACE TYPE BODY HUMAN AS

    MEMBER FUNCTION GET_AGE RETURN NUMBER AS

        V_NUM NUMBER;

    BEGIN

      SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,TO_DATE('20060601','YYYYMMDD'))) INTO V_NUM FROM DUAL;

   RETURN V_NUM;

    END;

END;

SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('20060601','YYYYMMDD')) FROM DUAL;

CREATE OR REPLACE TYPE EMPLOYEE_TYPE UNDER HUMAN(

    EMP_NO VARCHAR2(20),

    DEPT_NO VARCHAR2(20)
)

--存储过程　

SET SERVEROUT ON

DECLARE

    EMP1 EMPLOYEE_TYPE := EMPLOYEE_TYPE('xling','M',TO_DATE

('20060601','YYYYMMDD'),'2060006','DPT0201');

BEGIN

  DBMS_OUTPUT.PUT_LINE(EMP1.GET_AGE());

END;

--表

DROP TABLE EMPLOYEE

CREATE TABLE EMPLOYEE(

    GUID NUMBER NOT NULL,

    EMP EMPLOYEE_TYPE

)

CREATE OR REPLACE TRIGGER EMPLOYEE_T_I1

BEFORE INSERT ON EMPLOYEE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

BEGIN

  :NEW.GUID := XLING_PKG_TOOLS.F_GET_NEXTVAL('EMPLOYEE');

END;

CREATE OR REPLACE TRIGGER EMPLOYEE_T_D1

BEFORE DELETE ON EMPLOYEE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW

BEGIN

  XLING_PKG_TOOLS.P_DELETE_GUID(:OLD.GUID);

END;

INSERT INTO EMPLOYEE (EMP) VALUES (EMPLOYEE_TYPE('xling','M',TO_DATE

('20060601','YYYYMMDD'),'2060006','DPT0201'));

INSERT INTO EMPLOYEE (EMP) VALUES (EMPLOYEE_TYPE('snow','F',TO_DATE

('20000601','YYYYMMDD'),'2060001','DPT0301'));

INSERT INTO EMPLOYEE (EMP) VALUES (EMPLOYEE_TYPE('werewi','M',TO_DATE

('20050601','YYYYMMDD'),'2060007','DPT0401'));

--提示错误：ORA-22905: 无法从非嵌套表项访问行
-- TABLE 子句只能用在嵌套表？意思上有点像

SELECT

    GUID,I.NAME,I.SEX,I.BIRTHDAY,I.GET_AGE(),I.DEPT_NO,I.EMP_NO

FROM

  EMPLOYEE E,

  TABLE(E.EMP)

--

SELECT GUID,

    E.EMP.NAME,E.EMP.SEX,E.EMP.BIRTHDAY,E.EMP.GET_AGE(),E.EMP.EMP_NO,E.EMP.DEPT_NO
FROM

EMPLOYEE E

----------------------------------------------------------------------------------------
--抽象类型，注意：NOT FINAL NOT INSTANTIABLE

DROP TABLE EMPLOYEE

DROP TYPE EMPLOYEE_TYPE

CREATE OR REPLACE TYPE HUMAN AS OBJECT(

    NAME VARCHAR2(20),

    SEX VARCHAR2(1),

    BIRTHDAY DATE,
   
    MEMBER FUNCTION GET_AGE RETURN NUMBER

) NOT FINAL NOT INSTANTIABLE

CREATE OR REPLACE TYPE BODY HUMAN AS

    MEMBER FUNCTION GET_AGE RETURN NUMBER AS

        V_NUM NUMBER;

    BEGIN

      SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,TO_DATE('20060601','YYYYMMDD'))) INTO V_NUM FROM DUAL;

   RETURN V_NUM;

    END;

END;

/*
现在试图声明一个抽象类的实例，产生如下错误．
PLS-00713: 正在试图实例化一种 NOT INSTANTIABLE 的类型
*/

DECLARE

    AMAN HUMAN := HUMAN('xling','M',TO_DATE('20060601','YYYYMMMDD'));

BEGIN

  DBMS_OUTPUT.PUT_LINE(AMAN.GET_AGE());

END;

-------------------------------------------------------
--抽象方法：拥有抽象方法的类型必须为抽象类型，抽象方法不能有主体

/*
这样声明是不对的，因为具有抽象方法的对象类型必须是抽象类型
*/

CREATE OR REPLACE TYPE HUMAN AS OBJECT(

    NAME VARCHAR2(20),

    SEX VARCHAR2(1),

    BIRTHDAY DATE,
   
    NOT INSTANTIABLE MEMBER FUNCTION GET_AGE RETURN NUMBER

) NOT FINAL NOT INSTANTIABLE
--

DROP TYPE HUMAN

CREATE OR REPLACE TYPE HUMAN AS OBJECT(

    NAME VARCHAR2(20),

    SEX VARCHAR2(1),

    BIRTHDAY DATE,
   
    NOT INSTANTIABLE MEMBER FUNCTION GET_AGE RETURN NUMBER

) NOT FINAL NOT INSTANTIABLE

/*
抽象类型也不能有主体，如下会提示：
PLS-00632: NOT INSTANTIABLE 方法不能具有主体
*/

CREATE OR REPLACE TYPE BODY HUMAN AS

    NOT INSTANTIABLE MEMBER FUNCTION GET_AGE RETURN NUMBER AS

    BEGIN

      NULL;

    END;

END;

DROP TYPE BODY HUMAN

/*
只能为非 NOT INSTANTIABLE 的方法创建方法体
*/

DROP TYPE HUMAN

CREATE OR REPLACE TYPE HUMAN AS OBJECT(

    NAME VARCHAR2(20),

    SEX VARCHAR2(1),

    BIRTHDAY DATE,
   
    NOT INSTANTIABLE MEMBER FUNCTION GET_AGE RETURN NUMBER,

    MEMBER FUNCTION TT RETURN NUMBER

) NOT FINAL NOT INSTANTIABLE

CREATE OR REPLACE TYPE BODY HUMAN AS

    MEMBER FUNCTION TT RETURN NUMBER AS

        V_NUM NUMBER;

    BEGIN

      SELECT MONTHS_BETWEEN(SYSDATE,BIRTHDAY) INTO V_NUM FROM DUAL;

   RETURN V_NUM;

    END;

END;

/*
在子类里实现超类的抽象方法，注意：OVERRIDING
*/

CREATE OR REPLACE TYPE EMPLOYEE_TYPE UNDER HUMAN(

    EMP_NO VARCHAR2(20),

    DEPT_NO VARCHAR2(20),
   
    OVERRIDING MEMBER FUNCTION GET_AGE RETURN NUMBER

)

CREATE OR REPLACE TYPE BODY EMPLOYEE_TYPE AS

    OVERRIDING MEMBER FUNCTION GET_AGE RETURN NUMBER AS

      V_NUM NUMBER;

    BEGIN

      SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,BIRTHDAY)/12) INTO V_NUM FROM DUAL;

   RETURN V_NUM;

    END;
END;

SET SERVEROUT ON

DECLARE

    EMP1 EMPLOYEE_TYPE:= EMPLOYEE_TYPE('xling','M',TO_DATE

('19840601','YYYYMMDD'),'2060006','DPT0201');

BEGIN

  DBMS_OUTPUT.PUT_LINE(EMP1.GET_AGE());

END;

/*
覆盖并不限于 NOT INSTANTIABLE 方法，子类可以覆盖所有超类的方法，除非在超类的方法中用 FINAL 限制　
*/

DROP TYPE EMPLOYEE_TYPE

DROP TYPE HUMAN

CREATE OR REPLACE TYPE HUMAN AS OBJECT(

    NAME VARCHAR2(20),

    SEX VARCHAR2(1),

    BIRTHDAY DATE,

    FINAL MEMBER FUNCTION GET_AGE RETURN NUMBER

) NOT FINAL

CREATE OR REPLACE TYPE BODY HUMAN AS

    FINAL MEMBER FUNCTION GET_AGE RETURN NUMBER AS

       V_NUM NUMBER;

    BEGIN

      SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,BIRTHDAY)/12) INTO V_NUM FROM DUAL;

   RETURN V_NUM;

    END;

END;

/*
这里试图覆盖掉超类里的 FINAL 方法,
提示错误：PLS-00637:无法覆盖FINAL 方法
*/

CREATE OR REPLACE TYPE EMPLOYEE_TYPE UNDER HUMAN(

    EMP_NO VARCHAR2(20),

    DEPT_NO VARCHAR2(20),
   
    OVERRIDING MEMBER FUNCTION GET_AGE RETURN NUMBER
)

---------------------------------------------------------------------------
--方法的重载

DROP TYPE EMPLOYEE_TYPE

DROP TYPE HUMAN

CREATE OR REPLACE TYPE HUMAN AS OBJECT(

    NAME VARCHAR2(20),

    SEX VARCHAR2(1),

    BIRTHDAY DATE,
   
    MEMBER FUNCTION GET_AGE RETURN NUMBER,

    MEMBER FUNCTION GET_AGE(I_END_DATE IN DATE) RETURN NUMBER

)

CREATE OR REPLACE TYPE BODY HUMAN AS
   
    MEMBER FUNCTION GET_AGE RETURN NUMBER AS

        V_NUM NUMBER;

    BEGIN

      SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,BIRTHDAY)/12) INTO V_NUM FROM DUAL;

   RETURN V_NUM;

    END;
   
    MEMBER FUNCTION GET_AGE(I_END_DATE IN DATE) RETURN NUMBER AS

        V_NUM NUMBER;

    BEGIN

      SELECT FLOOR(MONTHS_BETWEEN(I_END_DATE,BIRTHDAY)/12) INTO V_NUM FROM DUAL;

   RETURN V_NUM;

    END;

END;

SET SERVEROUT ON

DECLARE

    V_HUMAN HUMAN := HUMAN('xling','M',TO_DATE('19830714','YYYYMMDD'));

BEGIN

  DBMS_OUTPUT.PUT_LINE('Method 1:' || V_HUMAN.GET_AGE());

  DBMS_OUTPUT.PUT_LINE('Method 2:' || V_HUMAN.GET_AGE(TO_DATE('20000101','YYYYMMDD')));

END;

--------------------------------------------------------------------------------------------
--将FINAL 对象对型改为可继承,CASCADE 选项指定应该如何处理从属对象及其数据，
ALTER TYPE HUMAN NOT FINAL CASCADE

CREATE OR REPLACE TYPE EMPLOYEE_TYPE UNDER HUMAN(

    EMP_NO VARCHAR2(20),

    DEPT_NO VARCHAR2(20)

)

SET SERVEROUT ON

DECLARE

    V_EMP EMPLOYEE_TYPE := EMPLOYEE_TYPE('xling','M',TO_DATE

('18900714','YYYYMMDD'),'2060006','DPT0201');

BEGIN

  DBMS_OUTPUT.PUT_LINE('Method 1:' || V_EMP.GET_AGE());

  DBMS_OUTPUT.PUT_LINE('Method 2:' || V_EMP.GET_AGE(TO_DATE('20000101','YYYYMMDD')));

END;