Just because you decide to use objects, don't think that your design decisions are over. On the contrary, the fun is just beginning. One of the core design options you must confront (consciously or not) is this: "Just how object-oriented do I want to make the PL/SQL in my applications?" When dealing with persistent data, this question translates (at least partially) into "Where should I allow modifications?" It is this second question that is the topic of this section.
There are four architectures discussed below, but they certainly don't represent all the possibilities.
Permit full use of conventional SELECT, INSERT, UPDATE, and DELETE statements on your persistent objects. Other than using complex datatypes, the objects option will, in this case, look a lot like conventional relational approaches. At this end of the spectrum, you don't even have to define any methods...but you pay a price.
Permit limited use of conventional SQL, but invoke the constructor method in INSERT, and create various UPDATE methods which will be invoked in clauses of UPDATE statements. Use DELETE as above. This is a better way to go, since you can rely at least partially on the core logic you embed in the methods. However, you still rely on application programmers to invoke the methods properly.
Implement all data manipulations via methods, including all DML on persistent object tables (or at least make an attempt to do so). If you come from an object shop, this might be your preferred approach. This approach absolutely commits you to an object bias in your applications. However, it ties the object type to a particular implementation, which might limit reuse.
Design the object methods to avoid references to persistent object tables, instead acting only on the SELF object and on data exchanged via method arguments. Construct PL/SQL "container" packages to manage your persistent object tables (this is similar to what you could do in Oracle7), but code these packages to reuse logic that is localized in the object type definition. When a PL/SQL application needs to manipulate persistent data, it must call the package; when it simply needs to perform an operation on a transient object variable, it will typically invoke a method. Approach 4 has a number of advantages over Approach 2 above; notably, it further increases the likelihood that application programmers will invoke the proper call in their code.
NOTE: Although the crystal ball isn't talking, a fifth approach may make a great deal of sense once Oracle supports inheritance. It might be possible to implement persistent object types as subtypes of the corresponding transient object type. Doing so could potentially provide the benefits of encapsulation and reuse while circumventing difficult schema evolution problems. (That is, subtypes should be capable of specializing behavior of their supertypes so you don't have to rebuild the entire dependency tree every time you make slight modifications in object specifications.)
The examples that follow abandon our friends at the pet shop and move on to looking at documents as objects. We will use only a few attributes; the important thing to watch is how we use Oracle features to allow the manipulation of the object data, while still protecting it.
This scheme, illustrated in Figure 18.1, is characterized by ultimate flexibility. It allows any application to use whatever SQL the programmer feels is necessary to achieve the desired result. It requires little or no coordination among programmers, although they will (you hope) be coding against a shared database design.
CREATE OR REPLACE TYPE Doc_t AS OBJECT ( doc_id INTEGER, name VARCHAR2(512), author VARCHAR2(60), url VARCHAR2(2000), publication_year INTEGER(4) ); CREATE TABLE docs OF Doc_t (PRIMARY KEY (doc_id));
It is certainly possible to go wild with SQL, as if we weren't using objects at all. Various applications might be issuing DML as they see fit.
An Oracle Forms application might be issuing inserts...
INSERT INTO docs VALUES (1001, 'Great Expectations', 'Charles Dickens', 'http://www.literature.org/Works/Charles-Dickens/ great-expectations/', 1861); INSERT INTO docs VALUES(42, 'The Hitchhiker''s Guide to the Galaxy', 'Douglas Adams', 'http://www.bxscience.edu/~grollman/trilogy/ Hitchhikers.Guide.to.the.Galaxy', 1979);
...while an application programmer is working in SQL*Plus...
UPDATE docs SET author = 'Chuck Dickens' WHERE doc_id = 1001; SELECT * FROM docs WHERE author LIKE '%Adams';
...and a Pro*C application is deleting data that's not in the mainframe any more:
DELETE docs WHERE doc_id = 42;
Does this possibly look a lot like what you've been doing for years? Consider for a moment the problems this might cause. First, allowing any application to issue any DML statement under the sun makes it difficult simply to assess the impact of a schema change, not to mention the actual cost and time to implement the change. Second, there is little hope of reusing DML statements since there is no central, controlled superstructure in place to facilitate code sharing. And what happens if you discover "bad data" in the database, which might be the result of a bug in an UPDATE statement in some application? Since DML can get mind-numbingly complex, simply locating buggy SQL can be a frustrating, costly process.
With an undisciplined approach, a database system of any substantial size -- regardless of whether the database is relational or object-relational -- may not survive the weight of its own maintenance.
By adding methods to your objects, you can publicly announce to other programmers that you will maintain a standard, controlled interface to an object, and that if they use the methods you have defined, they and you will be less prone to getting slapped with maintenance work later on. Figure 18.2 illustrates how clients in this second approach invoke their own DML statements, but use method calls in these statements.
Looking at a code sample, the set_author method below can be used in an UPDATE statement:
CREATE OR REPLACE TYPE Doc_t AS OBJECT ( doc_id INTEGER, name VARCHAR2(512), author VARCHAR2(60), url VARCHAR2(2000), publication_year INTEGER(4), -- we return the entire object so methods can be chained if needed MEMBER FUNCTION set_author (new_author VARCHAR2) RETURN Doc_t, PRAGMA RESTRICT_REFERENCES (set_author, RNDS,WNDS,RNPS,WNPS) ); CREATE OR REPLACE TYPE BODY Doc_t AS MEMBER FUNCTION set_author (new_author VARCHAR2) RETURN Doc_t IS the_doc Doc_t := SELF; BEGIN the_doc.author := new_author; RETURN the_doc; END set_author; END;
Application DML might look like the following. Inserts are straightforward enough, using the default constructor:
INSERT INTO docs VALUES (Doc_t(1005, 'The Tempest', 'William Shakespeare', 'http://the-tech.mit.edu/Shakespeare/Comedy/ tempest/thetempest.html', 1611));
Or even (just to illustrate the point):
INSERT INTO docs SELECT VALUE(d) FROM docs d;
Yes, the previous statement duplicates all the objects in the docs table.
In SQL, you can invoke a method in an UPDATE statement in a straightforward fashion. To change all of Shakespeare's documents so that I am the author, the following statement will do the job. Notice the use of the table alias d as the placeholder for the "current" object.
UPDATE docs d SET d = d.set_author('Bill') WHERE d.author = 'William Shakespeare';
Deleting a persistent object in this second approach is just like the first approach:
DELETE docs WHERE doc_id = 1001;
Or in PL/SQL, if we already have a REF in the doc_ref variable, we could delete as follows:
DELETE docs d WHERE REF(d) = doc_ref;
How does Approach 2 insulate applications from change? It's not hard to see that if we later implement a business rule regarding changing authors -- for example, it's illegal to alter the author of anything written prior to 1900 -- we need only recode the method. When dealing with persistent objects, you will rightly point out, this sort of restriction can be coded using database triggers or stored procedures; however, triggers are no help at all if you want to enforce rules on transient objects.
Overall, this second approach, while improved, still leaves much to be desired, since it still relies heavily on programmer discipline to use the object methods in their DML statements.
If you are wedded to object technology, this approach (illustrated in Figure 18.3) may be for you, although it poses some challenges. In the sample code, notice that every DML statement will occur via a procedure call. Remember that DML can only be executed in member procedures because Oracle does not allow functions to modify the database.
Rather than look at the full code of the object specification and body, let's look first at a client executing a single operation: change the name of the author. You would want a very simple call where you provide only the name of the new author. In other words, the call might ideally be invoked something like this:
DECLARE the_doc_ref REF Doc_t; BEGIN SELECT REF(d) INTO the_doc_ref FROM docs d WHERE d.doc_id = 1001; the_doc_ref.set_author('me'); -- hypothetical call; is invalid END;
Here, "set_author" is a proposed method of the Doc_t object. Set_author would know how to update the database object corresponding to the_doc_ref. Whoops! Did you forget? You cannot navigate the database through REF variables. And the_doc_ref is certainly a REF variable, so you can't use it in place of an object.
Let's look at this from the other side now. What could we do in the object definition to make an update work easily?
Here again is the specification, which we won't change (yet):
CREATE OR REPLACE TYPE Doc_t AS OBJECT ( doc_id INTEGER, name VARCHAR2(512), author VARCHAR2(60), url VARCHAR2(2000), publication_year INTEGER(4), MEMBER PROCEDURE set_author (new_author IN VARCHAR2) );
And here is the body, with a new, but so far incomplete, UPDATE statement:
CREATE OR REPLACE TYPE BODY Doc_t AS MEMBER PROCEDURE set_author (new_author IN VARCHAR2) IS BEGIN /* First update the transient object */ SELF.author := new_author; /* Now update the persistent object */ UPDATE docs d SET author = new_author WHERE ??? END; END;
What exactly do we put in the WHERE clause? You could try this:
WHERE d = SELF;
But then you are doing an object comparison that could be problematic if you have defined a MAP member function. Think about it: MAP only provides some scalar mapping of some object characteristics, and it is possible to have different objects with the same MAPped value!
So what you might think of next is using a REF to the object:
WHERE REF(d) = REF(SELF);
The problem with this WHERE clause is that you can't get the REF of SELF, since SELF is not a table alias; logically, it also fails because it might not exist as a referenceable object in the database. (It kind of makes sense, but you don't have to like it.)
Now what? What about passing in a REF, something like this:
CREATE OR REPLACE TYPE Doc_t AS OBJECT ( doc_id INTEGER, name VARCHAR2(512), author VARCHAR2(60), url VARCHAR2(2000), publication_year INTEGER(4), MEMBER PROCEDURE set_author (new_author IN VARCHAR2, doc_ref REF Doc_t) ); CREATE OR REPLACE TYPE BODY Doc_t AS MEMBER PROCEDURE set_author (new_author IN VARCHAR2, doc_ref REF Doc_t) IS BEGIN /* First update the transient object */ SELF.author := new_author; /* Now update the persistent object */ UPDATE docs d SET author = new_author WHERE REF(d) = doc_ref; END; END;
There! That not only compiles, it does what we want. Here's an example of using the member procedure in PL/SQL to update the persistent object:
DECLARE my_doc Doc_t; my_doc_ref REF Doc_t; BEGIN SELECT VALUE(d), REF(d) INTO my_doc, my_doc_ref FROM docs d WHERE doc_id = 1001; my_doc.set_author('Yogi Bear', my_doc_ref); END;
It's a little ugly with the extra REF argument in there, but there is no easy way in Oracle8.0 to get to a more "pure" object approach.
Are we finished? What about the possibility that we want to update only the transient object, rather than the persistent object? For example, we might want to set_author from within a SQL SELECT statement. For that, we can simply define a similar member function, which unfortunately cannot have the overloaded same name as the member procedure, since PRAGMA RESTRICT_REFERENCES will attempt to take effect on all modules of the supplied name.
CREATE OR REPLACE TYPE Doc_t AS OBJECT ( doc_id INTEGER, name VARCHAR2(512), author VARCHAR2(60), url VARCHAR2(2000), publication_year INTEGER(4), MEMBER FUNCTION set_author (new_author IN VARCHAR2) RETURN Doc_t, PRAGMA RESTRICT_REFERENCES (set_author, RNDS,WNDS,RNPS,WNPS), /* We are changing the name of the procedure below to have "_p" || on the end, as a mnemonic for Persistent (or Procedure) */ MEMBER PROCEDURE set_author_p (new_author IN VARCHAR2, doc_ref REF Doc_t) ); CREATE OR REPLACE TYPE BODY Doc_t AS MEMBER FUNCTION set_author (new_author IN VARCHAR2) RETURN Doc_t IS l_doc Doc_t := SELF; BEGIN l_doc.author := new_author; RETURN l_doc; END; MEMBER PROCEDURE set_author_p (new_author IN VARCHAR2, doc_ref REF Doc_t) IS BEGIN SELF := SELF.set_author (new_author); UPDATE docs d SET author = new_author WHERE REF(d) = doc_ref; END; END;
This code will definitely work. Because of the RESTRICT_REFERENCES, you can use the function in SQL:
SELECT d.set_author('me') FROM docs d;
...and you can use the function from PL/SQL:
DECLARE my_doc Doc_t := Doc_t(1,'OPP','Feuerstein',null,null); BEGIN my_doc := my_doc.set_author('Steven Feuerstein'); END;
...and you can use the procedure in PL/SQL, as indicated earlier.
Think for a minute about the possibility that you code a method that changes more than one attribute at a time. You could get a bit more elegant by updating the entire object in the UPDATE statement, rather than using individual assignments in the SET clause.
MEMBER PROCEDURE set_name_and_author (new_name IN VARCHAR2, new_author IN VARCHAR2, doc_ref IN REF Doc_t) IS BEGIN SELF.name := new_name; SELF.author := new_author; UPDATE docs d SET d = SELF WHERE REF(d) = doc_ref; END;
WARNING: While this code is syntactically correct, it fails in Oracle8.0.3 due to a PL/SQL bug. At press time, summer of 1997, we don't know exactly when this will be fixed -- soon, we hope!
The other "update" methods (for example, set_name, set_url, etc.) in our all-object approach would follow the general patterns we've established so far.
There are two more cases that you'll want to cover: persistent object construction and destruction. These are relatively simple INSERT and DELETE statements, as shown below:
CREATE OR REPLACE TYPE Doc_t AS OBJECT ( doc_id INTEGER, name VARCHAR2(512), author VARCHAR2(60), url VARCHAR2(2000), publication_year INTEGER(4), MEMBER PROCEDURE construct_and_save (the_doc_id IN INTEGER, the_name IN VARCHAR2, the_author IN VARCHAR2, the_url IN VARCHAR2, the_year IN INTEGER, doc_ref_out OUT REF Doc_t), MEMBER PROCEDURE save (doc_ref_out OUT REF Doc_t), MEMBER PROCEDURE destroy (doc_ref IN REF Doc_t), MEMBER FUNCTION set_author (new_author IN VARCHAR2) RETURN Doc_t, PRAGMA RESTRICT_REFERENCES (set_author, RNDS,WNDS,RNPS,WNPS), MEMBER PROCEDURE set_author_p (new_author IN VARCHAR2, doc_ref REF Doc_t) ); CREATE OR REPLACE TYPE BODY Doc_t AS MEMBER FUNCTION set_author (new_author IN VARCHAR2) ...as above MEMBER PROCEDURE set_author_p (new_author IN VARCHAR2, ...as above MEMBER PROCEDURE construct_and_save (the_doc_id IN INTEGER, the_name IN VARCHAR2, the_author IN VARCHAR2, the_url IN VARCHAR2, the_year IN INTEGER, doc_ref_out OUT REF Doc_t) IS /* Encapsulates the constructor and the INSERT. Note that the || values passed in via arguments need not match the initial || attributes of SELF. In fact, SELF can be atomically null || when you first invoke this method. */ BEGIN SELF := Doc_t(the_doc_id, the_name, the_author, the_url, the_year); SELF.save(doc_ref_out); END; MEMBER PROCEDURE save (doc_ref_out OUT REF Doc_t) IS /* Use this one for the INSERT if you have already invoked || the constructor and have a doc object in a variable. || The RETURNING clause returns the object's OID into the || host variable doc_ref. */ BEGIN INSERT INTO docs d VALUES (SELF) RETURNING REF(d) INTO doc_ref_out; END; MEMBER PROCEDURE destroy (doc_ref IN REF Doc_t) IS /* Note that we are passing in a REF that might not match || whatever might be in SELF. It is up to the calling module to || be aware of this idiosyncrasy. */ BEGIN SELF := NULL; DELETE docs d WHERE REF(d) = doc_ref; END; END;
When you store an object using the "save" procedure above, you will have already created an in-memory version of the object using the default constructor or other means. Contrast this with "construct_and_save."
As noted in the comments, the RETURNING clause of the INSERT statement returns the REF identifier of the newly created object into a program variable. Since this is a very useful value, we return it to the calling program using an OUT parameter. (RETURNING is a new feature of SQL in Oracle8 that allows us to retrieve data on INSERT, UPDATE, and DELETE statements. It's useful not only for returning a REF but also for returning other generated column values such as those computed by database triggers.)
When you "destroy" an object in a program, you simply assign NULL to the object. This returns its state to "atomically null," which is the nonvalue the object would have if you had never constructed it. Oracle does not supply any sort of "destructor" method.
Let's step back and think about the "method-only" approach. Making the object type responsible for its own persistence seems like a good idea. But this code assumes that your Doc_t object type will be intimately tied to the "docs" table. Is that a reasonable assumption? There is nothing in Oracle that forces you to implement the Doc_t object type only one time. If you ever decide to reuse the object types, which you probably will, our "method-only" approach could run into problems.
Maybe you could define an argument to indicate which table(s) needs to receive the data change. You could wind up with messy IF tests and multiple UPDATE statements or, at best, dynamic SQL to apply the change to multiple tables. Worse, you could end up having to drop and rebuild your object tables to accommodate the changes in method specifications (see Section 18.6.3, "Schema Evolution" later on). Very un-object-oriented.
But what is the alternative? Any way you code it, managing the persistent data in multiple object tables defined on the same object type will be more trouble than a single table. You could allow rampant DML statements, which we have already poo-poo'd as the moral equivalent of global variables. Perhaps we could make our code "more object-oriented" if the Oracle objects option allowed inheritance. That way, we might be able to have an object table managed by a child object type. The parent would manage only transient objects, and the children would manage persistent objects.
As we'll see in Approach 4, another method is to rely on the "other" object features of the language that you can find in packages. By using object types for nonpersistent transient data only, you can avoid a number of thorny issues, at the possible expense of clouding your abstraction.
If you typically program with packages, this approach (illustrated in Figure 18.4) will look familiar, and you should feel free to pat yourself on the back. If you don't use packages, you should seriously consider their benefits (see Chapter 16), which can include many of the same advantages of objects.
In this scheme, we eliminate all references to persistent data from the object definition and body. Instead, we write a PL/SQL package that will manage our object tables. By separating the layer that manages persistence (the package) from the layer that manages objects (the object type), we have a closer fit of PL/SQL with Oracle's object model, especially if we intend to reuse object type definitions. In addition, we gain access to REF CURSORs, which allow the encapsulation of complicated SELECT statements.
Let's start with the type definition, with only the single archetypal set_author function illustrated:
CREATE OR REPLACE TYPE Doc_t AS OBJECT ( doc_id INTEGER, name VARCHAR2(512), author VARCHAR2(60), url VARCHAR2(2000), publication_year INTEGER(4), MEMBER FUNCTION set_author (new_author IN VARCHAR2) RETURN Doc_t );
And the body, which might be very familiar by now:
CREATE OR REPLACE TYPE BODY Doc_t AS MEMBER FUNCTION set_author (new_author IN VARCHAR2) RETURN Doc_t IS the_doc Doc_t := SELF; BEGIN the_doc.author := new_author; RETURN the_doc; END set_author; END;
We can create an object table that will hold "appraisal" document objects, as long as they fit the Doc_t definition, and design the package without disturbing the object type:
CREATE TABLE appraisals OF Doc_t (PRIMARY KEY (doc_id));
The specification of our package to manage the persistent appraisal data could go something like this:
CREATE OR REPLACE PACKAGE manage_appraisal AS /* Allow creation of an appraisal document */ PROCEDURE create_one (doc_id_in INTEGER, name_in VARCHAR2, author_in VARCHAR2, url_in VARCHAR2, publication_year_in INTEGER, doc_ref_out OUT REF Doc_t); /* Look up an appraisal based on its document ID */ FUNCTION find_doc_by_id (doc_id_in INTEGER) RETURN Doc_t; /* Look up an appraisal's REF based on its document ID */ FUNCTION find_doc_ref_by_id (doc_id_in INTEGER) RETURN REF Doc_t; /* Overload the update procedures to accommodate any of: || 1) "primary key" style identification || 2) identification by object || 3) identification by object REF */ PROCEDURE set_one_author (doc_id_in IN INTEGER, new_author_in IN VARCHAR2); PROCEDURE set_one_author (doc_in IN Doc_t, new_author_in IN VARCHAR2); PROCEDURE set_one_author (docref_in IN REF Doc_t, new_author_in IN VARCHAR2); /* You could define other "set_one" update procedures here || for other attributes... */ -- don't forget about the deletions! PROCEDURE delete_one (doc_id_in IN INTEGER); PROCEDURE delete_one (doc_in IN Doc_t); PROCEDURE delete_one (docref_in IN REF Doc_t); END;
And the body:
CREATE OR REPLACE PACKAGE BODY manage_appraisal AS PROCEDURE create_one (doc_id_in INTEGER, name_in VARCHAR2, author_in VARCHAR2, url_in VARCHAR2, publication_year_in INTEGER, doc_ref_out OUT REF Doc_t) IS BEGIN INSERT INTO appraisals a VALUES (Doc_t(doc_id_in, name_in, author_in, url_in, publication_year_in)) RETURNING REF(a) INTO doc_ref_out; END create_one; FUNCTION find_doc_by_id (doc_id_in IN INTEGER) RETURN Doc_t IS doc Doc_t; CURSOR doc_cur IS SELECT VALUE(a) FROM appraisals a WHERE doc_id = doc_id_in; BEGIN OPEN doc_cur; FETCH doc_cur INTO doc; CLOSE doc_cur; RETURN doc; END find_doc_by_id; FUNCTION find_doc_ref_by_id (doc_id_in IN INTEGER) RETURN REF Doc_t IS doc_ref REF Doc_t; CURSOR doc_cur IS SELECT REF(a) FROM appraisals a WHERE doc_id = doc_id_in; BEGIN OPEN doc_cur; FETCH doc_cur INTO doc_ref; CLOSE doc_cur; RETURN doc_ref; END find_doc_ref_by_id; /* Unfortunately, the PL/SQL bug mentioned earlier prevents the || next three procedures from compiling. The error message is || "PLS-00382: expression is of wrong type." Until Oracle fixes || this problem, a conventional update statement will do the job, || although it will violate encapsulation and bypass the set_author || method. */ PROCEDURE set_one_author (doc_id_in IN INTEGER, new_author_in IN VARCHAR2) IS BEGIN UPDATE appraisals a SET a = a.set_author(new_author_in) WHERE doc_id = doc_id_in; END set_one_author; PROCEDURE set_one_author (doc_in IN Doc_t, new_author_in IN VARCHAR2) IS BEGIN UPDATE appraisals a SET a = a.set_author(new_author_in) WHERE doc_id = doc_in.doc_id; END set_one_author; PROCEDURE set_one_author (docref_in IN REF Doc_t, new_author_in IN VARCHAR2) IS BEGIN UPDATE appraisals a SET a = a.set_author(new_author_in) WHERE REF(a) = docref_in; END set_one_author; PROCEDURE delete_one (doc_id_in INTEGER) IS BEGIN DELETE appraisals WHERE doc_id = doc_id_in; END delete_one; PROCEDURE delete_one (doc_in Doc_t) IS BEGIN DELETE appraisals WHERE doc_id = doc_in.doc_id; END delete_one; PROCEDURE delete_one (docref_in REF Doc_t) IS BEGIN DELETE appraisals a WHERE REF(a) = docref_in; END delete_one; END manage_appraisal;
An underlying assumption is that you want genuine encapsulation of the persistent data, and that you will grant users only EXECUTE privileges on the package and on the object type. You could further encapsulate the underlying data by declaring public REF CURSOR variables and creating associated procedures that will use them to open and fetch. See Chapter 6, Database Interaction and Cursors, for a discussion of how to do this.
One of the challenges of encapsulating DML in packages is that the Oracle application development tools such as Developer/2000 assume that they have full use of SQL to navigate the database and manipulate tables. Oracle Forms' default block functionality relies heavily on this assumption.
Consistently using packages rather than Forms' default processing could force a lot of hand-coding of transactional triggers: on-insert, on-update, and on-delete. You will also need to hand-code your own on-lock trigger. (We haven't explicitly discussed locking in this chapter, but to support the Oracle Forms' transaction model, you would want to add methods or procedures to lock any persistent data that the user begins to change or delete.)
Using a PL/SQL container could be buying your future maintainability at the cost of immediate development productivity -- a tough trade-off!
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.