问题描述：

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

现要设计一张表结构, 年纪自动由出生年月算出(但不能用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

发表于 @ 2007年07月31日 22:50:00|评论(loading...)|编辑