Oracle_database_11g完全参考手册

create or replace type ADDRESS_TY as object
(Street  VARCHAR2(50),
 City    VARCHAR2(25),
 State   CHAR(2),
 Zip     NUMBER);
/

create or replace type ANIMAL_TY as object
(Breed      VARCHAR2(25),
 Name       VARCHAR2(25),
 BirthDate  DATE,
member function AGE (BirthDate IN DATE) return NUMBER);
/

create or replace type body ANIMAL_TY as
member function Age (BirthDate DATE) return NUMBER is
begin
  RETURN ROUND(SysDate - BirthDate);
end;
end;
/

rem  For the Nested Table examples:
create type ANIMALS_NT as table of ANIMAL_TY;
/

rem  Requires that the ADDRESS_TY type already exist.

create type PERSON_TY as object
(Name      VARCHAR2(25),

 Address   ADDRESS_TY);
/

rem  Creates a varying array datatype.

create or replace type TOOLS_VA as varray(5) of VARCHAR2(25);
/

drop table ADDRESS;
create table ADDRESS (
LastName   VARCHAR2(25),
FirstName  VARCHAR2(25),
Street     VARCHAR2(50),
City       VARCHAR2(25),
State      CHAR(2),
Zip        NUMBER,
Phone      VARCHAR2(12),
Ext        VARCHAR2(5)
);

insert into ADDRESS values ('BAILEY', 'WILLIAM',
     null,null,null,null,'213-555-0223',null);
insert into ADDRESS values ('ADAMS', 'JACK',
     null,null,null,null,'415-555-7530',null);
insert into ADDRESS values ('SEP', 'FELICIA',
     null,null,null,null,'214-555-8383',null);
insert into ADDRESS values ('DE MEDICI', 'LEFTY',
     null,null,null,null,'312-555-1166',null);
insert into ADDRESS values ('DEMIURGE', 'FRANK',
     null,null,null,null,'707-555-8900',null);