问题描述:
有一群人数据集,不是教练就是运动员. 教练和运动员的统一属性是姓名,出生年月,年纪
教练有自己的属性是角色
运动员也有自己的属性是身高和体重
现要设计一张表结构, 年纪自动由出生年月算出(但不能用trigger),教练和运动员的区分要用subtype.
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 ;
/
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 ));
第一种方法:
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 type
s 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 thetype
list and you have not specifiedONLY
for the type, or -
The most specific type of
expr
is explicitly specified in thetype
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 ofexpr
. If the most specific type ofexpr
istype
(or some subtype oftype
), thenTREAT
returnsexpr
. If the most specific type ofexpr
is nottype
(or some subtype oftype
), thenTREAT
returnsNULL
. -
You can specify
REF
only if the declared type ofexpr
is aREF
type. -
If the declared type of
expr
is aREF
to a source type ofexpr
, thentype
must be some subtype or supertype of the source type ofexpr
. If the most specific type ofDEREF
(expr
) istype
(or a subtype oftype
), thenTREAT
returnsexpr
. If the most specific type ofDEREF
(expr
) is nottype
(or a subtype oftype
), thenTREAT
returnsNULL
.
This function does not support CLOB
data directly. However, CLOB
s can be passed in as arguments through implicit data conversion.
讨论见:http://www.itpub.net/showthread.php?s=&threadid=322109&perpage=10&pagenumber=1