oracle 11g PL/SQL Programming学习十五
来源:互联网 发布:triz矛盾矩阵表 编辑:程序博客网 时间:2024/05/23 02:07
----------------------------------------------------------------------------
-----------------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 object23: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_string23:18:50 7 )23:18:50 8 instantiable not final;23:18:53 9 /Type created.Elapsed: 00:00:00.3623: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 RESULTMETHOD------ 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 is23:34:39 2 constructor function hello_there RETURN SELF AS RESULT is23:34:39 3 hello hello_there := hello_there('默认构造对象');23:34:39 4 begin23: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 is23:34:39 9 begin23: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 is23:34:39 14 begin23: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> declare23:37:22 2 hello hello_there:=hello_there;23:37:43 3 BEGIN23: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> declare23:39:30 2 hello hello_there:=hello_there('自定义对象');23:40:07 3 BEGIN23: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 object23: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 varchar223: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 is23:58:50 2 constructor function item_object RETURN SELF AS RESULT is23:58:50 3 item item_object := item_object('默认主标题','默认副标题');23:58:50 4 begin23: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 is23:58:50 9 begin23: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 is23:58:50 15 item item_object;23:58:50 16 cursor c(item_id_id number) is23:58:50 17 select '主&'||ename item_title,'副&'||sal item_subtitle from emp where empno=item_id_id;23:58:50 18 begin23:58:50 19 for i in c(item_id) loop23: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 is23:58:50 25 begin23: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> begin00: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 object00: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 varchar200:14:44 6 )00:14:44 7 instantiable not final;00:14:46 8 /Type created.Elapsed: 00:00:00.1200:14:47 SCOTT@orcl> create or replace type body map_comp is00:17:25 2 constructor function map_comp(who varchar2) RETURN SELF AS RESULT is00:17:25 3 begin00: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 is00:17:25 8 begin00: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.0800:22:21 SCOTT@orcl> DECLARE00: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) IS00:22:51 13 c MAP_COMP;00:22:51 14 BEGIN00: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 BEGIN00:22:51 20 -- Print reordered objects.00:22:51 21 FOR i IN 1 .. objects.COUNT LOOP00: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 LOOP00:22:51 27 FOR j IN 1 .. objects.COUNT LOOP00:22:51 28 IF objects(i).equals = LEAST(objects(i).equals, objects(j).equals) THEN00: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 LOOP00: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 LovegoodPL/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 object00: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 number00:34:06 8 )00:34:06 9 instantiable not final;00:34:06 10 /Type created.Elapsed: 00:00:00.0900:44:25 SCOTT@orcl> create or replace type body order_comp is00:45:48 2 constructor function order_comp(who varchar2,movie varchar2) RETURN SELF AS RESULT is00:45:48 3 begin00: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 is00:45:48 9 begin00: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 is00:45:48 13 begin00:45:48 14 -- The primary sort.00:45:48 15 IF self.movie < object.movie THEN00: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 THEN00:45:48 19 RETURN 1;00:45:48 20 ELSE00: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.0700:45:50 SCOTT@orcl> DECLARE00: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) IS00:49:34 24 c ORDER_COMP;00:49:34 25 BEGIN00: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 BEGIN00:49:34 31 FOR i IN 1 .. objects.COUNT LOOP00: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 LOOP00:49:34 37 FOR j IN 1 .. objects.COUNT LOOP00:49:34 38 IF objects(i).equals(objects(j)) = 1 THEN00: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 LOOP00: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.0200:49:36 SCOTT@orcl> DECLARE00: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) IS00:52:54 24 c ORDER_COMP;00:52:54 25 BEGIN00: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 BEGIN00:52:54 31 FOR i IN 1 .. objects.COUNT LOOP00: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 LOOP00:52:54 37 FOR j IN 1 .. objects.COUNT LOOP00:52:54 38 IF objects(i).equals(objects(j)) = 0 THEN00: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 LOOP00: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_comp22: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 varchar222: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 IS22: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 IS22:54:32 5 BEGIN22: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 IS22:54:32 12 BEGIN22: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> DECLARE23: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) IS23:00:29 27 c ORDER_COMP;23:00:29 28 BEGIN23: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 BEGIN23:00:29 34 FOR i IN 1 .. objects.COUNT LOOP23: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 LOOP23:00:29 40 FOR j IN 1 .. objects.COUNT LOOP23:00:29 41 IF objects(i).equals(objects(j)) = 1 THEN23: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 LOOP23: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_potter23:06:45 2 ( harry_potter_id NUMBER23:06:45 3 , character_role ORDER_COMP );Table created.Elapsed: 00:00:00.4023:06:46 SCOTT@orcl> INSERT INTO harry_potter23:07:25 2 VALUES(1, order_subcomp('Ron Weasley', 'Harry Potter 1', 'Socerer''s Stone'));1 row created.Elapsed: 00:00:00.0923:07:25 SCOTT@orcl> INSERT INTO harry_potter23:07:25 2 VALUES(1, order_comp('Hermione Granger', 'Harry Potter 1'));1 row created.Elapsed: 00:00:00.0023: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_role23: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_potter23:17:23 2 ( harry_potter_id NUMBER23:17:23 3 , character_role ORDER_subCOMP );Table created.Elapsed: 00:00:00.0623:17:24 SCOTT@orcl> INSERT INTO sub_harry_potter23: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.0123:18:40 SCOTT@orcl> INSERT INTO sub_harry_potter23:18:40 2 VALUES(1, order_subcomp('Ron Weasley', 'Harry Potter 1', 'Socerer''s Stone'));1 row created.Elapsed: 00:00:00.0123:18:56 SCOTT@orcl> INSERT INTO sub_harry_potter23: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_COMPElapsed: 00:00:00.01
类型的进化
当数据库永久对象已经存储数据的时候,更改对象类型是需要非常谨慎的.
如果必须要更改的话,你需要一个迁移策略,从旧的永久对象类型迁移到新的.
一旦你声明某对象类型作为表的列类型后,你不能改变该对象类型.
当子类存在数据库的时候,你给父类增加属性,将会获得ORA-02303错误.
如:
23:21:21 SCOTT@orcl> create or replace type order_comp is object23: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 number23: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 dependentsElapsed: 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 OBJECT23: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_TABLE23:35:51 9 )23:35:51 10 INSTANTIABLE NOT FINAL;23:35:52 11 /Type created.Elapsed: 00:00:00.0723:35:54 SCOTT@orcl> CREATE OR REPLACE TYPE BODY items_object IS23:39:39 2 CONSTRUCTOR FUNCTION items_object RETURN SELF AS RESULT IS23:39:39 3 c NUMBER := 1; -- Counter for table index.23:39:39 4 item ITEM_OBJECT;23:39:39 5 CURSOR c1 IS23:39:39 6 SELECT ename, job FROM emp;23:39:39 7 BEGIN23:39:39 8 FOR i IN c1 LOOP23: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 IS23:39:39 18 BEGIN23: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 IS23:39:39 23 BEGIN23: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 IS23: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 IS23:39:39 31 SELECT ename, job FROM emp;23:39:39 32 BEGIN23:39:39 33 FOR i IN c1 LOOP23: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.1523:39:40 SCOTT@orcl> DECLARE23:41:03 2 items ITEMS_OBJECT;23:41:03 3 BEGIN23: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 /14PL/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> DECLARE23:46:26 2 items ITEM_TABLE;23:46:26 3 BEGIN23:46:26 4 items := items_object.get_items_table;23:46:26 5 FOR i IN 1 .. items.COUNT LOOP23: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
- oracle 11g PL/SQL Programming学习十五
- oracle 11g PL/SQL Programming学习一
- oracle 11g PL/SQL Programming学习二
- oracle 11g PL/SQL Programming学习三
- oracle 11g PL/SQL Programming学习四
- oracle 11g PL/SQL Programming学习五
- oracle 11g PL/SQL Programming学习六
- oracle 11g PL/SQL Programming学习七
- oracle 11g PL/SQL Programming学习八
- oracle 11g PL/SQL Programming学习九
- oracle 11g PL/SQL Programming学习十
- oracle 11g PL/SQL Programming学习十一
- oracle 11g PL/SQL Programming学习十二
- oracle 11g PL/SQL Programming学习十三
- oracle 11g PL/SQL Programming学习十四
- oracle 11g PL/SQL Programming学习十六
- oracle 11g PL/SQL Programming学习十七
- Oracle Database 11g PL/SQL Programming
- 一道面试题:有一个整数数组,请求出两两之差绝对值最小的值,记住,只要得出最小值即可,不需要求出是哪两个数。
- hdu2059 龟兔赛跑
- Ajax提交表单中文乱码的一种解决方法
- Problem D hdu 3371 Connect the Cities
- HDU1505-----DP?HDU1506的加强版
- oracle 11g PL/SQL Programming学习十五
- Gson - Unparseable date
- Ant配置、打包项目
- directfb
- VMware vSphere服务器虚拟化实验五 vCenter Server 网络管理
- c++ new操作符的重载
- HTTP Status 404 - 解决办法
- hdu2159 TATE
- android的简单消息机制