資料庫表--object table

建立型別
JEL@JEL >create or replace type address_type
  2  as object
  3  (city varchar2(30),
  4  street varchar2(30),
  5  state varchar2(2),
  6  zip number)
  7  /

Type created.

JEL@JEL >create or replace type person_type
  2  as object
  3  (name varchar2(30),
  4  dob date,
  5  home_address address_type,
  6  work_address address_type)
  7  /

Type created.

建立表
JEL@JEL >create table people of person_type;

Table created.

JEL@JEL >desc people
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 DOB                                                DATE
 HOME_ADDRESS                                       ADDRESS_TYPE
 WORK_ADDRESS                                       ADDRESS_TYPE

插入資料
JEL@JEL >insert into people values('tom',to_date('19811008','YY/MM/DD'),address_type('reston','123 main street','va','45678'),address_type('rewood','1 oracle way','ca','23456'));

1 row created.

JEL@JEL >select * from people;

NAME                           DOB
------------------------------ ---------
HOME_ADDRESS(CITY, STREET, STATE, ZIP)
--------------------------------------------------------------------------------
WORK_ADDRESS(CITY, STREET, STATE, ZIP)
--------------------------------------------------------------------------------
tom                            08-OCT-81
ADDRESS_TYPE('reston', '123 main street', 'va', 45678)
ADDRESS_TYPE('rewood', '1 oracle way', 'ca', 23456)


查詢資料
JEL@JEL >select name,p.home_address.city from people p;

NAME                           HOME_ADDRESS.CITY
------------------------------ ------------------------------
tom                            reston

JEL@JEL >SELECT NAME,P.HOME_ADDRESS.ZIP FROM PEOPLE P;

NAME                           HOME_ADDRESS.ZIP
------------------------------ ----------------
tom                                       45678


物件型別內的function和procedure
 建立type和type body：
 

    create or replace type person as object

      (

        NAME varchar2 ( 10 ),

        SEX char ( 2 ),

        BIRTHDATE date ,

        PLACE varchar2 ( 100 ),

        member procedure chang_name( name varchar2 ),

        static function new (v_name varchar2 ,v_sex varchar2 ) return person

      );

 

    create or replace type body person is

      member procedure chang_name( name varchar2 ) is

        begin

          self.name:= name ;

        end chang_name;

      static function new (v_name varchar2 ,v_sex varchar2 ) return person

      is

        begin

          return (person(v_name,v_sex, null , null ));

        end new ;

    end ;
 
    在過程中呼叫兩種不同型別的方法：
 

    declare

      person_one person;

      person_two person;

    begin

      person_one:=person( ' 李四 ' , ' 男 ' , date '2008-10-20' , ' 上海 ' );--建立例項

      person_one.chang_name( ' 王五 ' );

      dbms_output.put_line(person_one.name);

 

      person_two:=person.new( ' 小張 ' , ' 女 ' );--可直接呼叫

      dbms_output.put_line(person_two.name);

    end ;

物件檢視
JEL@JEL >create table people_tab
  2  (name varchar2(30) primary key,
  3  dob date,
  4  home_city varchar2(30),
  5  home_street varchar2(30),
  6  home_state varchar2(2),
  7  home_zip number,
  8  work_city varchar2(30),
  9  work_street varchar2(30),                    
 10  work_state varchar2(2),
 11  work_zip number);

Table created.

JEL@JEL >create view v_people of person_type
  2  with object identifier(name)
  3  as
  4  select name,dob,              
  5  address_type(home_city,home_street,home_state,home_zip) home_address,
  6  address_type(work_city,work_street,work_state,work_zip) work_address
  7  from people_tab;

View created.

JEL@JEL >insert into v_people values('tom',to_date('19811008','YY/MM/DD'),address_type('reston','123 main street','va','45678'),address_type('rewood','1 oracle way','ca','23456'));

1 row created.

JEL@JEL >select * from people_tab;

NAME                           DOB       HOME_CITY
------------------------------ --------- ------------------------------
HOME_STREET                    HO   HOME_ZIP WORK_CITY
------------------------------ -- ---------- ------------------------------
WORK_STREET                    WO   WORK_ZIP
------------------------------ -- ----------
tom                            08-OCT-81 reston
123 main street                va      45678 rewood
1 oracle way                   ca      23456