oracle 11g PL/SQL Programming学习十五-优快云博客

本文介绍了PL/SQL中的对象类型定义、作用域、声明、实现等基础知识，并详细讲解了对象类型的构造函数、成员函数、静态函数以及MAP和ORDER函数的使用方法。此外，还探讨了对象类型的继承与多态特性，以及如何实现对象类型的集合。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

----------------------------------------------------------------------------
-----------------PL/SQL学习笔记系列 By Cryking-----------------
------------------------转载请注明出处,谢谢!------------------------

第14章对象类型(Object Types)
面向对象(Object-oriented)编程方案解决了函数、存储、包的一些缺点.
对象类型定义了如何存储数据以及API操作,像成员函数或存储.
操作在面向对象的编程语言中一般描述为方法,但在PL/SQL中实现为类成员函数或存储.
对象类型和对象在很多面向对象的程序语言中也被称为类.
对象类型的定义类似PL/SQL的包头和包体,对象类型体实现了对象类型定义的内容,包体实现了包头的内容.
可以像SQL数据类型一样创建对象类型及对象类型体.
你可以在以下4种情况中使用对象类型:
1.可以用做表列的数据类型
2.可以作为对象属性的数据类型
3.可以作为存储或函数的参数的数据类型
4.作为函数的返回类型

oracle 11g限定对象要么是永久对象,要么是临时对象.
永久对象又分单独对象和嵌入式对象.
单独对象存储在数据库的表中,有一个唯一的对象标识符.
嵌入式对象不存储在数据库表中,它是嵌入在另外的oracle结构中,像嵌入到另一个对象类型中.
嵌入式对象没有对象标识符.
临时对象是对象的实例,它不存储在数据库中.
临时对象的生命周期受PL/SQL块限制.
这章我们主要学习的是临时对象.

对象的基本内容
1.对象类型命名
必须以字母开头,只能由字母、数字、下划线组成.
对象类型名和其他所有对象共享同一个命名空间,除了数据库触发器对象.
2.对象类型的作用域范围
对象类型的作用域范围和其他函数或存储一样.它们都受到模式(schema)的限制.
当你要在另一个模式中使用对象的时候,你必须要给这个模式授予执行该对象的权限.

对象的声明
PL/SQL对象类型,和包一样,有一个定义原型.
不同的是对象类型有一组专用的函数:CONSTRUCTOR, MAP,ORDER.
构造(CONSTRUCTOR)函数决定了你如何构建对象类型的实例.它返回一个对象类型的实例,在PL/SQL中就是SELF.
你不能像包变量那样声明对象类型变量.
你需要按以下顺序列出对象类型的各个元素:属性、构造函数、函数、存储、MAP或ORDER函数.
对象类型原型:
CREATE [OR REPLACE] OBJECT TYPE object_name
[AUTHID {DEFINER | CURRENT_USER}] IS OBJECT
( [instance_variables {sql_datatype | plsql_datatype}]
, [CONSTRUCTOR FUNCTION constructor_name
[( parameter_list )] RETURN RESULT AS SELF
, [{MEMBER | STATIC} FUNCTION function_name
[( parameter_list )] RETURN { sql_data_type | plsql_data_type }
, [{MEMBER | STATIC} PROCEDURE procedure_name
[( parameter_list)]
,{[MAP FUNCTION map_name RETURN { CHAR | DATE | NUMBER | VARCHAR2 } |
[ORDER FUNCTION order_name RETURN { sql_data_type | plsql_data_type }}])
[NOT] INSTANTIABLE [NOT] FINAL;

如：

23:15:27 SCOTT@orcl> create or replace type hello_there is object
23:18:50   2  (
23:18:50   3    who varchar2(20),
23:18:50   4    constructor function hello_there RETURN SELF AS RESULT,
23:18:50   5    CONSTRUCTOR FUNCTION hello_there(who varchar2) return self as result,
23:18:50   6    member procedure to_string
23:18:50   7  )
23:18:50   8  instantiable not final;
23:18:53   9  /


Type created.


Elapsed: 00:00:00.36
23:18:56 SCOTT@orcl> desc hello_there
 hello_there is NOT FINAL
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------
 WHO                                                            VARCHAR2(20)


METHOD
------
 FINAL CONSTRUCTOR FUNCTION HELLO_THERE RETURNS SELF AS RESULT


METHOD
------
 FINAL CONSTRUCTOR FUNCTION HELLO_THERE RETURNS SELF AS RESULT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 WHO                            VARCHAR2                IN
 MEMBER PROCEDURE TO_STRING

对象类型体的实现
和包体一样,对象体的实现必须要和对象中声明的一致.
对象类型体创建原型:
CREATE [OR REPLACE] OBJECT TYPE object_name
[AUTHID {DEFINER | CURRENT_USER}] IS
( [CONSTRUCTOR FUNCTION constructor_name
[( parameter_list )] RETURN RESULT AS SELF IS
BEGIN
execution_statements;
END [constructor_name];
[{MEMBER | STATIC} FUNCTION function_name
[( parameter_list )] RETURN { sql_data_type | plsql_data_type } IS
BEGIN
execution_statements;
END [function_name];
[{MEMBER | STATIC} PROCEDURE procedure_name IS
[( parameter_list)]
BEGIN
execution_statements;
END [procedure_name];
{[MAP FUNCTION map_name RETURN { CHAR | DATE | NUMBER | VARCHAR2 } IS
BEGIN
execution_statements;
END [procedure_name]; |
[ORDER FUNCTION order_name RETURN { sql_data_type | plsql_data_type } IS
BEGIN
execution_statements;
END [procedure_name];}])
END [object_name];
如:

23:31:04 SCOTT@orcl> create or replace type body hello_there is
23:34:39   2    constructor function hello_there RETURN SELF AS RESULT is
23:34:39   3      hello hello_there := hello_there('默认构造对象');
23:34:39   4    begin
23:34:39   5      self := hello;
23:34:39   6      return;
23:34:39   7    end hello_there;
23:34:39   8    CONSTRUCTOR FUNCTION hello_there(who varchar2) return self as result is
23:34:39   9    begin
23:34:39  10      self.who := who;
23:34:39  11      return;
23:34:39  12    end hello_there;
23:34:39  13    member procedure to_string is
23:34:39  14    begin
23:34:39  15      dbms_output.put_line('Hello' || self.who || '.');
23:34:39  16    end to_string;
23:34:39  17  end;
23:34:40  18  /


Type body created.


Elapsed: 00:00:00.05

对象类型一般会提供一个缺省的构造函数.缺省的构造函数通常没有参数.

--使用缺省构造函数的对象类型变量
23:34:42 SCOTT@orcl> declare
23:37:22   2    hello hello_there:=hello_there;
23:37:43   3  BEGIN
23:37:45   4    hello.to_string();
23:37:58   5  END;
23:38:01   6  /
Hello默认构造对象.


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.06


--给定参数的构造函数的对象类型变量
23:38:03 SCOTT@orcl> declare
23:39:30   2    hello hello_there:=hello_there('自定义对象');
23:40:07   3  BEGIN
23:40:14   4    hello.to_string();
23:40:15   5  END;
23:40:16   6  /
Hello自定义对象.


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.01

对象类型属性的静态成员方法
静态方法能创建对象类型的实例.

--声明一个含静态方法的对象类型
23:40:17 SCOTT@orcl> create or replace type item_object is object
23:47:26   2  (
23:47:26   3    item_title varchar2(60),
23:47:26   4    item_subtitle varchar2(60),
23:47:26   5    constructor function item_object RETURN SELF AS RESULT,
23:47:26   6    CONSTRUCTOR FUNCTION item_object(item_title varchar2,item_subtitle varchar2) return self as result,
23:47:27   7    static function get_item_object(item_id number) return item_object,
23:47:27   8    member function to_string return varchar2
23:47:27   9  )
23:47:27  10  instantiable not final;
23:47:27  11  /


Type created.


Elapsed: 00:00:00.18

注意静态方法的实现需要创建一个对象类型实例.

--item_object实现
23:56:53 SCOTT@orcl> create or replace type body  item_object is
23:58:50   2    constructor function item_object RETURN SELF AS RESULT is
23:58:50   3      item item_object := item_object('默认主标题','默认副标题');
23:58:50   4    begin
23:58:50   5      self := item;
23:58:50   6      return;
23:58:50   7    end item_object;
23:58:50   8    CONSTRUCTOR FUNCTION item_object(item_title varchar2,item_subtitle varchar2) return self as result is
23:58:50   9    begin
23:58:50  10      self.item_title := item_title;
23:58:50  11      self.item_subtitle:=item_subtitle;
23:58:50  12      return;
23:58:50  13    end item_object;
23:58:50  14    STATIC FUNCTION get_item_object(item_id number) return item_object is
23:58:50  15    item item_object;
23:58:50  16    cursor c(item_id_id number) is
23:58:50  17    select '主&'||ename item_title,'副&'||sal item_subtitle from emp where empno=item_id_id;
23:58:50  18    begin
23:58:50  19    for i  in c(item_id) loop
23:58:50  20      item:=item_object(i.item_title,i.item_subtitle);
23:58:50  21      end loop;
23:58:50  22      return item;
23:58:50  23    end get_item_object;
23:58:50  24    member function  to_string return varchar2 is
23:58:50  25    begin
23:58:50  26      return '['||self.item_title||']['||self.item_subtitle||']';
23:58:50  27    end to_string;
23:58:50  28  end;
23:58:50  29  /


Type body created.


Elapsed: 00:00:00.06

静态函数get_item_object使用参数和本地游标从表中寻找到满足条件的行来实例化对象类型.
它返回本地实例化变量.

--对象类型中的静态函数使用
23:58:51 SCOTT@orcl> begin
00:02:34   2    dbms_output.put_line(item_object.get_item_object(7788).to_string);
00:03:20   3  end;
00:03:24   4  /
[主&SCOTT][副&3000]


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.01

对象的比对
对象实例的比较在面向编程中是非常重要的.
oracle提供了两个预定义函数MAP和ORDER来完成对象的比较.
你在对象类型体中只能定义MAP和ORDER其中之一.
如果你要同时定义MAP和ORDER在一个对象类型中,你将获得错误PLS-00154.
MAP成员函数不用带参数,只能返回CHAR,DATE,NUMBER,VARCHAR2数据类型.
注:子类不能重写父类的MAP和ORDER函数.
ORDER成员函数就比较复杂,它能带任何SQL数据类型的参数.但它只返回NUMBER数据类型.

使用MAP成员函数比较对象
当对象类型是由单个属性值来实例化的时候,使用MAP函数会很好.

--使用MAP来实现简单的冒泡排序算法
00:03:25 SCOTT@orcl> create or replace type map_comp is object
00:14:44   2  (
00:14:44   3    who varchar2(60),
00:14:44   4    constructor function map_comp(who varchar2) RETURN SELF AS RESULT,
00:14:44   5    map member function equals return varchar2
00:14:44   6  )
00:14:44   7  instantiable not final;
00:14:46   8  /


Type created.


Elapsed: 00:00:00.12
00:14:47 SCOTT@orcl> create or replace type body map_comp is
00:17:25   2    constructor function map_comp(who varchar2) RETURN SELF AS RESULT is
00:17:25   3    begin
00:17:25   4      self.who := who;
00:17:25   5      return;
00:17:25   6    end map_comp;
00:17:25   7    map member function equals return varchar2 is
00:17:25   8    begin
00:17:25   9     return self.who;
00:17:25  10    end equals;
00:17:25  11  end;
00:17:27  12  /


Type body created.


Elapsed: 00:00:00.08
00:22:21 SCOTT@orcl> DECLARE
00:22:51   2    -- Declare a collection of an object type.
00:22:51   3    TYPE object_list IS TABLE OF MAP_COMP;
00:22:51   4    -- Initialize four objects in mixed alphabetical order.
00:22:51   5    object1 MAP_COMP := map_comp('3Ron Weasley');
00:22:51   6    object2 MAP_COMP := map_comp('2Harry Potter');
00:22:51   7    object3 MAP_COMP := map_comp('5Luna Lovegood');
00:22:51   8    object4 MAP_COMP := map_comp('1Hermione Granger');
00:22:51   9    -- Define a collection of the object type.
00:22:51  10    objects OBJECT_LIST := object_list(object1,object2,object3,object4);
00:22:51  11    -- Swaps A and B.
00:22:51  12    PROCEDURE swap(a IN OUT MAP_COMP,b IN OUT MAP_COMP) IS
00:22:51  13      c MAP_COMP;
00:22:51  14    BEGIN
00:22:51  15      c := b;
00:22:51  16      b := a;
00:22:51  17      a := c;
00:22:51  18    END swap;
00:22:51  19  BEGIN
00:22:51  20      -- Print reordered objects.
00:22:51  21    FOR i IN 1 .. objects.COUNT LOOP
00:22:51  22      dbms_output.put_line('排序前:'||objects(i).equals);
00:22:51  23    END LOOP;
00:22:51  24    dbms_output.put_line('------');
00:22:51  25    --冒泡排序.
00:22:51  26    FOR i IN 1 .. objects.COUNT LOOP
00:22:51  27      FOR j IN 1 .. objects.COUNT LOOP
00:22:51  28        IF objects(i).equals = LEAST(objects(i).equals, objects(j).equals) THEN
00:22:51  29          swap(objects(i), objects(j));
00:22:51  30        END IF;
00:22:51  31      END LOOP;
00:22:51  32    END LOOP;
00:22:51  33    -- Print reordered objects.
00:22:51  34    FOR i IN 1 .. objects.COUNT LOOP
00:22:51  35      dbms_output.put_line('排序后:'||objects(i).equals);
00:22:51  36    END LOOP;
00:22:51  37  END;
00:22:51  38  /
排序前:3Ron Weasley
排序前:2Harry Potter
排序前:5Luna Lovegood
排序前:1Hermione Granger
------
排序后:1Hermione Granger
排序后:2Harry Potter
排序后:3Ron Weasley
排序后:5Luna Lovegood


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.02

使用ORDER成员函数比较对象
ORDER成员函数允许你传递一个实例对象到另一个对象,来比较它们是否相等.
ORDER成员函数支持多个属性值的对象类型实例的比较.

--使用ORDER函数来实现简单的冒泡排序算法
00:33:52 SCOTT@orcl> create or replace type order_comp is object
00:34:06   2  (
00:34:06   3    who varchar2(60),
00:34:06   4    movie varchar2(60),
00:34:06   5    constructor function order_comp(who varchar2,movie varchar2) RETURN SELF AS RESULT,
00:34:06   6    member function to_string return varchar2,
00:34:06   7    order member function equals(object order_comp)return number
00:34:06   8  )
00:34:06   9  instantiable not final;
00:34:06  10  /


Type created.


Elapsed: 00:00:00.09
00:44:25 SCOTT@orcl> create or replace type body order_comp is
00:45:48   2    constructor function order_comp(who varchar2,movie varchar2) RETURN SELF AS RESULT is
00:45:48   3    begin
00:45:48   4      self.who := who;
00:45:48   5      self.movie := movie;
00:45:48   6      return;
00:45:48   7    end order_comp;
00:45:48   8    member function to_string return varchar2 is
00:45:48   9    begin
00:45:48  10      RETURN '['||self.movie||']['||self.who||']';
00:45:48  11    end to_string;
00:45:48  12    order member function equals(object order_comp) return number is
00:45:48  13   begin
00:45:48  14    -- The primary sort.
00:45:48  15    IF self.movie < object.movie THEN
00:45:48  16      RETURN 1;
00:45:48  17      -- The secondary sort.
00:45:48  18    ELSIF self.movie = object.movie AND self.who < object.who THEN
00:45:48  19      RETURN 1;
00:45:48  20    ELSE
00:45:48  21      RETURN 0;
00:45:48  22    END IF;
00:45:48  23   end equals;
00:45:48  24  end;
00:45:48  25  /


Type body created.


Elapsed: 00:00:00.07
00:45:50 SCOTT@orcl> DECLARE
00:49:34   2    -- Declare a collection of an object type.
00:49:34   3    TYPE object_list IS TABLE OF ORDER_COMP;
00:49:34   4    -- Initialize four objects in mixed alphabetical order.
00:49:34   5    object1 ORDER_COMP := order_comp('Ron Weasley', 'Harry Potter 1');
00:49:34   6    object2 ORDER_COMP := order_comp('Harry Potter', 'Harry Potter 1');
00:49:34   7    object3 ORDER_COMP := order_comp('Luna Lovegood', 'Harry Potter5');
00:49:34   8    object4 ORDER_COMP := order_comp('Hermione Granger', 'Harry Potter 1');
00:49:34   9    object5 ORDER_COMP := order_comp('Hermione Granger', 'Harry Potter 2');
00:49:34  10    object6 ORDER_COMP := order_comp('Harry Potter', 'Harry Potter 5');
00:49:34  11    object7 ORDER_COMP := order_comp('Cedric Diggory', 'Harry Potter 4');
00:49:34  12    object8 ORDER_COMP := order_comp('Severus Snape', 'Harry Potter1');
00:49:34  13    -- Define a collection of the object type.
00:49:34  14    objects OBJECT_LIST := object_list(object1,
00:49:34  15                                       object2,
00:49:34  16                                       object3,
00:49:34  17                                       object4,
00:49:34  18                                       object5,
00:49:34  19                                       object6,
00:49:34  20                                       object7,
00:49:34  21                                       object8);
00:49:34  22    -- Swaps A and B.
00:49:34  23    PROCEDURE swap(a IN OUT ORDER_COMP, b IN OUT ORDER_COMP) IS
00:49:34  24      c ORDER_COMP;
00:49:34  25    BEGIN
00:49:34  26      c := b;
00:49:34  27      b := a;
00:49:34  28      a := c;
00:49:34  29    END swap;
00:49:34  30  BEGIN
00:49:34  31    FOR i IN 1 .. objects.COUNT LOOP
00:49:34  32      dbms_output.put_line('排序前:' || objects(i).to_string);
00:49:34  33    END LOOP;
00:49:34  34    dbms_output.put_line('------');
00:49:34  35    --冒泡排序
00:49:34  36    FOR i IN 1 .. objects.COUNT LOOP
00:49:34  37      FOR j IN 1 .. objects.COUNT LOOP
00:49:34  38        IF objects(i).equals(objects(j)) = 1 THEN
00:49:34  39          -- 升序排列
00:49:34  40          swap(objects(i), objects(j));
00:49:34  41        END IF;
00:49:34  42      END LOOP;
00:49:34  43    END LOOP;
00:49:34  44    -- Print reordered objects.
00:49:34  45    FOR i IN 1 .. objects.COUNT LOOP
00:49:34  46      dbms_output.put_line('排序后:' || objects(i).to_string);
00:49:34  47    END LOOP;
00:49:34  48  END;
00:49:34  49  /
排序前:[Harry Potter 1][Ron Weasley]
排序前:[Harry Potter 1][Harry Potter]
排序前:[Harry Potter 5][Luna Lovegood]
排序前:[Harry Potter 1][Hermione Granger]
排序前:[Harry Potter 2][Hermione Granger]
排序前:[Harry Potter 5][Harry Potter]
排序前:[Harry Potter 4][Cedric Diggory]
排序前:[Harry Potter 1][Severus Snape]
------
排序后:[Harry Potter 1][Harry Potter]
排序后:[Harry Potter 1][Hermione Granger]
排序后:[Harry Potter 1][Ron Weasley]
排序后:[Harry Potter 1][Severus Snape]
排序后:[Harry Potter 2][Hermione Granger]
排序后:[Harry Potter 4][Cedric Diggory]
排序后:[Harry Potter 5][Harry Potter]
排序后:[Harry Potter 5][Luna Lovegood]


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.02
00:49:36 SCOTT@orcl> DECLARE
00:52:54   2    -- Declare a collection of an object type.
00:52:54   3    TYPE object_list IS TABLE OF ORDER_COMP;
00:52:54   4    -- Initialize four objects in mixed alphabetical order.
00:52:54   5    object1 ORDER_COMP := order_comp('Ron Weasley', 'Harry Potter 1');
00:52:54   6    object2 ORDER_COMP := order_comp('Harry Potter', 'Harry Potter 1');
00:52:54   7    object3 ORDER_COMP := order_comp('Luna Lovegood', 'Harry Potter5');
00:52:54   8    object4 ORDER_COMP := order_comp('Hermione Granger', 'Harry Potter 1');
00:52:54   9    object5 ORDER_COMP := order_comp('Hermione Granger', 'Harry Potter 2');
00:52:54  10    object6 ORDER_COMP := order_comp('Harry Potter', 'Harry Potter 5');
00:52:54  11    object7 ORDER_COMP := order_comp('Cedric Diggory', 'Harry Potter 4');
00:52:54  12    object8 ORDER_COMP := order_comp('Severus Snape', 'Harry Potter1');
00:52:54  13    -- Define a collection of the object type.
00:52:54  14    objects OBJECT_LIST := object_list(object1,
00:52:54  15                                       object2,
00:52:54  16                                       object3,
00:52:54  17                                       object4,
00:52:54  18                                       object5,
00:52:54  19                                       object6,
00:52:54  20                                       object7,
00:52:54  21                                       object8);
00:52:54  22    -- Swaps A and B.
00:52:54  23    PROCEDURE swap(a IN OUT ORDER_COMP, b IN OUT ORDER_COMP) IS
00:52:54  24      c ORDER_COMP;
00:52:54  25    BEGIN
00:52:54  26      c := b;
00:52:54  27      b := a;
00:52:54  28      a := c;
00:52:54  29    END swap;
00:52:54  30  BEGIN
00:52:54  31    FOR i IN 1 .. objects.COUNT LOOP
00:52:54  32      dbms_output.put_line('排序前:' || objects(i).to_string);
00:52:54  33    END LOOP;
00:52:54  34    dbms_output.put_line('------');
00:52:54  35    --冒泡排序
00:52:54  36    FOR i IN 1 .. objects.COUNT LOOP
00:52:54  37      FOR j IN 1 .. objects.COUNT LOOP
00:52:54  38        IF objects(i).equals(objects(j)) = 0 THEN
00:52:54  39          --降序排列
00:52:54  40          swap(objects(i), objects(j));
00:52:54  41        END IF;
00:52:54  42      END LOOP;
00:52:54  43    END LOOP;
00:52:54  44    -- Print reordered objects.
00:52:54  45    FOR i IN 1 .. objects.COUNT LOOP
00:52:54  46      dbms_output.put_line('排序后:' || objects(i).to_string);
00:52:54  47    END LOOP;
00:52:54  48  END;
00:52:54  49  /
排序前:[Harry Potter 1][Ron Weasley]
排序前:[Harry Potter 1][Harry Potter]
排序前:[Harry Potter 5][Luna Lovegood]
排序前:[Harry Potter 1][Hermione Granger]
排序前:[Harry Potter 2][Hermione Granger]
排序前:[Harry Potter 5][Harry Potter]
排序前:[Harry Potter 4][Cedric Diggory]
排序前:[Harry Potter 1][Severus Snape]
------
排序后:[Harry Potter 5][Luna Lovegood]
排序后:[Harry Potter 5][Harry Potter]
排序后:[Harry Potter 4][Cedric Diggory]
排序后:[Harry Potter 2][Hermione Granger]
排序后:[Harry Potter 1][Severus Snape]
排序后:[Harry Potter 1][Ron Weasley]
排序后:[Harry Potter 1][Hermione Granger]
排序后:[Harry Potter 1][Harry Potter]


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.04

继承与多态(Inheritance and Polymorphism)
对象是可扩展的,因为你可以通过建立子类来增加它的属性.
子类继承父类的行为,子类也能通过创建父类同名的成员方法来重写父类的行为.
多态就是多个子类继承父类行为的过程.
一个经典的案例就是广义的车辆类.
你可以通过建造汽车、摩托车、卡车、行李车等来开发指定的车类.
这些子类扩展了车辆类的一般属性和方法,甚至有时重写了部分方法.
这些方法使得子类之间有所区别,如开汽车方法和骑摩托车方法.
当车辆类成为子类的时候,交通工具类就成为父类了.
继承又分为单继承和多继承.像JAVA就支持单继承,而C++就支持多继承.
PL/SQL只支持单继承模型,不过你可以使用面向对象的聚合原理来克服这种限制.

子类的声明
UNDER关键字表明当前类为某个类的子类.
子类型的应用有一些限制.
1.你不能重写类型的属性,也就是当你声明子类型时,不能列出它们.
2.MAP和ORDER成员函数是参数列表的元素.这个只用在对象类型中.
当父类已经存在MAP或ORDER函数的时候,你尝试在子类再放一个MAP或ORDER函数,将会获得PLS-00154错误.
如:创建order_comp的子类order_subcomp

22:47:12 SCOTT@orcl> create or replace type order_subcomp UNDER order_comp
22:50:48   2  (
22:50:48   3    subtitle varchar2(20),
22:50:48   4    constructor function order_subcomp(who      varchar2,
22:50:48   5                                       movie    varchar2,
22:50:48   6                                       subtitle varchar2)
22:50:48   7      return self as result,
22:50:48   8    overriding member function to_string return varchar2
22:50:48   9  )
22:50:48  10  instantiable final;
22:50:49  11  /


Type created.


Elapsed: 00:00:00.22

注意上面的子类构造函数继承了父类order_comp的两个属性who和movie,并重写了to_string方法.

--子类对象类型体的实现
22:50:51 SCOTT@orcl> CREATE OR REPLACE TYPE BODY order_subcomp IS
22:54:32   2    CONSTRUCTOR FUNCTION order_subcomp(who      VARCHAR2,
22:54:32   3                                       movie    VARCHAR2,
22:54:32   4                                       subtitle VARCHAR2) RETURN SELF AS RESULT IS
22:54:32   5    BEGIN
22:54:32   6      self.who      := who;
22:54:32   7      self.movie    := movie;
22:54:32   8      self.subtitle := subtitle;
22:54:32   9      RETURN;
22:54:32  10    END order_subcomp;
22:54:32  11    OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
22:54:32  12    BEGIN
22:54:32  13      RETURN(self as order_comp).to_string || '[' || self.subtitle || ']';
22:54:32  14    END to_string;
22:54:32  15  END;
22:54:34  16  /


Type body created.


Elapsed: 00:00:00.14

重写的成员函数to_string中出现了一个新的语法:(self as order_comp).to_string
这个语法让你调用父类的方法.这里调用了父类的to_string方法.
你不能在子类中直接访问父类的属性.

--子类对象类型的使用
23:00:06 SCOTT@orcl> DECLARE
23:00:29   2    -- Declare a collection of an object type.
23:00:29   3    TYPE object_list IS TABLE OF ORDER_COMP;
23:00:29   4    -- Initialize one subtype.
23:00:29   5    object1 ORDER_SUBCOMP := order_subcomp('Ron Weasley',
23:00:29   6                                           'Harry Potter 1',
23:00:29   7                                           'Socerer''s Stone');
23:00:29   8    -- Initialize seven types.
23:00:29   9    object2 ORDER_COMP := order_comp('Harry Potter', 'Harry Potter 1');
23:00:29  10    object3 ORDER_COMP := order_comp('Luna Lovegood', 'Harry Potter5');
23:00:29  11    object4 ORDER_COMP := order_comp('Hermione Granger', 'Harry Potter 1');
23:00:29  12    object5 ORDER_COMP := order_comp('Hermione Granger', 'Harry Potter 2');
23:00:29  13    object6 ORDER_COMP := order_comp('Harry Potter', 'Harry Potter 5');
23:00:29  14    object7 ORDER_COMP := order_comp('Cedric Diggory', 'Harry Potter 4');
23:00:29  15    object8 ORDER_COMP := order_comp('Severus Snape', 'Harry Potter1');
23:00:29  16    -- Define a collection of the object type.
23:00:29  17    objects OBJECT_LIST := object_list(object1,
23:00:29  18                                       object2,
23:00:29  19                                       object3,
23:00:29  20                                       object4,
23:00:29  21                                       object5,
23:00:29  22                                       object6,
23:00:29  23                                       object7,
23:00:29  24                                       object8);
23:00:29  25    -- Swaps A and B.
23:00:29  26    PROCEDURE swap(a IN OUT ORDER_COMP, b IN OUT ORDER_COMP) IS
23:00:29  27      c ORDER_COMP;
23:00:29  28    BEGIN
23:00:29  29      c := b;
23:00:29  30      b := a;
23:00:29  31      a := c;
23:00:29  32    END swap;
23:00:29  33  BEGIN
23:00:29  34      FOR i IN 1 .. objects.COUNT LOOP
23:00:29  35      dbms_output.put_line('排序前:'||objects(i).to_string);
23:00:29  36    END LOOP;
23:00:29  37     dbms_output.put_line('-----------');
23:00:29  38    -- A bubble sort.
23:00:29  39    FOR i IN 1 .. objects.COUNT LOOP
23:00:29  40      FOR j IN 1 .. objects.COUNT LOOP
23:00:29  41        IF objects(i).equals(objects(j)) = 1 THEN
23:00:29  42          swap(objects(i), objects(j));
23:00:29  43        END IF;
23:00:29  44      END LOOP;
23:00:29  45    END LOOP;
23:00:29  46    -- Print reordered objects.
23:00:29  47    FOR i IN 1 .. objects.COUNT LOOP
23:00:29  48      dbms_output.put_line('排序后:'||objects(i).to_string);
23:00:29  49    END LOOP;
23:00:29  50  END;
23:00:29  51  /
排序前:[Harry Potter 1][Ron Weasley][Socerer's Stone]
排序前:[Harry Potter 1][Harry Potter]
排序前:[Harry Potter 5][Luna Lovegood]
排序前:[Harry Potter 1][Hermione Granger]
排序前:[Harry Potter 2][Hermione Granger]
排序前:[Harry Potter 5][Harry Potter]
排序前:[Harry Potter 4][Cedric Diggory]
排序前:[Harry Potter 1][Severus Snape]
-----------
排序后:[Harry Potter 1][Harry Potter]
排序后:[Harry Potter 1][Hermione Granger]
排序后:[Harry Potter 1][Ron Weasley][Socerer's Stone]
排序后:[Harry Potter 1][Severus Snape]
排序后:[Harry Potter 2][Hermione Granger]
排序后:[Harry Potter 4][Cedric Diggory]
排序后:[Harry Potter 5][Harry Potter]
排序后:[Harry Potter 5][Luna Lovegood]


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.04

这里子类实例被添加到基类的集合变量中,并做为实参传送到了ORDER成员函数和局部的swap存储中.
子类可在运行时进行隐式转换,此时就和临时对象类型一样.
如:

23:00:32 SCOTT@orcl> CREATE TABLE harry_potter
23:06:45   2  ( harry_potter_id NUMBER
23:06:45   3  , character_role ORDER_COMP );


Table created.


Elapsed: 00:00:00.40
23:06:46 SCOTT@orcl> INSERT INTO harry_potter
23:07:25   2  VALUES(1, order_subcomp('Ron Weasley', 'Harry Potter 1', 'Socerer''s Stone'));


1 row created.


Elapsed: 00:00:00.09
23:07:25 SCOTT@orcl> INSERT INTO harry_potter
23:07:25   2  VALUES(1, order_comp('Hermione Granger', 'Harry Potter 1'));


1 row created.


Elapsed: 00:00:00.00
23:08:52 SCOTT@orcl> SELECT character_role FROM harry_potter;


CHARACTER_ROLE(WHO, MOVIE)
-----------------------------------------------------------------
ORDER_SUBCOMP('Ron Weasley', 'Harry Potter 1', 'Socerer''s Stone')


ORDER_COMP('Hermione Granger', 'Harry Potter 1')


2 rows selected.


Elapsed: 00:00:00.16

这里同时插入了一个子类对象和一个父类对象到表中.插入时子类隐式转换为父类.
通过SELECT可以看到列标题显示为父类对象的构造函数,查询的结果,
一行调用了子类的构造函数,另一行存储了调用父类的构造函数.
你可以通过TREAT函数来查询对象内容.
如:

23:09:01 SCOTT@orcl> SELECT TREAT(character_role AS ORDER_COMP).to_string() AS character_role
23:13:25   2    FROM harry_potter;


CHARACTER_ROLE
------------------------------------------------------------
[Harry Potter 1][Ron Weasley][Socerer's Stone]
[Harry Potter 1][Hermione Granger]


2 rows selected.


Elapsed: 00:00:00.02

TREAT函数实际是构造了一个子类或父类的实例.你在表列中声明的类型应当总是父类.
如果你在表中声明了一个子类类型的列,插入时会报错.

23:16:56 SCOTT@orcl> CREATE TABLE sub_harry_potter
23:17:23   2  ( harry_potter_id NUMBER
23:17:23   3  , character_role ORDER_subCOMP );


Table created.


Elapsed: 00:00:00.06
23:17:24 SCOTT@orcl> INSERT INTO sub_harry_potter
23:18:40   2  VALUES(1, order_subcomp('Hermione Granger', 'Harry Potter 1'));
VALUES(1, order_subcomp('Hermione Granger', 'Harry Potter 1'))
          *
ERROR at line 2:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'ORDER_SUBCOMP
'




Elapsed: 00:00:00.01
23:18:40 SCOTT@orcl> INSERT INTO sub_harry_potter
23:18:40   2  VALUES(1, order_subcomp('Ron Weasley', 'Harry Potter 1', 'Socerer'
's Stone'));


1 row created.


Elapsed: 00:00:00.01
23:18:56 SCOTT@orcl> INSERT INTO sub_harry_potter
23:19:32   2  VALUES(1, order_comp('Hermione Granger', 'Harry Potter 1'));
VALUES(1, order_comp('Hermione Granger', 'Harry Potter 1'))
          *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected SCOTT.ORDER_SUBCOMP got SCOTT.ORDER_COMP


Elapsed: 00:00:00.01

类型的进化
当数据库永久对象已经存储数据的时候,更改对象类型是需要非常谨慎的.
如果必须要更改的话,你需要一个迁移策略,从旧的永久对象类型迁移到新的.
一旦你声明某对象类型作为表的列类型后,你不能改变该对象类型.
当子类存在数据库的时候,你给父类增加属性,将会获得ORA-02303错误.
如:

23:21:21 SCOTT@orcl> create or replace type order_comp is object
23:29:20   2  (
23:29:20   3    who varchar2(60),
23:29:20   4    movie varchar2(60),
23:29:20   5    addtest varchar2(60),
23:29:20   6    constructor function order_comp(who varchar2,movie varchar2,addtest VARCHAR2) RETURN SELF AS RESULT,
23:29:20   7    member function to_string return varchar2,
23:29:20   8    order member function equals(object order_comp)return number
23:29:20   9  )
23:29:20  10  instantiable not final;
23:29:21  11  /
create or replace type order_comp is object
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents




Elapsed: 00:00:00.03

对象集合体的实现
实现一个对象类型的集合是比较容易的.
你可以声明一个以该对象类型为元素类型的VARRAY或者嵌套表.

--创建item_object类型的集合类型(嵌套表)
23:29:22 SCOTT@orcl> CREATE OR REPLACE TYPE item_table IS TABLE OF item_object;
23:32:17   2  /


Type created.


Elapsed: 00:00:00.20

集合的wrapper应该定义至少一个实例变量.这个石磊变量应该是集合数据类型.

--ITEM_TABLE集合类型的使用
23:32:19 SCOTT@orcl> CREATE OR REPLACE TYPE items_object IS OBJECT
23:35:51   2  (
23:35:51   3    items_table ITEM_TABLE,
23:35:51   4    CONSTRUCTOR FUNCTION items_object RETURN SELF AS RESULT,
23:35:51   5    CONSTRUCTOR FUNCTION items_object(items_table ITEM_TABLE)
23:35:51   6      RETURN SELF AS RESULT,
23:35:51   7    MEMBER FUNCTION get_size RETURN NUMBER,
23:35:51   8    STATIC FUNCTION get_items_table RETURN ITEM_TABLE
23:35:51   9  )
23:35:51  10  INSTANTIABLE NOT FINAL;
23:35:52  11  /


Type created.


Elapsed: 00:00:00.07
23:35:54 SCOTT@orcl> CREATE OR REPLACE TYPE BODY items_object IS
23:39:39   2    CONSTRUCTOR FUNCTION items_object RETURN SELF AS RESULT IS
23:39:39   3      c    NUMBER := 1; -- Counter for table index.
23:39:39   4      item ITEM_OBJECT;
23:39:39   5      CURSOR c1 IS
23:39:39   6        SELECT ename, job FROM emp;
23:39:39   7    BEGIN
23:39:39   8      FOR i IN c1 LOOP
23:39:39   9        item := item_object(i.ename, i.job);
23:39:39  10        items_table.EXTEND;
23:39:39  11        self.items_table(c) := item; -- Must use something other than loop index.
23:39:39  12        c := c + 1;
23:39:39  13      END LOOP;
23:39:39  14      RETURN;
23:39:39  15    END items_object;
23:39:39  16    CONSTRUCTOR FUNCTION items_object(items_table ITEM_TABLE)
23:39:39  17      RETURN SELF AS RESULT IS
23:39:39  18    BEGIN
23:39:39  19      self.items_table := items_table;
23:39:39  20      RETURN;
23:39:39  21    END items_object;
23:39:39  22    MEMBER FUNCTION get_size RETURN NUMBER IS
23:39:39  23    BEGIN
23:39:39  24      RETURN self.items_table.COUNT;
23:39:39  25    END get_size;
23:39:39  26    STATIC FUNCTION get_items_table RETURN ITEM_TABLE IS
23:39:39  27      c           NUMBER := 1; -- Counter for table index.
23:39:39  28      item        ITEM_OBJECT;
23:39:39  29      items_table ITEM_TABLE := item_table();
23:39:39  30      CURSOR c1 IS
23:39:39  31        SELECT ename, job FROM emp;
23:39:39  32    BEGIN
23:39:39  33      FOR i IN c1 LOOP
23:39:39  34        item := item_object(i.ename, i.job);
23:39:39  35        items_table.EXTEND;
23:39:39  36        items_table(c) := item; -- Must use something other than loop index.
23:39:39  37        c := c + 1;
23:39:39  38      END LOOP;
23:39:39  39      RETURN items_table;
23:39:39  40    END get_items_table;
23:39:39  41  END;
23:39:39  42  /


Type body created.


Elapsed: 00:00:00.15
23:39:40 SCOTT@orcl> DECLARE
23:41:03   2    items ITEMS_OBJECT;
23:41:03   3  BEGIN
23:41:03   4    items := items_object(items_object.get_items_table);
23:41:03   5    dbms_output.put_line(items.get_size);
23:41:03   6  END;
23:41:13   7  /
14


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.00

items_object构造函数使用一个静态函数get_items_table作为它的实际参数.
静态函数get_items_table返回了集合类型变量items_table.
其中每个元素都是item_object类型的.

--打印集合类型变量items_table中的内容
--使用了item_object对象类型的to_string方法
23:46:10 SCOTT@orcl> DECLARE
23:46:26   2    items ITEM_TABLE;
23:46:26   3  BEGIN
23:46:26   4    items := items_object.get_items_table;
23:46:26   5    FOR i IN 1 .. items.COUNT LOOP
23:46:26   6      dbms_output.put_line(items(i).to_string);
23:46:26   7    END LOOP;
23:46:26   8  END;
23:46:27   9  /
[SMITH][CLERK]
[ALLEN][SALESMAN]
[WARD][SALESMAN]
[JONES][MANAGER]
[MARTIN][SALESMAN]
[BLAKE][MANAGER]
[CLARK][MANAGER]
[SCOTT][ANALYST]
[KING][PRESIDENT]
[TURNER][SALESMAN]
[ADAMS][CLERK]
[JAMES][CLERK]
[FORD][ANALYST]
[MILLER][CLERK]


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.01