第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