Relative Pets, a combination pet store and animal hospital, is developing a database to help track their clientele. They tend to take an animal-centric view of the world, so their main entity is the Pet. They're also working on a web site where they can post photos of some critters that have been put up for adoption. Being on the foreleg of technology, they of course want to use an object-relational database like Oracle8.
We'll start simple. In this example, all of the attributes are primitive datatypes rather than objects or collections. The photo attribute is of the Oracle8 datatype BFILE, which is simply a reference to a file stored in the operating system (see Chapter 4, Variables and Program Data ). We'll follow the convention that object type names start with a capital letter and end in "_t."
CREATE OR REPLACE TYPE Pet_t AS OBJECT ( tag_no INTEGER, -- pet license number name VARCHAR2(60), -- name of pet animal_type VARCHAR2(30), -- dog, cat, ferret,... sex VARCHAR2(1), -- M/F photo BFILE, -- JPG image in O/S file MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN Pet_t, MEMBER FUNCTION set_photo (file_location IN VARCHAR2) RETURN Pet_t, MEMBER PROCEDURE print_me ); /
This type contains scalar attributes and three methods. Note that the member functions return a value of type Pet_t. This affords flexibility in how the methods can be used later on.
CREATE OR REPLACE TYPE BODY Pet_t AS /* set_tag_no: Starting with the attributes of the "current" || pet object, return a new pet object with a new tag number. */ MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN Pet_t IS the_pet Pet_t := SELF; -- initialize to current object BEGIN the_pet.tag_no := new_tag_no; RETURN the_pet; END; /* set_photo: Return new pet object with new photo filename. || Use BFILENAME, a built-in Oracle function that accepts || a directory alias and filename and returns a value of || type BFILE. */ MEMBER FUNCTION set_photo (file_location IN VARCHAR2) RETURN Pet_t IS the_pet Pet_t := SELF; BEGIN the_pet.photo := BFILENAME('WEB_PHOTOS', file_location); RETURN the_pet; END; /* Do a simple listing of this object. Don't deal with the || contents of the photo, just its name. */ MEMBER PROCEDURE print_me IS directory_alias VARCHAR2(60); file_name VARCHAR2(60); BEGIN DBMS_LOB.FILEGETNAME(SELF.photo, directory_alias, file_name); DBMS_OUTPUT.PUT_LINE('Tag No : ' || tag_no); DBMS_OUTPUT.PUT_LINE('Name : ' || name); DBMS_OUTPUT.PUT_LINE('Type : ' || animal_type); DBMS_OUTPUT.PUT_LINE('Sex : ' || sex); DBMS_OUTPUT.PUT_LINE('Photo : ' || file_name); END; END;
This code introduces a couple of syntactic devices that we'll cover in detail later, namely the use of the "default constructor" to create an object of the designated type, and the use of the SELF parameter as a means of referring to the object and its component attributes. Notice that this code makes no mention of any tables in the database.
We haven't created a comprehensive implementation of this type yet, since we haven't coded methods for setting all of the attributes. Eventually, there could be quite a large number of new attributes, each of which could be managed by at least one method. Yes, an object approach can get a bit code-happy if you look at how the objects are implemented "under the covers." You may be asking yourself, why use these trivial procedures for setting attribute values? Why not expose the attributes and let application programs just set the value?
Although exceptions exist, a convention of the object approach is that all operations on object data must be implemented by methods. As soon as you allow the use of a back door to manipulate data, you have violated encapsulation, and you are asking for the same kind of trouble you'd get with excessive use of global variables: code that breaks easily, with low confidence that modifications will be free of errors, and, in general, high maintenance costs (see the discussion of global variables in Chapter 16, Packages ). It's true enough that comprehensive object definitions can be code intensive; the payback comes later on, during maintenance and future development, when well implemented objects are less likely to break and more likely to facilitate reuse and resilience.
To create an object based on a type -- that is, to instantiate it -- you can use a default constructor , a function that Oracle makes available as soon as you create a type. The constructor has the same name as the object type, and accepts each attribute as an argument, in the order in which attributes are declared in the object type definition. That is, Pet_t is both the name of an object type and the name of the corresponding default constructor:
DECLARE the_pet Pet_t; BEGIN -- Instantiate the_pet using the default constructor the_pet := Pet_t(104525, 'Mambo', 'DOG', 'M', BFILENAME('WEB_PHOTOS','Mambo.jpg')); -- Invoke a method to change the tag number of the_pet the_pet := the_pet.set_tag_no(104552); -- Print a listing about the_pet the_pet.print_me(); END;
Notice the syntax for invoking the set_tag_no and print_me methods on the_pet. This object.method syntax is not uncommon in other OO languages. It's different from package.procedure_or_function syntax, because what you find on the left side of the dot is an object instance. That is, a correct call is:
the_pet .set_tag_no(104552) -- the_pet is a variable of type Pet_t
Pet_t .set_tag_no(104552) -- invalid; what pet's no. are we changing?
You have to tell Oracle which particular object instance you want to use.
If you prefer, you can use a method invocation that is almost indistinguishable from a conventional packaged function or procedure call, with either named or positional notation. The following calls show how you can simply supply the object instance as the SELF parameter (more about SELF later):
CREATE TABLE pets OF Pet_t (PRIMARY KEY (tag_no));
This creates a table with columns for the attributes. The table also has other special characteristics, such as a hidden object identifier column, on which Oracle automatically creates a unique index. As implied by the PRIMARY KEY clause, it also creates a relational primary key constraint -- and therefore an additional unique index -- on the tag_no column. (You cannot, by the way, use a nonscalar column such as a REF or a collection as the primary key.)
Proving to ourselves that our table looks right, we can do a "describe" on it as follows:
SQL> desc pets Name Null? Type ------------------------------- -------- ---- TAG_NO NUMBER(38) NAME VARCHAR2(60) ANIMAL_TYPE VARCHAR2(30) SEX VARCHAR2(1) PHOTO BINARY FILE LOB
and we can insert a record object into the table:
INSERT INTO pets VALUES(Pet_t(1044526,'Socks','CAT', 'M', BFILENAME('WEB_PHOTOS', 'socks.jpg')));
The INSERT statement uses the constructor in the VALUES clause.[ 10 ] Now the object can continue its life more or less permanently in a database table. It can get retrieved by PL/SQL programs or referenced by other tables as needed. However, up to this point, we have not defined any methods that will manage the persistent data. We'll see later how to design these additional methods you have to invoke when programs need to change the state of a persistent object.
As a dues-paying member of the SPCA, Relative Pets keeps a vaccination history on each animal. To introduce the rich data structures available to objects, we can create a nested table datatype to hold information about vaccinations. Each pet can have many vaccinations.
CREATE TYPE Vaccination_list_t AS TABLE OF VARCHAR2(30);
(See Chapter 19, Nested Tables and VARRAYs , for more information about this new collection type.) Since we are going to track the owners of the pets, we could benefit from having a "person" object. For now, we won't give this object type many attributes, but we will implement the RESTRICT_REFERENCES pragma so that we can use its "full_name" function in DML (see Chapter 17, Calling PL/SQL Functions in SQL , for a full discussion of this pragma):
CREATE TYPE Person_t AS OBJECT ( person_id INTEGER, last_name VARCHAR2(60), first_name VARCHAR2(30), MEMBER FUNCTION full_name RETURN VARCHAR2, PRAGMA RESTRICT_REFERENCES (full_name, RNDS, WNDS, RNPS, WNPS) );
Here is a simple body that implements the full_name method:
CREATE TYPE BODY Person_t AS MEMBER FUNCTION full_name RETURN VARCHAR2 IS BEGIN RETURN first_name || ' ' || last_name; END; END;
Finally, we can put a vaccination list and an owner in our "pet" object type:
CREATE OR REPLACE TYPE Pet_t AS OBJECT ( tag_no INTEGER, -- pet license number name VARCHAR2(60), -- name of pet animal_type VARCHAR2(30), -- dog, cat, ferret,... sex VARCHAR2(1), -- M/F photo BFILE, -- JPG image in O/S file vaccinations Vaccination_list_t, owner Person_t, MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN Pet_t, MEMBER FUNCTION set_photo (file_location IN VARCHAR2) RETURN Pet_t, MEMBER PROCEDURE print_me );
Although not illustrated here, after making this change, we might want to add new member functions in the type specification and body.
In this example, we are setting ourselves up to use a nested "person" object within the pet object. This is not necessarily a good idea, since the enclosed objects are not sharable by anything else. That is, if one person owned many pets, the information about the owner would have to be copied into each pet object. We're passing up an opportunity to control data redundancy, which is one of the reasons we are using a such a wonderful database system in the first place. Not to worry: we can use the REF keyword to tell Oracle that we want to store only a pointer or reference to another (persistent) object. That is, in our final version:
CREATE OR REPLACE TYPE Pet_t AS OBJECT ( tag_no INTEGER, name VARCHAR2(60), animal_type VARCHAR2(30), sex VARCHAR2(1), photo BFILE, vaccinations vaccination_list_t, owner_ref REF Person_t, MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER) RETURN Pet_t, MEMBER FUNCTION set_photo (file_location VARCHAR2) RETURN Pet_t, MEMBER PROCEDURE print_me, MEMBER PROCEDURE vaccinate (vaccination VARCHAR2, on_date DATE), PRAGMA RESTRICT_REFERENCES (set_tag_no, RNDS, WNDS, RNPS, WNPS), PRAGMA RESTRICT_REFERENCES (set_photo, RNDS, WNDS, RNPS, WNPS) );
we use REF Person_t to indicate that this attribute will store a pointer to an object rather than to its contents.
And we can build the tables:
CREATE TABLE persons OF Person_t (PRIMARY KEY (person_id)); CREATE TABLE pets OF Pet_t (PRIMARY KEY (tag_no)) NESTED TABLE vaccinations STORE AS pet_vaccinations_tab;
Using this separate persons table and the REF attribute will allow the existence of people outside the context of their pets (something the pet-obsessed may not envision, but probably a good idea from a design point of view). In this context, REF is called a type modifier .
Does a REF sound a lot like a foreign key? While there are important differences between REFs and foreign keys (see Table 18.2 ), Oracle actually claims that REFs, are "more reliable and persistent" than foreign keys -- probably because REFs do not refer to user-changeable values, but rather to invisible internal values.
In fact, the problem with REFs is that they are too persistent. Oracle currently allows you to delete an object that is the target of a REF without deleting the reference to it. They even dignify this state with a name: a dangling REF . This is roughly equivalent to what would happen if you delete a department record without changing the records of employees in that department. There is no declarative way to prevent dangling REFs, but it should not be too challenging to do so by implementing pre-delete triggers on the table that contains the "parent" objects.[ 11 ] To make life somewhat easier, Oracle provides a predicate, IS DANGLING, to test for this condition:
UPDATE pets SET owner_ref = NULL WHERE owner_ref IS DANGLING ;
Oracle has a special syntax for retrieving and modifying data in both SQL and PL/SQL using the REF operator; they also provide a DEREF operator (can you guess why?). We'll look at those operators a bit later.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.