OOP例子

问题描述：

有一群人数据集,不是教练就是运动员. 教练和运动员的统一属性是姓名,出生年月,年纪
教练有自己的属性是角色
运动员也有自己的属性是身高和体重

现要设计一张表结构, 年纪自动由出生年月算出(但不能用trigger),教练和运动员的区分要用subtype.

 

1 、创建基类对象
create   or   replace  type person  as  object
(
person_name  varchar2 ( 10 ),
date_of_birth date,
age  int ,
person_type  int )
not  final;
/
2 、创建教练和运动员对象
create   or   replace  type coach under person
(
person_role  varchar2 ( 10 ),
constructor  function  coach(person_name  varchar2 ,
date_of_birth date,
person_role  varchar2 )
return  self  as  result
)
/

create   or   replace  type body coach  is

constructor  function  coach(person_name  varchar2 ,
date_of_birth date,
person_role  varchar2 )  return  self  as  result  is
begin
self.person_name : =  person_name;
self.date_of_birth : =  date_of_birth;
self.age : =  trunc((sysdate - date_of_birth) / 365 );
self.person_role : =  person_role;
self.person_type : =   1 ;
return ;
end ;

end ;
/

-- 运动员
create   or   replace  type athlete under person
(
height  number ( 3 ),
weight  number ( 3 ),
constructor  function  athlete(person_name  varchar2 ,
date_of_birth date,
height  number ,
weight  number )
return  self  as  result
)
/
create   or   replace  type body athlete  is

constructor  function  athlete(person_name  varchar2 ,
date_of_birth date,
height  number ,
weight  number )  return  self  as  result  is
begin
self.person_name : =  person_name;
self.date_of_birth : =  date_of_birth;
self.age : =  trunc((sysdate - date_of_birth) / 365 );
self.height : =  height;
self.weight : =  weight;
self.person_type : =   2 ;
return ;
end ;

end ;
/

3 、创建表
create   table  sport_meeting_attendee  of  person;
插入2条记录
insert   into  sport_meeting_attendee  values (coach( ' tom ' ,to_date( ' 1954-12-01 ' . ' yyyy-mm-dd ' ), ' 体操 ' ));
SQL >   Insert   into  sport_meeting_attendee  values  (ATHLETE( ' JIM ' , to_date( ' 1984-05-21 ' , ' YYYY-MM-DD ' ), 188 , 73 ));

4 、对表中结果进行查询
第一种方法：
  select  substr(t.acoach.person_name,  1 ,  10 ) person_name , 
 t.acoach.age,  ' 教练 '  type1, 
 substr(t.acoach.person_role,  1 ,  10 ) person_role ,  NULL  HEIGHT ,  NULL  WEIGHT
  from  (
       select  treat(value(t )  as  coach) acoach
       from  Sport_meeting_attendee t
       where  person_type  =   1  ) t 
union
select  t.athlete.person_name person_name,t.athlete.age, ' 运动员 ' type1,
null  person_role,t.athlete.height height ,t.athlete.weight weight
from (
     select  treat(value(t)  as  athlete) athlete
     from  Sport_meeting_attendee t
     where  person_type  =   2 ) t;
第二种方法：
   select  substr(t.acoach.person_name,  1 ,  10 ) person_name , 
 t.acoach.age age,  ' 教练 '  type1, 
 substr(t.acoach.person_role,  1 ,  10 ) person_role ,  NULL  HEIGHT ,  NULL  WEIGHT
  from  (
       select  treat(value(p)  as  coach) acoach
       from  Sport_meeting_attendee p
       where  value(p)  is   of  (coach) ) t         
union
select  t.athlete.person_name person_name,t.athlete.age, ' 运动员 ' type1,
null  person_role,t.athlete.height height ,t.athlete.weight weight
from (
     select  treat(value(p)  as  athlete) athlete
     from  Sport_meeting_attendee p
     where  value(p)  is   of  (athlete)) t;
    

 

VALUE

Syntax

Description of the illustration value.gif

Purpose

VALUE takes as its argument a correlation variable (table alias) associated with a row of an object table and returns object instances stored in the object table. The type of the object instances is the same type as the object table.

IS OF type Condition

Use the IS OF type condition to test object instances based on their specific type information.

is_of_type_conditions::=

Description of the illustration is_of_type_conditions.gif

You must have EXECUTE privilege on all types referenced by type, and all types must belong to the same type family.

This condition evaluates to null if expr is null. If expr is not null, then the condition evaluates to true (or false if you specify the NOT keyword) under either of these circumstances:

• The most specific type of expr is the subtype of one of the types specified in the type list and you have not specified ONLY for the type, or

• The most specific type of expr is explicitly specified in the type list.

The expr frequently takes the form of the VALUE function with a correlation variable.

The following example uses the sample table oe.persons, which is built on a type hierarchy in "Substitutable Table and Column Examples". The example uses the IS OF type condition to restrict the query to specific subtypes:

TREAT

Syntax

Description of the illustration treat.gif

Purpose

TREAT changes the declared type of an expression.

You must have the EXECUTE object privilege on type to use this function.

• type must be some supertype or subtype of the declared type of expr. If the most specific type of expr is type (or some subtype of type), then TREAT returns expr. If the most specific type of expr is not type (or some subtype of type), then TREAT returns NULL.

• You can specify REF only if the declared type of expr is a REF type.

• If the declared type of expr is a REF to a source type of expr, then type must be some subtype or supertype of the source type of expr. If the most specific type of DEREF(expr) is type (or a subtype of type), then TREAT returns expr. If the most specific type of DEREF(expr) is not type (or a subtype of type), then TREAT returns NULL.

This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.

 讨论见：http://www.itpub.net/showthread.php?s=&threadid=322109&perpage=10&pagenumber=1