128.Oracle数据库SQL开发之 数据库对象——用子类型对象代替超类型对象

128.Oracle数据库SQL开发之 数据库对象——用子类型对象代替超类型对象

       欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50087089

  使用子类型对象代替超类型对象，当存储和操纵相关的类型时，用子类型对象代替超类型对象会提供很大的灵活性。

1.  SQL例子

创建t_person类型的一个表如下：

CREATE TABLE object_customers OF t_person;

插入一条：

INSERT INTO object_customers VALUES (

  t_person(1,'Jason', 'Bond', '03-APR-1965', '800-555-1212',

   t_address('21 New Street', 'Anytown', 'CA', '12345')

  )

);

INSERT INTO object_customers VALUES (

 t_business_person(2, 'Steve', 'Edwards', '03-MAR-1955', '800-555-1212',

   t_address('1 Market Street', 'Anytown', 'VA', '12345'),

    'Manager','XYZ Corp'

  )

);

查看如下：

object_user2@PDB1> select * from object_customerso;

 

         IDFIRST_NAME LAST_NAME  DOB         PHONE

---------- ---------- ---------- ---------------------

ADDRESS(STREET, CITY, STATE, ZIP)

----------------------------------------------------------------------------------------------------

          1 Jason     Bond       03-APR-65 800-555-1212

T_ADDRESS('21 New Street', 'Anytown', 'CA','12345')

 

          2 Steve     Edwards          03-MAR-55800-555-1212

T_ADDRESS('1 Market Street', 'Anytown','VA', '12345')

查看如下：

object_user2@PDB1> select value(o) fromobject_customers o;

 

VALUE(O)(ID, FIRST_NAME, LAST_NAME, DOB,PHONE, ADDRESS(STREET, CITY, STATE, ZIP))

----------------------------------------------------------------------------------------------------

T_PERSON(1, 'Jason', 'Bond', '03-APR-65','800-555-1212', T_ADDRESS('21 New Street', 'Anytown', 'CA'

, '12345'))

 

T_BUSINESS_PERSON(2, 'Steve', 'Edwards','03-MAR-55', '800-555-1212', T_ADDRESS('1 Market Street', '

Anytown', 'VA', '12345'), 'Manager', 'XYZCorp')

2.  PL/SQL例子

也可以在PL/SQL中操作子类型和超类型对象。

创建过程如下：

CREATE PROCEDURE subtypes_and_supertypes AS

  -- createobjects

 v_business_person t_business_person :=

   t_business_person(

      1,'John', 'Brown',

      '01-FEB-1955','800-555-1211',

     t_address('2 State Street', 'Beantown', 'MA', '12345'),

     'Manager', 'XYZ Corp'

    );

  v_persont_person :=

    t_person(1,'John', 'Brown', '01-FEB-1955', '800-555-1211',

     t_address('2 State Street', 'Beantown', 'MA', '12345'));

 v_business_person2 t_business_person;

  v_person2t_person;

BEGIN

  -- assignv_business_person to v_person2

  v_person2 :=v_business_person;

 DBMS_OUTPUT.PUT_LINE('v_person2.id = ' || v_person2.id);

 DBMS_OUTPUT.PUT_LINE('v_person2.first_name = ' ||

   v_person2.first_name);

 DBMS_OUTPUT.PUT_LINE('v_person2.last_name = ' ||

   v_person2.last_name);

 

  -- thefollowing lines will not compile because v_person2

  -- is of typet_person, and t_person does not know about the

  -- additionaltitle and company attributes

  --DBMS_OUTPUT.PUT_LINE('v_person2.title = ' ||

  --   v_person2.title);

  --DBMS_OUTPUT.PUT_LINE('v_person2.company = ' ||

  --   v_person2.company);

 

  -- thefollowing line will not compile because you cannot

  -- directlyassign a t_person object to a t_business_person

  -- object

  --v_business_person2 := v_person;

END subtypes_and_supertypes;

/

调用过程如下：

object_user2@PDB1> set serveroutput on

object_user2@PDB1> call subtypes_and_supertypes();

v_person2.id = 1

v_person2.first_name = John

v_person2.last_name = Brown

 

Call completed.

3.  NOT SUBSTITUTABLE对象

如果想要阻止用子类型对象代替超类型对象，可以将一个对象表或对象列标记为不可代替。

CREATE TABLE object_customers_not_subs OF t_person

NOT SUBSTITUTABLE AT ALL LEVELS;

 

CREATE TYPE t_vehicle AS OBJECT (

  id    INTEGER,

  make  VARCHAR2(15),

  modelVARCHAR2(15)

) NOT FINAL NOT INSTANTIABLE;

/

其中NOT SUBSTITUTABLE AT ALL LEVELS子句指出除t_person类型的对象之外，其他类型的对象都不能插入到此表中。