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/，如需转载，请注明出处，否则将追究法律责任。