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类型的对象之外,其他类型的对象都不能插入到此表中。