Oracle Database 10g SQL (Osborne ORACLE Press Series) - pages from 132-140
Type Inheritance
With the release of the Oracle9 i
database, you can use object type inheritance . This allows you to
define hierarchies of database types. For example, you might want to define a
business person object type and have that type inherit existing attributes from
person_typ . The business person type could extend person_typ with attributes to
store the person s job title and the name of the company they work for. For
person_typ to be inherited from, it must be defined using the NOT FINAL clause:
CREATE
TYPE person_typ AS OBJECT (id NUMBER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
dob DATE,
phone VARCHAR2(12),
address address_typ) NOT FINAL;
/
The NOT FINAL clause indicates that
person_typ can be inherited from when defining another type. The default is
FINAL , meaning that the object type cannot be inherited from.
|
Note
|
I ve provided a SQL*Plus script
named object_schema2.sql in the SQL directory that creates a user named
object_user2 with a password of object_password . The object_schema2.sql
script creates the types, tables, and performs the various INSERT statements
shown in the rest of this chapter. You can run the object_ schema2.sql script
if you are using an Oracle9 i database or above.
|
To have a new type inherit
attributes and methods from an existing type, you use the UNDER clause when
defining your new type. Our example business person type, which I ll name
business_person_typ , uses the UNDER clause to inherit the attributes from person_typ
:
CREATE
TYPE business_person_typ UNDER person_typ (title VARCHAR2(20),
company VARCHAR2(20));
/
In this example, person_typ is known
as the supertype , and business_person_typ is known as the subtype .
You can then use business_person_typ when defining column objects or object
tables. For example, the following statement creates an object table named
object_business_customers :
CREATE
TABLE object_business_customers OF business_person_typ;
The following example inserts a row
into object_business_customers . Notice that the two additional title and
company attributes are supplied:
INSERT
INTO object_business_customers VALUES (business_person_typ(1, 'John', 'Brown',
'01-FEB-1955', '800-555-1211',
address_typ('2 State Street', 'Beantown',
'MA', '12345'),
'Manager', 'XYZ Corp'));
The final example selects this row:
SELECT
*FROM object_business_customers;
ID
FIRST_NAME LAST_NAME DOB PHONE
----------
---------- ---------- --------- ------------
ADDRESS(STREET,
CITY, STATE, ZIP)
--------------------------------------------------------
TITLE
COMPANY
--------------------
--------------------
1 John Brown 01-FEB-55
800-555-1211
ADDRESS_TYP('2
State Street', 'Beantown', 'MA', '12345')
Manager
XYZ Corp
NOT INSTANTIABLE Object Types
You can mark an object type as NOT
INSTANTIABLE , which prevents objects of that type from being created. You
might want to mark an object type as NOT INSTANTIABLE when you want to use that
type only as a supertype. For example, you could create a type to represent
vehicles and use it as a supertype for another type to represent cars and
motorcycles. The following statement creates a type named vehicle_typ , which
is marked as NOT INSTANTIABLE :
CREATE
TYPE vehicle_typ AS OBJECT (id NUMBER,
make VARCHAR2(15),
model VARCHAR2(15)) NOT FINAL NOT
INSTANTIABLE;
/
|
Note
|
vehicle_typ is also marked as NOT
FINAL . A NOT INSTANTIABLE type cannot be FINAL because you wouldn t be able
to use it as a supertype, and that s why vehicle_typ is marked as NOT FINAL .
|
The next example creates a type
named car_typ that inherits from the vehicle_typ supertype. Notice car_typ has
an additional attribute named convertible that records whether the car is a
convertible:
CREATE
TYPE car_typ UNDER vehicle_typ (convertible CHAR(1));
/
The following example creates a type
named motorcycle_typ that inherits from the vehicle_typ supertype. Notice
motorcycle_typ has an additional attribute named sidecar that records whether
the motorcycle has a sidecar:
CREATE
TYPE motorcycle_typ UNDER vehicle_typ (sidecar CHAR(1));
/
The next example creates tables
named vehicles , cars and motorcycles , which are object tables that use the
types vehicle_typ , car_typ and motorcycle_typ :
CREATE
TABLE vehicles OF vehicle_typ;
CREATE
TABLE cars OF car_typ;
CREATE
TABLE motorcycles OF motorcycle_typ;
Because vehicle_typ is NOT
INSTANTIABLE , you cannot add a row to the vehicles table. If you attempt to do
so, the database returns an error; for example:
SQL>
INSERT
INTO vehicles VALUES (2 vehicle_typ(1, 'Toyota', 'MR2',
'01-FEB-1955')3);
vehicle_typ(1,
'Toyota', 'MR2', '01-FEB-1955') *
ERROR
at line 2:ORA-22826: cannot construct an instance of a non instantiable type
The following examples add rows to
the cars and motorcycles tables:
INSERT
INTO cars VALUES (car_typ(1, 'Toyota', 'MR2', 'Y'));
INSERT
INTO motorcycles VALUES (motorcycle_typ(1, 'Harley-Davidson', 'V-Rod', 'N'));
The final example queries the cars
and motorcycles tables:
SELECT
*FROM cars;
ID
MAKE MODEL C
----------
--------------- --------------- -
1 Toyota MR2 Y
SELECT
*FROM motorcycles;
ID
MAKE MODEL S
----------
--------------- --------------- -
1 Harley-Davidson V-Rod N
User-Defined
Constructors
Like other object-oriented
languages, you can define your own constructors to initialize the attributes of
an object. You can define your own constructor to do things like
programmatically default one or more attributes of an object.
The following example creates a type
named person_typ2 that declares two constructor method signatures:
CREATE
OR REPLACE TYPE person_typ2 AS OBJECT (id NUMBER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
dob DATE,
phone VARCHAR2(12),
CONSTRUCTOR FUNCTION person_typ2(p_id NUMBER,
p_first_name VARCHAR2,
p_last_name VARCHAR2) RETURN SELF AS RESULT,
CONSTRUCTOR FUNCTION person_typ2(p_id NUMBER,
p_first_name VARCHAR2,
p_last_name VARCHAR2,
p_dob DATE,
p_phone VARCHAR2) RETURN SELF AS RESULT);
/
Notice the following about the
constructors:
- The keywords CONSTRUCTOR FUNCTION are used to identify constructors.
- The keywords RETURN SELF AS RESULT indicate an object of the same type as person_typ2 is returned by each constructor.
- The first constructor accepts three parameters, and the second constructor accepts five parameters.
The constructor signatures don t
contain the actual code bodies for the constructors; the code is contained in
the following statement:
CREATE
OR REPLACE TYPE BODY person_typ2 AS
CONSTRUCTOR FUNCTION person_typ2(p_id NUMBER,
p_first_name VARCHAR2,
p_last_name VARCHAR2) RETURN SELF AS RESULT IS
BEGIN
SELF.id := p_id;
SELF.first_name := p_first_name;
SELF.last_name := p_last_name;
SELF.dob := SYSDATE;
SELF.phone := '555-1212';
RETURN;
END;
CONSTRUCTOR FUNCTION person_typ2(p_id NUMBER,
p_first_name VARCHAR2,
p_last_name VARCHAR2,
p_dob DATE,
p_phone VARCHAR2) RETURN SELF AS RESULT IS
BEGIN
SELF.id := p_id;
SELF.first_name := p_first_name;
SELF.last_name := p_last_name;
SELF.dob := p_dob;
SELF.phone := p_phone;
RETURN;
END;
END;
/
Notice the following:
- The constructors use SELF to set the attributes of the object. For example, SELF.id := p_id sets the id attribute of the object to the p_id parameter that is passed to the constructor.
- The first constructor sets the dob attribute to the datetime returned by the SYSDATE() function, and sets phone to 555-1212. The second constructor simply sets the attributes to the parameters passed to the constructor.
The following example describes
person_typ2 :
DESC person_typ2;
Name Null? Type
-----------------------------------------
-------- -------------------
ID NUMBER
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
DOB DATE
PHONE VARCHAR2(12)
METHOD
------
FINAL MEMBER FUNCTION PERSON_TYP2 RETURNS
PERSON_TYP2
Argument Name Type In/Out Default?
------------------------------
----------------------- ------ --------
P_ID NUMBER IN
P_FIRST_NAME VARCHAR2 IN
P_LAST_NAME VARCHAR2 IN
METHOD
------
FINAL MEMBER FUNCTION PERSON_TYP2 RETURNS
PERSON_TYP2
Argument Name Type In/Out Default?
------------------------------
----------------------- ------ --------
P_ID NUMBER IN
P_FIRST_NAME VARCHAR2 IN
P_LAST_NAME VARCHAR2 IN
P_DOB DATE IN
P_PHONE VARCHAR2 IN
You can then create a table of type
person_typ2; for example:
CREATE
TABLE object_customers2 OF person_typ2;
Summary
In this chapter, you learned that
- The Oracle database allows you to create object types. An object type is like a class in Java.
- An object type may contain attributes and methods .
- A simple example of an object type would be a type that models a product. This object type could contain attributes for the product s name , description, and price, along with a method that gets the sell-by date of the product.
- You create an object type using the CREATE TYPE statement.
- You can use an object type to define a column in a table, and the column is known as a column object.
- You can also use an object type to define an entire table, and the table is known as an object table.
- You use object references to model relationships between object tables, rather than foreign keys. Object references are defined using the REF type and are basically pointers to objects in an object table. Each object in an object table has a unique object identifier (OID) that you can then store in a REF column.
- With the release of the Oracle9 i database, you can use object type inheritance. This allows you to define hierarchies of database types.
- You can mark an object type as NOT INSTANTIABLE , which prevents objects of that type from being created. You might want to mark an object type as NOT INSTANTIABLE when you want to use that type as a supertype .
- You can define your own constructors.
In the next chapter, you ll learn
about collections.
కామెంట్లు లేవు:
కామెంట్ను పోస్ట్ చేయండి