It was the philosopher George Santayana who stated, “Those who cannot remember the past are condemned to repeat it.” Philosophy is an interesting pursuit from my point of view because it looks at very high-level abstract patterns of behavior. Patterns are repeated data or behavior of a simple or complex nature.
When we build an application, we attempt to model the workflow of the real world in order to automate or organize the workflow to improve how quickly or accurately we can perform its associated work. However, modeling is not only representing what data is used, but also recognizing the patterns of usage: behavior. Restated simply, we attempt to model the real world, and those who get modeling right profit from it the most.
As business analysts and computer scientists, we have come a long way. Relational database technology has freed us from the tyranny of the high priests of database administration, so we are all capable of storing and retrieving data in sets of highly organized, two-dimensional tables. But, up to this point, we store only the data we use, for the most part ignoring the behavior.
As an analyst, I know that the accuracy of a real-world model depends on its use of the following:
- Current data: Data (words, numbers, and dates) about what happened last. I say “last” because usually, by the time you record the information, it’s data about what just happened, not what’s about to happen.
- Hierarchical data: Data about the relationships of like datum. For example, in a business organization, a department may belong to a subdivision, which may in turn belong to a division, which may in turn belong to a particular operating company; these are all levels of organization that have a hierarchical relationship to each other.
- Historical data: Multiple occurrences of “current” data; that is, data about what has happened over time, not just the last occurrence of behavior. Yes, data is about behavior.
- Current behavior: Methods (functions or procedures) used to accomplish the last act of work. Once again, I say “last” because by the time the act of work is recorded, these are the methods just used, not what will be used in the future.
- Hierarchical behavior: Methods used with hierarchical data.
- Historical behavior: Multiple occurrences of “current” methods; that is, methods used to accomplish acts of work at a particular point in time.
So I submit that in order for you to have an accurate model of the real world, you need to employ all six facets of information in your model. Is that what we do today? For the most part, no.
Today, simple applications store current data in a database and store current methods (behavior) in a presentation layer’s codebase. When I say presentation layer, I mean the software you use to enter data into the application (data entry) or to view data from an application (reporting).
More sophisticated applications may store hierarchical data and try to separate the code (program logic) used to present information from the rules of work, or so-called business (busyness) rules. Then they have one database and two codebases: a database for the persistence of data, a codebase for the “current” business rules or behavior, and a second codebase for presenting or interfacing with a human role player. When business rules are changed in the mid-tier codebase, the old rules are lost forever.
Rarely do applications store the behavior that belongs to an entity with the entity. And even more rarely is the behavior that is stored with an entity temporal in nature; that is, sensitive to at what time the behavior takes place. Yet, that’s how the real world is. No wonder business people have problems with our software solutions. We’re not modeling the real world as it is!
To make matters worse, there’s a disturbing trend toward the presentation layer defining the model. Mapping tools like Hibernate (java) or Django (Python) will create database entities for you. That’s very convenient. However, what may be a convenient way to pass current data for data entry is not necessarily an accurate model of the real world.
Often, using the presentation layer to build the underlying real-world model leads to denormalized data, which leads to fat rows in a database, which because of physics, leads to poor performance. The mantra behind tools like Enterprise JavaBeans (EJB), Hibernate, Django and so on is database independence—as if to suggest that all databases are created equal, which they certainly are not.
Oracle is dominant among its peers because it is superior. It’s that simple. If you’re not going to use Oracle, I suggest checking out PostgreSQL, which is a high-quality, object-relational, open source alternative, but it’s not Oracle by any stretch of imagination.
We’ve had the capability of modeling the real world accurately in object-relational databases for some time now, so why don’t we? The following are my answers to that question:
- “Lex I: Corpus omne perseverare in statu suo quiescendi vel movendi uniformiter in directum, nisi quatenus a viribus impressis cogitur statum illum mutare.”: Newton’s first law translated for our purposes: everyone uses relational databases, so everyone will continue to use relational databases unless there is a compelling enough reason to move in a different direction.
- Ignorance, not stupidity (business community): The level of awareness that the real world is a combination of fact and action, or data and methods, and the value of that information is not commonly understood by business people. Yet, they are the ones who drive the need for applications. If the business community doesn’t understand its own work problems, then no information technology department within that community will ever understand the associated work problems either because the business community defines applications of business.
- Ignorance, not stupidity (technical community): The level of awareness that technology developers have currently attained does not commonly include object-relational technology. Heck, most techies I talk to don’t even know what object-relational means. You’ll hear more evidence about this in this chapter.
In order for you to understand and communicate these newer ideas (my answers), you need to come up to speed on some technology jargon. Let’s start with object orientation.
Object Orientation
What’s the big deal about object orientation anyway? First and foremost, it’s about modeling the real world. Here are some examples:
- Rather than being concerned about a particular process, the emphasis is on independent entities that can actually perform the process.
- Rather than write one large procedure that does everything, and would also need to be tossed if the process in question changed, it’s about assigning the appropriate parts of a procedure to the various role players in the procedure.
- Instead of having one large program, it’s about modularity—having multiple, independent, and reusable components that act appropriately together to complete an act of work.
So it’s about teamwork.
Key Tenets
Object orientation has three key tenets:
- Encapsulation: This refers to packaging related data and methods together. Object orientation is the natural way to think about things; natural, as in nature. It’s the way nature does it. Nature creates things that have both attributes and behavior. For example, I don’t think about what I’d like to eat for dinner, and then you go eat it for me. It doesn’t work that way. My thoughts about food affect me, not you. We are separate entities. All my attributes and behaviors are encapsulated in me. All your attributes and behaviors are encapsulated in you. If someone tells each of us to do something, odds are, we’ll do it differently, or may not do it at all. How’s that for modularity?
- Inheritance: This is the ability to reuse the data and methods of a more abstract type. If we continue on about the idea of asking each of us to perform a particular task, we’ll most likely do it differently because we inherited different genetic attributes from our parents, and they taught us different behaviors, too. So we inherited different attributes and behaviors from our parents, upon which we will add new ones, or override what our parents taught us and do it our own way.
- Polymorphism: This refers to hiding different implementations behind a common interface. Polymorphism is the use of the same name for a particular behavior, yet knowing that each object, or person in our case, may perform the behavior differently. If I’m asked to clean the floor, I’ll get out the vacuum cleaner, and then follow up by washing the floor with a bucket of clear water, while you may hire someone to do the job. All the same, the floor gets cleaned, and that’s the behavior that was requested.
Using these three tenets, you can naturally model both the attributes and behavior, or data and methods, for any real-world entity. Let’s talk more about reuse.
Reuse
What is reuse anyway? I consider it reuse any time you can abstract multiple lines of code into a larger block of code that will then be called by more than one actor. I also think there are multiple levels of reuse. Let’s start with the smallest unit of reuse: method-level reuse.
I always tell programmers who I work with that if they must code the same lines of code more than once to accomplish the same task, then they need to create a method. That’s what I call method-level reuse.
The trick is where to put the method. That’s an easy question to answer for an object-oriented programmer: put the method with the entity that’s executing the behavior! That’s what object-orientation is all about.
Next up is what I call component-level reuse.
Component-Level Reuse
Component-level reuse is when you can reuse a particular class of an entity, or in SQL jargon, a TYPE. You can reuse a class in two ways: through direct application (composition) or inheritance. Direct application would be where you create a TYPE called PERSON, and then create a table based on that type, called PERSONS. In this case, you’ll have used the TYPE PERSON directly.
On the other hand, you could create a sub-TYPE called WORKER based on TYPE PERSON. Now TYPE WORKER would inherit all the data attributes and methods from its parent PERSON, possibly adding some of its own attributes and methods. This would be a case where you reused component PERSON by using inheritance.
Finally, if you define these TYPEs, and then reuse them in more than one application, that’s also an example of component reuse, and an example of reuse at the most intriguing level. For example, if a human resource information system (HRIS) uses the TYPE PERSON in its TYPE hierarchy for TYPE EMPLOYEE, and then a safety application on the same database uses PERSON in its TYPE hierarchy for TYPE WORKER, then both applications can TREAT their sub-TYPEs as a PERSON in order to share (or reuse) related information.
The last example sounds like it could also be called application-level reuse.
You can also design an entire set of applications such that they all use the same underlying abstract TYPEs. This allows each application to share a common set of applications that act as infrastructure for more complex applications. The use of common TYPEs in the inheritance hierarchy of sub-TYPEs would allow the common use of the same tables for all related applications. Now that’s what I call reuse!
Object-oriented programmers have been building service-oriented applications for ages. That’s the entire nature of object-oriented programming. You build independent components that provide supporting services through their method calls. You test them, and then you don’t have to recode the same service ever again.
Even if you continue to build relational instead of object-relational databases, you can still provide service-oriented routines by properly associating your methods with your data. That’s what I’ve been having you do up to this point. I’ve asked you to create what I called a “table package” for each table. Each “table package” contains a set of methods that logically belong to the data that is stored in the associated table.
For example, I named the relational Worker table WORKERS. Then I asked you to create a package called WORKER that contained a method for allocating a new primary key value. I’ve been using this pseudo-object orientation since PL/SQL became available, and it’s a tactic that has worked well. Every programmer I’ve worked with has quickly come up to speed on the technique, and then reused the methods in the table package over and over again to accomplish the same logical task consistently every time.
In addition, although applications have been written in multiple programming languages and tools, all of them call the same “table package” methods to perform a particular service. Once again, even though different presentation layers are coded in different languages by different programmers, the same consistent behavior is seen across all presentations for all services—and that is reuse of the best kind.
Do these varying presentation layers recode the same behavior in varying languages? No. They all use the same code stored with the data. That’s object orientation and service orientation in use. So how do you get there from here?
A Roadmap to Reality
So how do you get to using object-relational technology from relational technology? It’s not as hard as you might think. Just like the function of life, f(l), you’ll get out whatever you put in to it. You can fully implement object-oriented technology in four progressive steps.
- Create table packages.
- Create user-defined types.
- Create object views.
- Create object tables.
Yes, you can implement four different levels of object orientation in your database, and based on what you implement, gain a level of consistency and reusability equal to your level of implementation.
The first step is to start thinking in an object-oriented fashion, or as I call it, thinking naturally. This means organizing your data and methods in a related fashion, as I had you do earlier with table packages. I always name tables with a plural name, and then name their related packages with a singular name, so the package names are the singular of the table names. You can use whatever convention you want. By doing so, you should at least be able to employ encapsulation and polymorphism.
You’ll be employing encapsulation by gathering behavior and hiding it behind a public method call specification. Create a table package for every table in your database that has the same SQL statement executed on it more than once. Create a method for such a SQL statement, so the same behavior can be shared by multiple program units. Also, add any other related methods that perform calculations, concatenations, or locale-specific behavior.
Next, look at the bigger picture. Notice the patterns of use of your table package methods, and use the same names for a particular pattern of use across all table packages.
For example, I use a FUNCTION named get_id() in every table package where I need a method that allocates a new primary key sequence value. Then, for a code table like WORKER_TYPES, if I need to get an ID value for a new entry, I call WORKER_TYPE.get_id(). Similarly, getting an ID value for a new WORKERS table entry will be a different package name, but the same method name: WORKER.get_id().
That’s polymorphism in action.
The second step is to create a new object type, a user-defined type, that encapsulates both data attributes and method call specifications in a permanent new “data” or object type in the database.
Gather the list of column names you use in the relational table, and then collect the methods you specify in each table’s package specification. Use this information along with the keyword TYPE to create a new object type in the database.
Unlike the table’s associated package specification, where all methods are essentially STATIC (always available), you’ll need to decide whether methods will operate on an instance of an object, a MEMBER method, or will be available through the object’s TYPE definition, a STATIC method. In addition, you may want to add your own CONSTRUCTOR method(s) in order to make it easier to create a new object instance. And you may also want to define a MAP MEMBER method to specify how objects are sorted.
When you create user-defined types, you have the opportunity to use all three facets of object orientation. As with table packages, you’ll be employing encapsulation and polymorphism. But you may also be able to leverage inheritance, so keep on the lookout for higher-level patterns between user-defined types. If you see a high-level pattern, consider defining a super-TYPE, and then inheriting from that super-TYPE for similar sub-TYPEs.
If you stop at this level of implementation, you’ll still be able to use data from relational tables as objects by TREATing the relational data as objects on the fly in your SQL statements. But, if you get this far, I would at least do step 3.
Step 3: Object Views
The third step involves creating object views for your relational tables. An object view presents the underlying relational table data as a table of the newly created user-defined object type.
If you use more than one table in an object view, you can write INSTEAD OF triggers that will then appropriately update the underlying relational tables if you execute a SQL INSERT, UPDATE, or DELETE statement against an object view.
One of the advantages to using object views is that you can still use object-relational-ignorant tools against the relational tables. But if I were you, and you went this far, I would go all the way and reap the full benefit of object-oriented technology in an Oracle database; I would do step 4.
The last step is where you create an object-relational database or objectbase, one where real-world entities are accurately modeled for both attributes and behavior. I’m not advocating moving all of an application’s code into the database—I mean objectbase; actually, just the opposite. The only items that should reside in the objectbase are the entities, along with their data attributes and methods that are in scope for the particular application.
This means that what is normally thought of as data in a relational database should be stored in the objectbase, along with its associated entity business rules. Whatever code is used to present the object-relational model—data-entry screens, reports, graphics, and so on—should continue to reside in an external codebase. Regardless of the presentation layer, any entity-related business rule should be executed in the objectbase, not in the presentation layer.
That may seem like a radical departure from what you’re currently doing, or perhaps not. If you’re using EJBs, you’re already making a remote procedure call to employ business rules in a so-called middle tier. Why not do it correctly, and employ the authoritative rules that permanently reside side by side with the data attributes, instead of supposedly recoding the same rules again and again (with inconsistency) in another codebase?
A Change in Order
Building an object-relational database, or objectbase, calls for a change in order. When building a relational database, you follow this order:
- Create tables with columns (attributes).
- Create table packages with methods (behavior).
- Code presentation by accessing the data in tables and the behaviors in their associated packages.
When you build an object-relational database, or objectbase, you follow this order:
- Create user-defined types with attributes and behaviors.
- Create tables based on your user-defined types.
- Code presentation by accessing attributes and behaviors in user-defined type tables.
With that change of order in mind, I think it’s time I actually show you example of what I’m talking about. Enough philosophy—it’s time for action. We’ll begin with how to create a new user-defined type, an object type.
Object Types
You remember the keyword TYPE from Chapter 3, right? You used it to declare the equivalent of an array in PL/SQL: a PL/SQL table. Well in this context, you’re going to use it to permanently create a new user-defined data type in the database. Using the term data type is a bit of a misnomer. You’re really going to be creating new types of classes of entities, or object types. Yes, here’s a new word for your vocabulary: object type. An object type defines both data attributes and related methods. So the SQL keywords you’ll use to create user-defined object types are CREATE TYPE.
By creating a user-defined type, you’ll permanently marry data and its associated methods in the database so they can’t be separated, not even accidentally. It’s a marriage for life; no divorce allowed. Of course, you’ll be able to change data and behavior over time, but you’ll be coding behavior so it will be time-dependent, and therefore be coding behavior over time.
Some day, we will store universal behavioral executables the same way we store data. But today, the best we can do is to store our code with our data, not as separate entities, and to modify our code to be point-in-time-dependent, so we can reproduce behavior as it existed at any point in time.
Since you probably have little experience with this form of SQL, I’m going to show you two examples, with you doing some of the coding along the way. First, let’s create a user-defined TYPE for one of your code tables: Gender Type.
Note In Chapters 1–5, you were using an Oracle username of RPS to log into the database. This meant you were using the RPS schema. In this chapter, you need to use Oracle username OPS to log into the database so you won’t encounter naming conflicts. By the end of this chapter, you will have object-relational types and tables in schema OPS that have the same names as relational packages and tables in schema RPS.
Create a User-Defined Type Specification
Creating a user-defined type takes two steps. First, you create a specification, and then you create a type body, or implementation, similar to creating a package. Listing 6-1 is an example of a CREATE TYPE specification script for creating a user-defined type for code table Gender.
Listing 6-1. An Example of a CREATE TYPE Statement for a Code Table, gender_type.tps
01 create TYPE GENDER_TYPE as object (
02 /*
03 gender_type.tps
04 by Don Bales on 2014-10-20
05 Type GENDER's attributes and methods.
06 */
07 id number,
08 code varchar2(30),
09 description varchar2(80),
10 active_date date,
11 inactive_date date,
12 /*
13 A constructor for creating a new instance of type GENDER_TYPE with NULL
14 values.
15 */
16 CONSTRUCTOR FUNCTION gender_type(
17 self in out gender_type)
18 return self as result,
19 /*
20 A constructor for creating a new instance of type GENDER_TYPE for insert.
21 */
22 CONSTRUCTOR FUNCTION gender_type(
23 self in out gender_type,
24 aiv_code varchar2,
25 aiv_description varchar2)
26 return self as result,
27 /*
28 Gets the code and decription values for the specified id.
29 */
30 STATIC PROCEDURE get_code_descr(
31 ain_id in number,
32 aov_code out varchar2,
33 aov_description out varchar2),
34 /*
35 Verifies that the passed code value is an exact or like match on the
36 date specified.
37 */
38 STATIC PROCEDURE get_code_id_descr(
39 aiov_code in out varchar2,
40 aon_id out number,
41 aov_description out varchar2,
42 aid_on in date),
43 /*
44 Verifies that the passed code value is currently an exact or like match.
45 */
46 STATIC PROCEDURE get_code_id_descr(
47 aiov_code in out varchar2,
48 aon_id out number,
49 aov_description out varchar2),
50 /*
51 Returns a new primary key id value for a row.
52 */
53 MEMBER FUNCTION get_id
54 return number,
55 /*
56 Returns the id for the specified code value.
57 */
58 STATIC FUNCTION get_id(
59 aiv_code in varchar2)
60 return number,
61 /*
62 Test-based help for this package. "set serveroutput on" in SQL*Plus.
63 */
64 STATIC PROCEDURE help,
65 /*
66 Test units for this package.
67 */
68 STATIC PROCEDURE test,
69 /*
70 A MAP function for sorting at the object level.
71 */
72 MAP MEMBER FUNCTION to_varchar2
73 return varchar2
74 );
75 /
76 @se.sql GENDER_TYPE
The CREATE TYPE specification syntax used by Listing 6-1 is as follows:
CREATE [OR REPLACE] TYPE <type_name> AS OBJECT (
<attribute_name_1> <attribute_type_1>,
<attribute_name_2> <attribute_type_2>,...
<attribute_name_N> <attribute_type_N>,
[MAP]MEMBER or STATIC FUNCTION or PROCEDURE <method_name> [(
SELF [IN OUT][NOCOPY]<type_name>,
<parameter_name_1> [IN OUT] <parameter_type_1>,
<parameter_name_2> [IN OUT] <parameter_type_2>,...
<parameter_name_N> [IN OUT] <parameter_type_N>)]
[RETURN <return_type>],
CONSTRUCTOR FUNCTION <type_name> (
SELF [IN OUT][NOCOPY]<type_name>,[
<parameter_name_1> [IN OUT] <parameter_type_1>,
<parameter_name_2> [IN OUT] <parameter_type_2>,...
<parameter_name_N> [IN OUT] <parameter_type_N>)],
);
where
- <type_name> is the name of your user-defined TYPE.
- <attribute_name> is the name of a data item in your user-defined TYPE.
- <attribute_type> is a predefined scalar data type or a user-defined type.
- <method_name> is the name of a MEMBER or STATIC, FUNCTION or PROCEDURE.
- <parameter_name> is the name of a parameter being passed into or out of a method.
- <parameter_type> is a predefined scalar data type or a user-defined type.
- <return_type> is also a predefined scalar data type or a user-defined type to be returned by an associated FUNCTION.
Wow! As you can see by Listing 6-1, I’ve added a lot of methods to my Gender Type code user-defined TYPE. Let’s take a look at the listing line by line:
- Line 1 uses the DDL keywords CREATE TYPE ... AS OBJECT to declare a new user-defined TYPE specification for my schema in the database. I call my new type GENDER_TYPE, as in it’s the Gender Type code’s user-defined object type.
- Lines 2 through 6 contain my usual source code preamble that documents the name of the source file, the author, date written, and purpose.
- On lines 7 through 11, I list the attributes for the TYPE. Theses are the names of the columns from the original relational table. Did you notice that I ended the last attribute with a comma (,), which means I intend to continue specifying attributes or methods for the TYPE?
- Lines 12 through 18 declare a user-defined CONSTRUCTOR method. You’ll call a CONSTRUCTOR any time you want to create a new instance of a TYPE. For example, all user-defined TYPEs come with a default CONSTRUCTOR, where you pass in attribute values in the order they are specified in the TYPE specification. So to create a new instance of Gender Type using the default CONSTRUCTOR, you would code something like this:
o_gender_type := GENDER_TYPE(3, ‘U’, ‘Unknown’, SYSDATE, NULL);
The CONSTRUCTOR I’ve declared here will create an instance of the TYPE with all its attributes initialized to NULL values. This is handy when I want to access a member function without actually working with a permanent instance of a TYPE.
- Lines 12 through 15 hold a multiline comment that documents the purpose of the method. Remember to document as you go.
- Lines 19 through 26 declare a user-defined CONSTRUCTOR for coding convenience. Using it, you can create a new instance of Gender Type by simply passing the code and description like this:
o_gender_type := GENDER_TYPE(‘U’, ‘Unknown’);
- On lines 27 through 33, I specify my first STATIC method for the TYPE. If I specify a method as STATIC, it’s available for execution through the TYPE’s name, like a package, but not available through an instance of the type. For example, to execute PROCEDURE get_code_descr(), I would code GENDER_TYPE.get_code_desc(...), not using a variable of the TYPE in the method call like this: o_gender_type.get_code_desc(...).
- Skipping down, lines 50 through 54 declare a MEMBER method, get_id(), to allocate the next primary key value. This is the same method I asked you to create in a table package in Chapter 5. Now it has become a MEMBER method for the TYPE.
- Finally, lines 69 through 73 declare a MAP MEMBER FUNCTION. This method will be used by the Oracle database any time it needs to sort object-level instances of the TYPE.
Let’s review. How did I get here, to a TYPE specification, from there, a relational table and associated table package? Here’s what I did:
- Took the columns from the relational table and made them the attributes of the TYPE.
- Took the methods from the relational table’s package specification and made them MEMBER or STATIC methods of the TYPE.
- Added a MAP MEMBER FUNCTION for sorting.
- Added user-defined CONSTRUCTOR FUNCTIONs for convenience.
Now that you’ve seen the specification, let’s look at the implementation!
Create a User-Defined Type Implementation
Listing 6-2 is a CREATE TYPE BODY ... AS (implementation) script for the TYPE GENDER_TYPE specification shown in Listing 6-1. I’ll cover all the methods implemented in this TYPE in subsequent chapters, when I take a polymorphic look at methods required by the TYPE and presentation layers: data entry, data migration, data processing, reporting, graphing, and so on. For now, it’s important for you to focus on the method implementations that are unique to a TYPE, namely the MAP MEMBER and CONSTRUCTOR FUNCTIONs.
Listing 6-2. An Example of a CREATE TYPE BODY Statement for a Code Table, gender_type.tpb
001 create or replace TYPE BODY GENDER_TYPE as
002 /*
003 gender_type.tpb
004 by Don Bales on 2014-10-20
005 Type GENDER_TYPE's attributes and methods
006 */
007
008 CONSTRUCTOR FUNCTION gender_type(
009 self in out gender_type)
010 return self as result is
011
012 begin
013 id := NULL;
014 code := NULL;
015 description := NULL;
016 active_date := NULL;
017 inactive_date := NULL;
018 return;
019 end gender_type;
020
021
022 CONSTRUCTOR FUNCTION gender_type(
023 self in out gender_type,
024 aiv_code varchar2,
025 aiv_description varchar2)
026 return self as result is
027
028 begin
029 id := get_id();
030 code := aiv_code;
031 description := aiv_description;
032 active_date := SYSDATE;
033 inactive_date := DATE_.d_MAX;
034 return;
035 end gender_type;
036
037
038 STATIC PROCEDURE get_code_descr(
039 ain_id in number,
040 aov_code out varchar2,
041 aov_description out varchar2 ) is
042
043 begin
044 select code,
045 description
046 into aov_code,
047 aov_description
048 from GENDER_TYPES
049 where id = ain_id;
050 end get_code_descr;
051
052
053 STATIC PROCEDURE get_code_id_descr(
054 aiov_code in out varchar2,
055 aon_id out number,
056 aov_description out varchar2,
057 aid_on in date ) is
058
059 v_code varchar2(30);
060
061 begin
062 select id,
063 description
064 into aon_id,
065 aov_description
066 from GENDER_TYPES
067 where code = aiov_code
068 and aid_on between active_date and nvl(inactive_date, DATE_.d_MAX);
069 exception
070 when NO_DATA_FOUND then
071 select id,
072 code,
073 description
074 into aon_id,
075 v_code,
076 aov_description
077 from GENDER_TYPES
078 where code like aiov_code||'%'
079 and aid_on between active_date and nvl(inactive_date, DATE_.d_MAX);
080 aiov_code := v_code;
081 end get_code_id_descr;
082
083
084 STATIC PROCEDURE get_code_id_descr(
085 aiov_code in out varchar2,
086 aon_id out number,
087 aov_description out varchar2 ) is
088
089 begin
090 get_code_id_descr(
091 aiov_code,
092 aon_id,
093 aov_description,
094 SYSDATE );
095 end get_code_id_descr;
096
097
098 MEMBER FUNCTION get_id
099 return number is
100
101 n_id number;
102
103 begin
104 select GENDERS_ID.nextval
105 into n_id
106 from SYS.DUAL;
107
108 return n_id;
109 end get_id;
110
111
112 STATIC FUNCTION get_id(
113 aiv_code in varchar2 )
114 return number is
115
116 n_id number;
117
118 begin
119 select id
120 into n_id
121 from GENDER_TYPES
122 where code = aiv_code;
123
124 return n_id;
125 end get_id;
126
127
128 STATIC PROCEDURE help is
129
130 begin
131 pl('No help at this time.'),
132 end help;
133
134
135 STATIC PROCEDURE test is
136
137 begin
138 pl('No tests coded at this time'),
139 end test;
140
141
142 MAP MEMBER FUNCTION to_varchar2
143 return varchar2 is
144
145 begin
146 return description||to_char(active_date, 'YYYYMMDDHH24MISS'),
147 end to_varchar2;
148
149
150 end;
151 /
152 @be.sql GENDER_TYPE
The CREATE TYPE BODY syntax used by Listing 6-2 is as follows:
CREATE [OR REPLACE] TYPE BODY <type_name> AS
[MAP]MEMBER or STATIC FUNCTION or PROCEDURE <method_name> [(
SELF [IN OUT][NOCOPY]<type_name>,
<parameter_name_1> [IN OUT] <parameter_type_1>,
<parameter_name_2> [IN OUT] <parameter_type_2>,...
<parameter_name_N> [IN OUT] <parameter_type_N>),]
[RETURN <return_type>],
CONSTRUCTOR FUNCTION <type_name> (
SELF [IN OUT][NOCOPY]<type_name>,[
<parameter_name_1> [IN OUT] <parameter_type_1>,
<parameter_name_2> [IN OUT] <parameter_type_2>,...
<parameter_name_N> [IN OUT] <parameter_type_N>),]
END;
where
- <type_name> is the name of your user-defined TYPE.
- <method_name> is the name of a MEMBER or STATIC, FUNCTION or PROCEDURE.
- <parameter_name> is the name of a parameter being passed INto or OUT of a method.
- <parameter_type> is a predefined scalar data type or a user-defined type.
- <return_type> is also a predefined scalar data type or a user-defined type to be returned by an associated FUNCTION.
As you can see by Listing 6-2, I’ve added quite a bit of behavior to my Gender Type code user-defined type. Let’s break down the listing:
- Line 1 uses the DDL keywords CREATE TYPE BODY ... AS to create a TYPE BODY for TYPE specification GENDER_TYPE. The BODY consists of method implementations for the methods declare in the TYPE specification.
- Lines 9 through 19 implement a CONSTRUCTOR FUNCTION that will return an instance of a Gender Type object initialized to NULL values.
- Lines 22 through 35 implement a CONSTRUCTOR FUNCTION for coding convenience that requires you to pass in only a code and description in order to create a new Gender Type object instance. As you can see on line 29, the method initializes the id attribute using the MEMBER FUNCTION get_id().
- Lines 98 through 109 implement the MEMBER FUNCTION get_id() in order to allocate the next primary key value.
- Lines 198 through 203 implement the MAP MEMBER FUNCTION to_varchar2(), which returns the description for a given code along with its creation date, all as one long varchar2 value. Oracle will use this value to determine precedence when ordering object instances in a SQL SELECT statement. You don’t need to declare and implement a MAP MEMBER FUNCTION, but I always do.
Which came first, the chicken or the egg? Now, if you’re real sharp, you may have noticed that I have some dependency issues with the implementation. In order for the TYPE BODY to compile, an object table named GENDER_TYPES must already exist! Yes, the STATIC methods refer to an object table GENDER_TYPES, based on object TYPE GENDER_TYPE. This isn’t a big problem. I can address it one of two ways:
- Compile the TYPE specification, create the object table based on the TYPE specification, and then compile the TYPE BODY.
- Use dynamic SQL in the STATIC methods so the dependent table name in the SQL statements doesn’t get resolved until runtime.
I normally choose the first alternative, for performance reasons.
It’s Your Turn to Create a User-Defined Type
Now that you’ve seen me transform the Gender Type table and table package into a user-defined TYPE, it’s time for you to do the same for the Worker Type table.
- Make sure to log into the database using username OPS, password OPS.
- Get the list of columns from the relational SQL WORKER_TYPES table (from Chapter 5), and at least the one method you created in table package WORKER_TYPE, and use that information to code a WORKER_TYPE TYPE specification.
- Save your specification script as worker_type.tps.
- Compile your specification: @worker_type.tps.
- Create a WORKER_TYPE TYPE BODY script, which will contain the implementation for your method get_id().
- Save you body script as worker_type.tpb.
- Compile your body: @worker_type.tpb.
- Test your user-defined type to at least see that it compiled.
To test your user-defined type, you can use an anonymous PL/SQL procedure like the following.
declare
-- Declare a worker_type_o variable
o_worker_type WORKER_TYPE;
begin
-- Now use the default constructor to create a new instance
-- of the object
o_worker_type := new WORKER_TYPE(
NULL, 'H', 'A hard worker', SYSDATE, NULL);
-- Now allocate a new ID using the member function get_id()
o_worker_type.id := o_worker_type.get_id();
-- Now show the values of the attributes in the instance
pl('o_worker_type.id = '||o_worker_type.id);
pl('o_worker_type.code = '||o_worker_type.code);
pl('o_worker_type.description = '||o_worker_type.description);
pl('o_worker_type.active_date = '||o_worker_type.active_date);
pl('o_worker_type.inactive_date = '||o_worker_type.inactive_date);
end;
/
Your results should look something like this:
o_worker_type.id = 111
o_worker_type.code = H
o_worker_type.description = A hard worker
o_worker_type.active_date = 20070223 100918
o_worker_type.inactive_date =
PL/SQL procedure successfully completed.
Listings 6-3 and 6-4 demonstrate my solution for this exercise. Once again, I’m showing you a fully implemented TYPE.
Listing 6-3. A Worker Type Used-Defined TYPE Specification, worker_type.tps
01 create TYPE WORKER_TYPE as object (
02 /*
03 worker_type.tps
04 by Don Bales on 2014-10-20
05 Type WORKER_TYPE's attributes and methods.
06 */
07 id number,
08 code varchar2(30),
09 description varchar2(80),
10 active_date date,
11 inactive_date date,
12 -- Gets the code and decription values for the specified work_type_id.
13 STATIC PROCEDURE get_code_descr(
14 ain_id in number,
15 aov_code out varchar2,
16 aov_description out varchar2 ),
17 -- Verifies the passed aiov_code value is an exact or like match on the
18 -- date specified.
19 STATIC PROCEDURE get_code_id_descr(
20 aiov_code in out varchar2,
21 aon_id out number,
22 aov_description out varchar2,
23 aid_on in date ),
24 -- Verifies the passed aiov_code value is currently an exact or like match.
25 STATIC PROCEDURE get_code_id_descr(
26 aiov_code in out varchar2,
27 aon_id out number,
28 aov_description out varchar2 ),
29 -- Returns a newly allocated id value.
30 MEMBER FUNCTION get_id
31 return number,
32 -- Returns the id for the specified code value.
33 STATIC FUNCTION get_id(
34 aiv_code in varchar2 )
35 return number,
36 -- Test-based help for this package. "set serveroutput on" in SQL*Plus.
37 STATIC PROCEDURE help,
38 -- Test units for this package.
39 STATIC PROCEDURE test,
40 -- A MAP function for sorting at the object level.
41 MAP MEMBER FUNCTION to_varchar2
42 return varchar2,
43 -- A constructor for creating a new instance of type WORKER_TYPE
44 -- with NULL values.
45 CONSTRUCTOR FUNCTION worker_type(
46 self in out worker_type)
47 return self as result,
48 -- A constructor for creating a new instance of type WORKER_TYPE
49 -- for insert.
50 CONSTRUCTOR FUNCTION worker_type(
51 self in out worker_type,
52 aiv_code in varchar2,
53 aiv_description in varchar2)
54 return self as result
55 );
56 /
57 @se.sql WORKER_TYPE
Listing 6-4. A Worker Type User-Defined TYPE BODY, worker_type.tpb
001 create or replace TYPE BODY WORKER_TYPE as
002 /*
003 worker_type.tpb
004 by Don Bales on 2014-10-20
005 Type WORKER_TYPE's methods
006 */
007
008
009 MEMBER FUNCTION get_id
010 return number is
011
012 n_id number;
013
014 begin
015 select WORKER_TYPES_ID.nextval
016 into n_id
017 from SYS.DUAL;
018
019 return n_id;
020 end get_id;
021
022
023 STATIC FUNCTION get_id(
024 aiv_code in varchar2 )
025 return number is
026
027 n_id number;
028
029 begin
030 select id
031 into n_id
032 from WORKER_TYPES
033 where code = aiv_code;
034
035 return n_id;
036 end get_id;
037
038
039 STATIC PROCEDURE get_code_descr(
040 ain_id in number,
041 aov_code out varchar2,
042 aov_description out varchar2 ) is
043
044 begin
045 select code,
046 description
047 into aov_code,
048 aov_description
049 from WORKER_TYPES
050 where id = ain_id;
051 end get_code_descr;
052
053
054 STATIC PROCEDURE get_code_id_descr(
055 aiov_code in out varchar2,
056 aon_id out number,
057 aov_description out varchar2,
058 aid_on in date ) is
059
060 v_code varchar2(30);
061
062 begin
063 select id,
064 description
065 into aon_id,
066 aov_description
067 from WORKER_TYPES
068 where code = aiov_code
069 and aid_on between active_date and nvl(inactive_date, DATE_.d_MAX);
070 exception
071 when NO_DATA_FOUND then
072 select id,
073 code,
074 description
075 into aon_id,
076 v_code,
077 aov_description
078 from WORKER_TYPES
079 where code like aiov_code||'%'
080 and aid_on between active_date and nvl(inactive_date, DATE_.d_MAX);
081
082 aiov_code := v_code;
083 end get_code_id_descr;
084
085
086 STATIC PROCEDURE get_code_id_descr(
087 aiov_code in out varchar2,
088 aon_id out number,
089 aov_description out varchar2 ) is
090
091 begin
092 get_code_id_descr(
093 aiov_code,
094 aon_id,
095 aov_description,
096 SYSDATE );
097 end get_code_id_descr;
098
099
100 STATIC PROCEDURE help is
101
102 begin
103 pl('No help coded at this time.'),
104 end help;
105
106
107 STATIC PROCEDURE test is
108
109 begin
110 pl('No tests coded at this time.'),
111 end test;
112
113
114 MAP MEMBER FUNCTION to_varchar2
115 return varchar2 is
116
117 begin
118 return description||to_char(active_date, 'YYYYMMDDHH24MISS'),
119 end to_varchar2;
120
121
122 CONSTRUCTOR FUNCTION worker_type(
123 self in out worker_type)
124 return self as result is
125
126 begin
127 id := NULL;
128 code := NULL;
129 description := NULL;
130 active_date := NULL;
131 inactive_date := NULL;
132 return;
133 end worker_type;
134
135
136 CONSTRUCTOR FUNCTION worker_type(
137 self in out worker_type,
138 aiv_code in varchar2,
139 aiv_description in varchar2)
140 return self as result is
141
142 begin
143 id := get_id();
144 code := aiv_code;
145 description := aiv_description;
146 active_date := SYSDATE;
147 inactive_date := DATE_.d_MAX;
148 return;
149 end worker_type;
150
151
152 end;
153 /
154 @be.sql WORKER_TYPE
Why don’t we create an object view next!
Object Views
Think of this as a cloning project. You can seamlessly transition into the use of user-defined objects by intelligently overlaying your relational database structure with a set of object views. An object view takes the columns from one or more relational tables and morphs them into a pseudo-table of user-defined objects, complete with attributes and behavior.
Create an Object View
Listing 6-5 is a script to create an object view for the relational Gender Type code table GENDER_TYPES. This view automatically maps the column values in table GENDER_TYPES to the attributes of user-defined TYPE GENDER_TYPE, producing what appears to be a table of GENDER_TYPE objects called GENDER_TYPEZ.
Listing 6-5. An Example of an Object View Script for Table GENDER_TYPES, gender_typez.vw
01 rem gender_typez.vw
02 rem by Donald J. Bales on 2014-10-20
03 rem Create an object view for relational table GENDER_TYPES
04
05 create view GENDER_TYPEZ of GENDER_TYPE
06 with object identifier (id) as
07 select id,
08 code,
09 description,
10 active_date,
11 inactive_date
12 from RPS.GENDER_TYPES;
The CREATE VIEW syntax used in Listing 6-5 is as follows:
CREATE [OR REPLACE] VIEW <view_name> OF <type_name>
WITH OBJECT IDENTIFIER (<primary_key_attributes>) AS
SELECT <column_name_1>,
<column_name_2>,...
<column_name_N>
FROM <table_name>;
where
- <view_name> is the name of the view to create.
- <type_name> is the user-defined TYPE to map the columns to in the relational table.
- <primary_key_attributes> is one or more attributes in the user-defined TYPE that map to the primary key column(s) of the underlying table.
- <column_name> is the name of a column in the table to map to the attributes of the user-defined TYPE.
- <table_name> is the underlying table to map to the user-defined TYPE.
That wasn’t much work, was it? OK, now let’s test the view. Listing 6-6 is an anonymous PL/SQL procedure to test the object view. It’s well commented, so you can see what is being tested. Note that you can execute INSERT, UPDATE, DELETE, and SELECT statements against an object view or its underlying table and get the same results. An object view therefore provides a means for your database to be both relational and object-oriented at the same time!
Note On line 12, I’ve added RPS. to GENDER_TYPES so the anchor points to the relational SQL schema’s GENDER_TYPES table.
Listing 6-6. A Test Unit for View GENDER_TYPEZ, gender_typez.sql
01 rem gender_typez.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem test unit for object view GENDER_TYPEZ
04
05 declare
06 -- Declare a variable of the user-define type
07 o_gender_type GENDER_TYPE;
08 -- Declare a variable for the underlying table
09 r_gender_type RPS.GENDER_TYPES%ROWTYPE;
10
11 begin
12 -- Insert a test object using the convenience constructor
13 insert into GENDER_TYPEZ
14 values ( GENDER_TYPE( 'T', 'Test') );
15
16 -- Now update the inactive date on the object
17 update GENDER_TYPEZ
18 set inactive_date = SYSDATE
19 where code = 'T';
20
21 -- Retrieve the object in order to show its values
22 select value(g)
23 into o_gender_type
24 from GENDER_TYPEZ g
25 where code = 'T';
26
27 -- Show the object's values
28 pl('o_gender_type.id = '||o_gender_type.id);
29 pl('o_gender_type.code = '||o_gender_type.code);
30 pl('o_gender_type.description = '||o_gender_type.description);
31 pl('o_gender_type.active_date = '||o_gender_type.active_date);
32 pl('o_gender_type.inactive_date = '||o_gender_type.inactive_date);
33
34 -- Delete the test object
35 delete GENDER_TYPEZ
36 where code = 'T';
37
38 -- This time insert the test object using the instance variable
39 insert into GENDER_TYPEZ
40 values ( o_gender_type );
41
42 -- Now, select the values from the underlying relational table
43 select *
44 into r_gender_type
45 from RPS.GENDER_TYPES
46 where code = 'T';
47
48 -- Show the record's values
49 pl('r_gender_type.id = '||r_gender_type.id);
50 pl('r_gender_type.code = '||r_gender_type.code);
51 pl('r_gender_type.description = '||r_gender_type.description);
52 pl('r_gender_type.active_date = '||r_gender_type.active_date);
53 pl('r_gender_type.inactive_date = '||r_gender_type.inactive_date);
54
55 -- Last, delete the object from the relational table
56 delete RPS.GENDER_TYPES
57 where code = 'T';
58
59 -- Commit all these operations
60 commit;
61
62 -- Confirm that the test completed successfully
63 pl('Test completed successfully.'),
64 end;
65 /
And, here are the results of the test unit in Listing 6-6:
SQL> @gender_ov.sql
o_gender.id = 131
o_gender.code = T
o_gender.description = Test
o_gender.active_date = 20070223 140020
o_gender.inactive_date = 20070223 140020
r_gender.id = 131
r_gender.code = T
r_gender.description = Test
r_gender.active_date = 20070223 140020
r_gender.inactive_date = 20070223 140020
Test completed successfully.
PL/SQL procedure successfully completed.
Note On line 9, I’ve added RPS. to GENDER_TYPES%ROWTYPE so the anchor points to the relational SQL schema’s GENDER_TYPES table.
It’s Your Turn to Create an Object View
Yes, it’s your turn. Can you guess what I’m going to ask you to do next? I thought so.
- Make sure you are logged in to the database using Oracle username OPS.
- Create a script to create an object view for table RPS.WORKER_TYPES that maps its values to user-defined type WORKER_TYPE. You know, the TYPE you just finished creating in the previous exercise.
- Save your script as worker_typez.vw.
- Execute your script to create the object view: @worker_typez.vw.
- Create a test unit script and test your new object view.
Nope, I’m not going to show you my solution. This is so simple you don’t need my help!
Next, let’s do step four of the four-step transformation to object-orientation.
Object Tables
Once you’ve created a TYPE, it’s no harder to create an object (-relational) table than it is to build a relational table.
Create an Object Table
As a matter of fact, Listing 6-7 is nothing more than my table-creation script for the Gender Types table, gendertypes.tab, from Chapter 5, with a few minor changes.
Listing 6-7. An Example of an Object Table Script for GENDER_TYPES, gender_types.tab
01 rem gender_types.tab
02 rem by Donald J. Bales on 2014-10-20
03 rem Create an object table for the Gender Type codes
04
05 --drop table GENDER_TYPES;
06 create table GENDER_TYPES of GENDER_TYPE;
07
08 --drop sequence GENDERS_ID;
09 create sequence GENDERS_ID
10 start with 1;
11
12 alter table GENDER_TYPES add
13 constraint GENDER_TYPES_PK
14 primary key ( id )
15 using index;
16
17 alter table GENDER_TYPES add
18 constraint GENDER_TYPES_UK
19 unique ( code, active_date )
20 using index;
21
22 execute SYS.DBMS_STATS.gather_table_stats(USER, 'GENDER_TYPES'),
The CREATE TABLE syntax used in Listing 6-7 is as follows:
CREATE TABLE <table_name> OF <type_name>;
where <table_name> is the name of the table to create, and <type_name> is the user-defined TYPE to use instead of column names for the table.
There isn’t a lot of extra work to creating an object table vs. a relational table, now is there? Here’s the rundown:
- On line 6, I use the new syntax to create a table of one user-defined TYPE instead of one or more predefined scalar data types.
- On lines 10 through 12, I create a sequence for the object (-relational) table.
- On lines 12 through 15, I create a primary key on the object table, just as I did on the relational table.
- On lines 17 through 20, I create a unique key on the object table, just as I did on the relational table.
It’s Your Turn to Create an Object Table
Once again, it’s your turn. For this exercise, create an object table named WORKER_TYPES for the Worker Type code table.
- Write a script to create an object table based on user-defined TYPE WORKER_TYPE.
- Save your script as worker_types.tab.
- Execute your script to create the object view: @worker_types.tab.
- Create a test unit script and test your new object table.
I’ll indulge you. Listing 6-8 is my solution to this exercise.
Listing 6-8. An Example of an Object Table Script for WORKER_TYPES, worker_types.tab
01 rem worker_type.tab
02 rem copyright by Donald J. Bales on 2014-10-20
03 rem Create an object table for the Worker Type codes
04
05 --drop table WORKER_TYPES;
06 create table WORKER_TYPES of WORKER_TYPE;
07
08 --drop sequence WORKER_TYPES_ID;
09 create sequence WORKER_TYPES_ID
10 start with 1;
11
12 alter table WORKER_TYPES add
13 constraint WORKER_TYPES_PK
14 primary key ( id )
15 using index;
16
17 alter table WORKER_TYPES add
18 constraint WORKER_TYPES_UK
19 unique ( code )
20 using index;
21
22 execute SYS.DBMS_STATS.gather_table_stats(USER, 'WORKER_TYPES'),
To test this object table, I just save my object view test unit as worker_types.sql, change the object view name to the object table name, and get rid of the relational table tests, and then ta da! Listing 6-9 shows my test unit for WORKER_TYPES.
Listing 6-9. A Test Unit for Table WORKER_TYPES, worker_types.sql
01 rem worker_types.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem test unit for object table WORKER_TYPES
04
05 declare
06 -- Declare a variable of the user-define type
07 o_worker_type WORKER_TYPE;
08
09 begin
10 -- Insert a test object using the convenience constructor
11 insert into WORKER_TYPES
12 values ( WORKER_TYPE( 'T', 'Test') );
13
14 -- Now update the inactive date on the object
15 update WORKER_TYPES
16 set inactive_date = SYSDATE
17 where code = 'T';
18
19 -- Retrieve the object in order to show its values
20 select value(g)
21 into o_worker_type
22 from WORKER_TYPES g
23 where code = 'T';
24
25 -- Show the object's values
26 pl('o_worker_type.id = '||o_worker_type.id);
27 pl('o_worker_type.code = '||o_worker_type.code);
28 pl('o_worker_type.description = '||o_worker_type.description);
29 pl('o_worker_type.active_date = '||o_worker_type.active_date);
30 pl('o_worker_type.inactive_date = '||o_worker_type.inactive_date);
31
32 -- Delete the test object
33 delete WORKER_TYPES
34 where code = 'T';
35
36 -- This time insert the test object using the instance variable
37 insert into WORKER_TYPES
38 values ( o_worker_type );
39
40 -- Last, delete the object from the relational table
41 delete WORKER_TYPES
42 where code = 'T';
43
44 -- Commit all these operations
45 commit;
46
47 -- Confirm that the test completed successfully
48 pl('Test completed successfully.'),
49 end;
50 /
The results of the test unit in Listing 6-9 should look something like this:
SQL> @worker_types.sql
o_worker_type.id = 1
o_worker_type.code = T
o_worker_type.description = Test
o_worker_type.active_date = 29-DEC-14
o_worker_type.inactive_date = 29-DEC-14
Test completed successfully.
PL/SQL procedure successfully completed.
Come on, you have to admit it. It’s pretty easy to make a transition to object orientation, right? Perhaps, or perhaps not. Some argue that there is a problem between how object-oriented languages represent data and how data is stored in a relational database, calling it an impedance mismatch.
Impedance Mismatch?
I argue that if done properly, there is little so-called “mismatch” between the classes used in an object-oriented language like Java and object-relational user-defined types in Oracle—that is, if they both properly model the real world.
I have an entire section dedicated to the use of Java Database Connectivity (JDBC) and object-relational user-defined types in one of my previous books (Java Programming with Oracle JDBC, OReilly, 2001). My 30 plus years of experience in both programming with object-oriented languages and using relational and now object-relational databases has led me to the conclusion that the mismatch is one of improper analysis vs. the reality of the problem domain.
The only time I see a mismatch is when one of the following occurs:
- “Catchall” classes are used in the presentation layer, which make it easy to represent and move objects around in the presentation layer, but they don’t actually model the real world at all.
- Denormalized attributes in classes, which once again make it easier to work with data in the presentation layer, but don’t even remotely represent the cardinality of the real world.
- No time is taken to do a proper analysis. It’s build a presentation and then create a database that fits it, or restated, shoot, ready, aim! Right.
- Target databases use a different programming language from the presentation layer’s object-oriented programming environment, so the database programming language is misunderstood, misused, or ignored altogether.
Don’t take these in any particular order, because there are more reasons than I am willing to list. I seem to remember the same phenomena—you know, all the reasons not to adopt better technology—years ago when relational technology first appeared, say 1984?
So what haven’t I told you? There are, in fact, a few topics I have not covered yet. Let’s delve into those next.
First, I haven’t shown any examples with nested types. For example, I could create a TYPE ADDRESS for address information, and then use that TYPE as an attribute in another TYPE like CONTACT. You can do that; it’s not a problem.
Nor have I shown you any examples of a TYPE that contain arrays of other TYPEs. That’s certainly possible. You can create what is called a nested TYPE, and implement it as a nested table where you can have an unlimited number of entries, or as a varying array that has a fixed number of entries. I don’t like the sound of the latter. It’s not very relational in nature. And nesting is where all the so-called impedance mismatch occurs.
In the minds of some object-oriented programmers, it’s necessary to create huge, complex, object types that contain everything remotely similar. You know this; if you’re an object-oriented programmer, you’ve seen them, or worse, created them.
Consider Figure 6-1, a class diagram to replace the ERD for the problem domain from Chapter 1. Are all three historical entities—LocgicalAssignment, PhysicalAssignment, and WorkAssignment—part of Worker in an object-oriented setting? No, they may be related, but you won’t find them on any particular worker, will you? They are actually stand-alone entities. However, in most cases, someone would create a class Worker that contained these three entities as arrays. There’s the mismatch. It’s a break between reality and programming convenience.
Figure 6-1. A partial class diagram for the sample problem domain
It’s Your Turn to Prove There’s No Impedance Mismatch
At this point, I’ve coded the scripts to create the Gender code object table, and you’ve done the same for the Worker Type object table. Now you can put these to work as you code the scripts for and create a Worker object table. Here’s what to do.
- Verify that you successfully executed the following scripts: gender_type.tps, gender_type.tpb, gender_types.tab, and gender_types.ins.
- Verify that you successfully coded and executed the following scripts: worker_type.tps, worker_type.tpb, worker_types.tab, and worker_types.ins.
- Write a script to create a TYPE specification for the Worker entity. You can get the attributes from the column names in file worker_types.tabfrom Chapter 5. You can get the method names from package file worker_type.pks from Chapter 5.
- Save your script as worker.tps.
- Compile your script: @worker.tps.
- Write a script to create an object table named WORKERS using the user-defined type WORKER you just created.
- Save your script as workers.tab.
- Compile your script: @workers.tab.
- Write a script to implement the TYPE BODY for the Worker entity. You can get the method implementations from package file worker_type.pkb from Chapter 5.
- Save your script as worker.tpb.
- Compile your script: @worker.tps.
- At this point, you should have a valid WORKERS object table. So now modify the script you used to populate the WORKERS table in Chapter 5, workers.ins, to populate the WORKERS object table.
- Save your script as workers.ins.
- Execute your script in order to populate the object table: @workers.ins.
- Create a test unit script and test your new object table.
Listings 6-10 through 6-13 show my solutions for this exercise. Listing 6-10 is my specification for the TYPE WORKER.
Listing 6-10. The Specification Script for User-Defined TYPE WORKER, worker.tps
001 create type WORKER as object (
002 /*
003 worker.tps
004 by Don Bales on 2014-10-20
005 Type WORKER's attributes and methods.
006 */
007 id number(38),
008 worker_type_id number(38),
009 external_id varchar2(30),
010 first_name varchar2(30),
011 middle_name varchar2(30),
012 last_name varchar2(30),
013 name varchar2(100),
014 birth_date date,
015 gender_type_id number(38),
016 /*
017 Get the worker's current age.
018 */
019 MEMBER FUNCTION get_age
020 return number,
021 /*
022 Get the worker's age on the specified date.
023 */
024 MEMBER FUNCTION get_age(
025 aid_on date)
026 return number,
027 /*
028 Calculate a worker's age for the given birth date
029 and point in time.
030 */
031 STATIC FUNCTION get_age(
032 aid_birth_date in date,
033 aid_on in date)
034 return number,
035 /*
036 Calculate a worker's current age for the given bith date.
037 */
038 STATIC FUNCTION get_age(
039 aid_birth_date in date)
040 return number,
041 /*
042 Get the specified worker's age at the given point in time.
043 */
044 STATIC FUNCTION get_age(
045 ain_id in number,
046 aid_on in date)
047 return number,
048 /*
049 Get the specified worker's current age.
050 */
051 STATIC FUNCTION get_age(
052 ain_id in number)
053 return number,
054 /*
055 Get the specified worker's birth date.
056 */
057 STATIC FUNCTION get_birth_date(
058 ain_id in number)
059 return date,
060 /*
061 Get the specified worker's external ID.
062 */
063 STATIC FUNCTION get_external_id
064 return varchar2,
065 /*
066 Calculate the locale specific formatted name.
067 */
068 STATIC FUNCTION get_formatted_name(
069 aiv_first_name in varchar2,
070 aiv_middle_name in varchar2,
071 aiv_last_name in varchar2)
072 return varchar2,
073 /*
074 Get the specified worker's formatted name.
075 */
076 STATIC FUNCTION get_formatted_name(
077 ain_id in number)
078 return varchar2,
079 /*
080 Get the next primary key value for the table.
081 */
082 STATIC FUNCTION get_id
083 return number,
084 /*
085 Get the specified worker's internal ID.
086 */
087 STATIC FUNCTION get_id(
088 aiv_external_id in varchar2)
089 return number,
090 /*
091 Get the specified worker's row object.
092 */
093 STATIC FUNCTION get_row(
094 aio_worker in WORKER)
095 return WORKER,
096 /*
097 Calculate the non-locale specific unformmated name.
098 */
099 STATIC FUNCTION get_unformatted_name(
100 aiv_first_name in varchar2,
101 aiv_middle_name in varchar2,
102 aiv_last_name in varchar2)
103 return varchar2,
104 /*
105 Display the help text for this TYPE.
106 */
107 MEMBER PROCEDURE help,
108 /*
109 Check to see if a worker with the same name, birth_date and
110 gender already exists in the database.
111 */
112 STATIC FUNCTION is_duplicate(
113 aiv_name in varchar2,
114 aid_birth_date in varchar2,
115 ain_gender_type_id in varchar2)
116 return boolean,
117 /*
118 Set the specified worker's row object.
119 */
120 STATIC PROCEDURE set_row(
121 aioo_worker in out WORKER),
122 /*
123 Execute the test unit for this TYPE.
124 */
125 MEMBER PROCEDURE test,
126 /*
127 The MAP function for this TYPE.
128 */
129 MAP MEMBER FUNCTION to_varchar2
130 return varchar2,
131 /*
132 A convenience constructor for this TYPE.
133 */
134 CONSTRUCTOR FUNCTION worker(
135 self in out worker,
136 ain_worker_type_id in number,
137 aiv_first_name in varchar2,
138 aiv_middle_name in varchar2,
139 aiv_last_name in varchar2,
140 aid_birth_date in date,
141 ain_gender_type_id in number)
142 return self as result,
143 /*
144 A NULL values constructor for this TYPE.
145 */
146 CONSTRUCTOR FUNCTION worker(
147 self in out worker)
148 return self as result
149 );
150 /
151 @se.sql
Listing 6-11. The CREATE TABLE Script for Object Table WORKERS, workers.tab
01 rem worker_ot.tab
02 rem by Donald J. Bales on 2014-10-20
03 rem Create an object table for Workers
04
05 --drop table WORKERS;
06 create table WORKERS of WORKER;
07
08 --drop sequence WORKERS_ID;
09 create sequence WORKERS_ID
10 start with 1;
11
12 --drop sequence EXTERNAL_ID_SEQ;
13 create sequence EXTERNAL_ID_SEQ
14 start with 100000000 order;
15
16 alter table WORKERS add
17 constraint WORKERS_PK
18 primary key ( id )
19 using index;
20
21 alter table WORKERS add
22 constraint WORKERS_UK1
23 unique ( external_id )
24 using index;
25
26 alter table WORKERS add
27 constraint WORKERS_UK2
28 unique (
29 name,
30 birth_date,
31 gender_type_id )
32 using index;
33
34 alter table WORKERS add
35 constraint WORKERS_FK1
36 foreign key ( worker_type_id )
37 references WORKER_TYPES ( id );
38
39 alter table WORKERS add
40 constraint WORKERS_FK2
41 foreign key ( gender_type_id )
42 references GENDER_TYPES ( id );
43
44 execute SYS.DBMS_STATS.gather_table_stats(USER, 'WORKERS'),
Listing 6-12. The BODY Implementation Script for TYPE WORKER , worker.tpb
001 create or replace type body WORKER as
002 /*
003 worker.tpb
004 by Don Bales on 2014-10-20
005 TYPE WORKER's methods
006 */
007
008 MEMBER FUNCTION get_age(
009 aid_on in date)
010 return number is
011
012 begin
013 return WORKER.get_age(birth_date, aid_on);
014 end get_age;
015
016
017 MEMBER FUNCTION get_age
018 return number is
019
020 begin
021 return WORKER.get_age(birth_date, SYSDATE);
022 end get_age;
023
024
025 STATIC FUNCTION get_age(
026 aid_birth_date in date,
027 aid_on in date)
028 return number is
029
030 begin
031 if aid_birth_date is not NULL and
032 aid_on is not NULL then
033 return trunc(months_between(aid_on, aid_birth_date) / 12);
034 else
035 return NULL;
036 end if;
037 exception
038 when OTHERS then
039 return NULL;
040 end get_age;
041
042
043 STATIC FUNCTION get_age(
044 aid_birth_date in date)
045 return number is
046
047 begin
048 return WORKER.get_age(aid_birth_date, SYSDATE);
049 end get_age;
050
051
052 STATIC FUNCTION get_age(
053 ain_id in number,
054 aid_on in date)
055 return number is
056
057 begin
058 return WORKER.get_age(WORKER.get_birth_date(ain_id), aid_on);
059 end get_age;
060
061
062 STATIC FUNCTION get_age(
063 ain_id in number)
064 return number is
065
066 begin
067 return WORKER.get_age(WORKER.get_birth_date(ain_id));
068 end get_age;
069
070
071 STATIC FUNCTION get_birth_date(
072 ain_id in number)
073 return date is
074
075 d_birth_date date;
076
077 begin
078 select birth_date
079 into d_birth_date
080 from WORKERS
081 where id = ain_id;
082
083 return d_birth_date;
084 end get_birth_date;
085
086
087 STATIC FUNCTION get_external_id
088 return varchar2 is
089
090 v_external_id varchar2(30);
091
092 begin
093 select lpad(to_char(EXTERNAL_ID_SEQ.nextval), 9, '0')
094 into v_external_id
095 from SYS.DUAL;
096
097 return v_external_id;
098 end get_external_id;
099
100
101 STATIC FUNCTION get_id
102 return number is
103
104 n_id number;
105
106 begin
107 select WORKERS_ID.nextval
108 into n_id
109 from SYS.DUAL;
110
111 return n_id;
112 end get_id;
113
114
115 STATIC FUNCTION get_id(
116 aiv_external_id in varchar2)
117 return number is
118
119 n_id number;
120
121 begin
122 select id
123 into n_id
124 from WORKERS
125 where external_id = aiv_external_id;
126
127 return n_id;
128 end get_id;
129
130
131 STATIC FUNCTION get_formatted_name(
132 aiv_first_name in varchar2,
133 aiv_middle_name in varchar2,
134 aiv_last_name in varchar2)
135 return varchar2 is
136
137 begin
138 return aiv_last_name||', '||aiv_first_name||' '||aiv_middle_name;
139 end get_formatted_name;
140
141
142 STATIC FUNCTION get_formatted_name(
143 ain_id in number)
144 return varchar2 is
145
146 v_first_name varchar2(30);
147 v_middle_name varchar2(30);
148 v_last_name varchar2(30);
149
150 begin
151 select first_name,
152 middle_name,
153 last_name
154 into v_first_name,
155 v_middle_name,
156 v_last_name
157 from WORKERS
158 where id = ain_id;
159
160 return get_formatted_name(
161 v_first_name,
162 v_middle_name,
163 v_last_name);
164 end get_formatted_name;
165
166
167 STATIC FUNCTION get_row(
168 aio_worker in WORKER)
169 return WORKER is
170
171 o_worker WORKER;
172
173 begin
174 if aio_worker.id is not NULL then
175 -- retrieve the row by the primary key
176 select value(w)
177 into o_worker
178 from WORKERS w
179 where id = aio_worker.id;
180 elsif aio_worker.external_id is not NULL then
181 -- retrieve the row by the external unique key
182 select value(w)
183 into o_worker
184 from WORKERS w
185 where external_id = aio_worker.external_id;
186 else
187 -- retrieve the row by the name, birth_date, and gender
188 select value(w)
189 into o_worker
190 from WORKERS w
191 where name = worker.get_formatted_name(
192 aio_worker.first_name,
193 aio_worker.middle_name,
194 aio_worker.last_name)
195 and birth_date = aio_worker.birth_date
196 and gender_type_id = aio_worker.gender_type_id;
197 end if;
198 return o_worker;
199 exception
200 when NO_DATA_FOUND then
201 raise;
202 when OTHERS then
203 raise_application_error(-20001, SQLERRM||
204 ' on select WORKERS'||
205 ' in WORKER.get_row()'),
206 end get_row;
207
208
209 STATIC FUNCTION get_unformatted_name(
210 aiv_first_name in varchar2,
211 aiv_middle_name in varchar2,
212 aiv_last_name in varchar2)
213 return varchar2 is
214
215 begin
216 return upper(replace(replace(replace(replace(replace(
217 aiv_last_name||aiv_first_name||aiv_middle_name,
218 '''', NULL), ',', NULL), '-', NULL), '.', NULL), ' ', NULL));
219 end get_unformatted_name;
220
221
222 STATIC FUNCTION is_duplicate(
223 aiv_name in varchar2,
224 aid_birth_date in varchar2,
225 ain_gender_type_id in varchar2)
226 return boolean is
227
228 n_selected number;
229
230 begin
231 execute immediate
232 'select count(1)
233 from WORKERS
234 where name = aiv_name
235 and birth_date = aid_birth_date
236 and gender_type_id = ain_gender_type_id'
237 into n_selected
238 using in aiv_name,
239 in aid_birth_date,
240 in ain_gender_type_id;
241
242 if nvl(n_selected, 0) > 0 then
243 return TRUE;
244 else
245 return FALSE;
246 end if;
247 end is_duplicate;
248
249
250 MEMBER PROCEDURE help is
251
252 begin
253 -- 12345678901234567890123456789012345678901234567890123456789012345678901234567890
254 pl('================================== PACKAGE ==================================='),
255 pl(chr(9));
256 pl('WORKER'),
257 pl(chr(9));
258 pl('---------------------------------- FUNCTIONS ---------------------------------'),
259 pl(chr(9));
260 pl('YOU GOTTA CODE THIS BUDDY WORKER.get_id'),
261 pl('return number;'),
262 pl(chr(9)||'Returns a newly allocated sequence value for id.'),
263 pl(chr(9));
264 pl('WORKER.get_id('),
265 pl('aiv_external_id in varchar2 )'),
266 pl('return number;'),
267 pl(chr(9)||'Returns the corresponding id for the specified external_id.'),
268 pl(chr(9));
269 pl('---------------------------------- PROCEDURES --------------------------------'),
270 pl(chr(9));
271 pl('WORKER.get_external_id_descr('),
272 pl('ain_id in number,'),
273 pl('aov_external_id out varchar2,'),
274 pl('aov_description out WORKERS.description%TYPE );'),
275 pl(chr(9)||'Gets the corresponding external_id and description for the specified'),
276 pl(chr(9)||'id.'),
277 pl(chr(9));
278 pl('WORKER.get_external_id_id_descr('),
279 pl('aiov_external_id in out varchar2,'),
280 pl('aon_id out number,'),
281 pl('aov_description out WORKERS.description%TYPE,'),
282 pl('aid_on in WORKERS.active%TYPE );'),
283 pl(chr(9)||'Gets the corresponding external_id, id, and description for'),
284 pl(chr(9)||'the specified external_id. First it trys to find an exact match. If one'),
285 pl(chr(9)||'cannot be found, it trys to find a like match. It may throw a'),
286 pl(chr(9)||'NO_DATA_FOUND or a TOO_MANY_ROWS exception if a match cannot be'),
287 pl(chr(9)||'found for the specified external_id and point in time.'),
288 pl(chr(9));
289 pl('WORKER.get_external_id_id_descr('),
290 pl('aiov_external_id in out varchar2,'),
291 pl('aon_id out number,'),
292 pl('aov_description out WORKERS.description%TYPE );'),
293 pl(chr(9)||'Gets the corresponding external_id, id, and description for'),
294 pl(chr(9)||'the specified external_id. First it trys to find an exact match. If one'),
295 pl(chr(9)||'cannot be found, it trys to find a like match. It may throw a'),
296 pl(chr(9)||'NO_DATA_FOUND or a TOO_MANY_ROWS exception if a match cannot be'),
297 pl(chr(9)||'found for the specified external_id at the current point in time.'),
298 pl(chr(9));
299 pl('WORKER.help( );'),
300 pl(chr(9)||'Displays this help text if set serveroutput is on.'),
301 pl(chr(9));
302 pl('WORKER.test( );'),
303 pl(chr(9)||'Built-in test unit. It will report success or error for each test if set'),
304 pl(chr(9)||'serveroutput is on.'),
305 pl(chr(9));
306 end help;
307
308
309 STATIC PROCEDURE set_row(
310 aioo_worker in out WORKER) is
311
312 d_null constant date := DATE_.d_MIN;
313 n_null constant number := 0;
314 v_null constant varchar2(1) := ' ';
315 o_worker WORKER;
316
317 begin
318 -- set the formatted name
319 aioo_worker.name := worker.get_formatted_name(
320 aioo_worker.first_name,
321 aioo_worker.middle_name,
322 aioo_worker.last_name);
323 -- get the existing row
324 begin
325 o_worker := get_row(aioo_worker);
326 exception
327 when NO_DATA_FOUND then
328 o_worker := NULL;
329 end;
330 -- if a row exists, update it if needed
331 if o_worker is not NULL then
332 aioo_worker.id := o_worker.id;
333 if nvl(o_worker.worker_type_id, n_null) <>
334 nvl(aioo_worker.worker_type_id, n_null) or
335 nvl(o_worker.external_id, n_null) <>
336 nvl(aioo_worker.external_id, n_null) or
337 nvl(o_worker.first_name, v_null) <>
338 nvl(aioo_worker.first_name, v_null) or
339 nvl(o_worker.middle_name, v_null) <>
340 nvl(aioo_worker.middle_name, v_null) or
341 nvl(o_worker.last_name, v_null) <>
342 nvl(aioo_worker.last_name, v_null) or
343 nvl(o_worker.birth_date, d_null) <>
344 nvl(aioo_worker.birth_date, d_null) or
345 nvl(o_worker.gender_type_id, n_null) <>
346 nvl(aioo_worker.gender_type_id, n_null) then
347 begin
348 update WORKERS
349 set worker_type_id = aioo_worker.worker_type_id,
350 external_id = aioo_worker.external_id,
351 first_name = aioo_worker.first_name,
352 middle_name = aioo_worker.middle_name,
353 last_name = aioo_worker.last_name,
354 name = aioo_worker.name,
355 birth_date = aioo_worker.birth_date,
356 gender_type_id = aioo_worker.gender_type_id
357 where id = aioo_worker.id;
358
359 -- n_updated := nvl(n_updated, 0) + nvl(sql%rowcount, 0);
360 exception
361 when OTHERS then
362 raise_application_error( -20002, SQLERRM||
363 ' on update WORKERS'||
364 ' in WORKER.set_row()' );
365 end;
366 end if;
367 else
368 -- add the row if it does not exist
369 begin
370 aioo_worker.id := get_id();
371 insert into WORKERS
372 values ( aioo_worker );
373
374 -- n_inserted := nvl(n_inserted, 0) + nvl(sql%rowcount, 0);
375 exception
376 when OTHERS then
377 raise_application_error( -20003, SQLERRM||
378 ' on insert WORKERS'||
379 ' in WORKER.set_row()' );
380 end;
381 end if;
382 end set_row;
383
384
385 MEMBER PROCEDURE test(
386 self in out nocopy worker) is
387
388 begin
389 pl('================================== PACKAGE ==================================='),
390 pl(chr(9));
391 pl('WORKER'),
392 pl(chr(9));
393 pl(chr(9)||'No tests for WORKER at this time'),
394 end test;
395
396
397 MAP MEMBER FUNCTION to_varchar2
398 return varchar2 is
399
400 begin
401 return rtrim(name||to_char(birth_date, 'YYYYMMDDHH24MISS'));
402 end to_varchar2;
403
404
405 CONSTRUCTOR FUNCTION worker(
406 self in out worker,
407 ain_worker_type_id in number,
408 aiv_first_name in varchar2,
409 aiv_middle_name in varchar2,
410 aiv_last_name in varchar2,
411 aid_birth_date in date,
412 ain_gender_type_id in number)
413 return self as result is
414
415 begin
416 id := WORKER.get_id();
417 worker_type_id := ain_worker_type_id;
418 external_id := WORKER.get_external_id();
419 first_name := aiv_first_name;
420 middle_name := aiv_middle_name;
421 last_name := aiv_last_name;
422 name := WORKER.get_formatted_name(
423 first_name, middle_name, last_name);
424 birth_date := aid_birth_date;
425 gender_type_id := ain_gender_type_id;
426 return;
427 end worker;
428
429
430 CONSTRUCTOR FUNCTION worker(
431 self in out worker)
432 return self as result is
433
434 begin
435 id := NULL;
436 worker_type_id := NULL;
437 external_id := NULL;
438 first_name := NULL;
439 middle_name := NULL;
440 last_name := NULL;
441 name := NULL;
442 birth_date := NULL;
443 gender_type_id := NULL;
444 return;
445 end worker;
446
447
448 end; --WORKER;
449 /
450 @be.sql WORKER
Note Make sure you execute scripts gender_types.ins and worker_types.ins before executing workers.ins (Listing 6-13); otherwise, the script will be missing required code values.
Listing 6-13. The Populate Table Script for Object Table WORKERS, workers.ins
01 rem workers.ins
02 rem by Donald J. Bales on 2014-10-20
03 rem Seed the Worker table with the top 100 names
04 rem 100 last x 100 first x 26 middle = 260,000 entries
05
06 set serveroutput on size 1000000;
07
08 declare
09
10 -- This is the number of seconds since midnight
11 -- I'll use it to profile my code's performance.
12 n_start number :=
13 to_number(to_char(SYSDATE, 'SSSSS'));
14
15 -- Here, I declare four psuedo-constants to hold the
16 -- ID values from the code tables, rather `than look
17 -- them up repeatedly during the insert process.
18 n_G_FEMALE GENDER_TYPES.id%TYPE;
19 n_G_MALE GENDER_TYPES.id%TYPE;
20 n_WT_CONTRACTOR WORKER_TYPES.id%TYPE;
21 n_WT_EMPLOYEE WORKER_TYPES.id%TYPE;
22
23 -- I'll use this to keep track of the number of
24 -- rows inserted.
25 n_inserted number := 0;
26
27 begin
28 -- Get the ID values for the codes
29 n_G_FEMALE := GENDER_TYPE.get_id('F'),
30 n_G_MALE := GENDER_TYPE.get_id('M'),
31 n_WT_CONTRACTOR := WORKER_TYPE.get_id('C'),
32 n_WT_EMPLOYEE := WORKER_TYPE.get_id('E'),
33
34 -- Use an INSERT INTO SELECT SQL statement
35 insert into WORKERS
36 select WORKER(
37 WORKERS_ID.nextval,
38 decode(mod(WORKERS_ID.currval, 2),
39 0, n_WT_EMPLOYEE, n_WT_CONTRACTOR),
40 lpad(to_char(EXTERNAL_ID_SEQ.nextval), 9, '0'),
41 first_name,
42 letter||'.',
43 last_name,
44 WORKER.get_formatted_name(
45 first_name, letter||'.', last_name),
46 DATE_.random(
47 to_number(to_char(SYSDATE, 'YYYY')) - 65,
48 to_number(to_char(SYSDATE, 'YYYY')) - 18),
49 decode(gender_code, 'F', n_G_FEMALE, n_G_MALE))
50 from RPS.TOP_100_LAST_NAMES,
51 RPS.TOP_100_FIRST_NAMES,
52 RPS.A_THRU_Z;
53
54 n_inserted := n_inserted + sql%rowcount;
55
56 commit;
57
58 pl(to_char(n_inserted)||' rows inserted in '||
59 (to_number(to_char(SYSDATE, 'SSSSS')) - n_start)||
60 ' seconds.'),
61 end;
62 /
63
64 execute SYS.DBMS_STATS.gather_table_stats(USER, 'WORKERS'),
If you compare the three object tables—GENDER_TYPES, WORKER_TYPES, and WORKERS—against the class diagram in Figure 6-1, you’ll see that you’ve fully implemented both the attributes and methods of the classes: GENDER_TYPE, WORKER_TYPE, and WORKER in the database using user-defined TYPEs. Congratulations.
Is there a performance penalty for using object-relational technology? Sure there is! You didn’t think you could do extra work, that is, instatiating classes (TYPES) for free did you? How much of a performance penalty? Well, workers.ins inserted 260,000 objects in 126 seconds. That’s 2063 objects per second, or about six times slower than its relational counterpart. Is it worth the extra functionality? I think so, but now it’s up to you.
Summary
To sum it all up, the real world is made up of things, or objects. Objects have both attributes and behaviors. Using attributes alone, you can answer only the question “What was that?” But with behaviors, you can also answer the question “When did it behave that way?” So you decide which way to go. Try one, then the other!
Table 6-1 is a summary of the SQL and PL/SQL objects that are used in order to fully model a business, scientific, or any real-world problem accurately.
Table 6-1. A Summary of SQL and PL/SQL Objects Used to Model the Real World
Item | Data | Behavior |
|---|---|---|
Relational table (SQL) | Columns | |
Relational view (SQL) | Columns | |
Table package (PL/SQL) | Methods | |
User-defined type (SQL and PL/SQL) | Attributes | Methods |
Object view (SQL) | Columns mapped to attributes | Methods |
Object table (SQL) | Attributes | Methods |
The single most important point of this chapter is that you can’t accurately model anything without attributes and behavior, or data and methods. And as the saying goes, “Those who cannot remember the past are condemned to repeat it.” We, the business and technical community, can now accurately model the real world. So we can remember the past and no longer repeat our mistakes. We can profit from our knowledge, the information we have gathered, as we all move through time and space.
Now that you know how to write some PL/SQL, let’s look at how you can see what’s going on as your PL/SQL program executes.