首页 > Linux操作系统 > Linux操作系统 > 数据库对象类型的练习
create or replace type address_type
as object
(
street_addr1 varchar2(25),
street_addr2 varchar2(25),
city varchar2(30),
state varchar2(2),
zip_code number
)
/
create table people
(
name varchar2(10),
home_address address_type,
work_address address_type
)
declare
l_home_address address_type;
l_work_address address_type;
begin
l_home_address := address_type('123 Main Street', null,'Reston',
'VA',45678);
l_work_address := address_type('1 Oracle Way',null,'Redwood','CA','23456');
insert into people (name, home_address, work_address)
values
('Tom_Kyte',l_home_address, l_work_address);
end;
/
commit;
select * from people;
select name, p.home_address.city, p.work_address.zip_code from people P
select count(*) from people
alter type address_type
replace
as object
(
street_addr1 varchar2(25),
street_addr2 varchar2(25),
city varchar2(30),
state varchar2(2),
zip_code number,
member function toString return varchar2
)
create or replace type body address_type
as
member function toString return varchar2
is
begin
if(street_addr2 is not null)
then
return street_addr1||chr(10)||street_addr2||chr(10)||city||','||state||' '||zip_code;
else
return street_addr1||chr(10)||city||','||state||' '||zip_code;
end if;
end;
end;
/
select name, P.home_address.toString() from people P;
对象类型不能排序,不能使用> 或者<
alter type address_type
replace
as object
(
street_addr1 varchar2(25),
street_addr2 varchar2(25),
city varchar2(30),
state varchar2(2),
zip_code number,
member function toString return varchar2,
order member function order_function(compare2 in Address_type) return number
)
create or replace type body address_type
as
member function toString return varchar2
is
begin
if(street_addr2 is not null)
then
return street_addr1||chr(10)||street_addr2||chr(10)||city||','||state||' '||zip_code;
else
return street_addr1||chr(10)||city||','||state||' '||zip_code;
end if;
end;
order member function order_function(compare2 in address_type)
return number
is
begin
------ 太长了,不抄袭了
end;
end;
/
create or replace type address_array_type
as varray(25) of address_type;
alter table people add previous_addresses address_array_type;
select * from people where name = 'Tom_Kyte';
declare
l_prev_address address_array_type;
begin
select p.previous_addresses into l_prev_address from people p
where p.name = 'Tom_Kyte';
l_prev_address.extend;
l_prev_address(l_prev_address.count) := address_type('123 Main Street',null
,'Reston', 'VA',45678);
update people
set previous_addresses = l_prev_address
where name = 'Tom_Kyte';
commit;
end;
/
create or replace type emp_type
(
empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2)
)
create or replace type emp_tab_type as table of emp_type;
create or replace type dept_type as object
(
deptno number(2),
dname varchar2(14),
loc varchar2(13),
emps emp_tab_type
)
create table dept
(
deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
create or replace view dept_or of dept_type
with object identifier(deptno)
as
select deptno, dname,loc,
cast ( multiset(
select empno,ename,job,mgr,hiredate,sal,comm
from emp
where emp.deptno = dept.depno)
as emp_tab_type )
from dept
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21634752/viewspace-701572/,如需转载,请注明出处,否则将追究法律责任。