Oracle PL/SQL Programming, Third Edition

An Extended Example

I’d like to point out that this example—indeed, most of this chapter—relies heavily on features introduced in Oracle9i. In a number of areas, this section focuses on features available only in Oracle9i Release 2. If you really want server-side object orientation, you probably won’t want to use any version earlier than Oracle9i anyway, and you’ll probably agree that ignoring the OO-challenged earlier versions is no great sacrifice.

A Tree of Types

In keeping with the sample general application area we explored in our introductory book, Learning Oracle PL/SQL Programming (O’Reilly), I’d like to build an Oracle system that will use an object-oriented approach to modeling a trivial library catalog. The catalog can hold books, serials (such as magazines, proceedings, or newspapers), and, eventually, other artifacts.

A graphic portrayal of the top-level types appears in Figure 21-1. Later on, we might want to add to the type hierarchy, as the dotted-line boxes imply.

Figure 21-1. Type hierarchy for a trivial library catalog

Creating a base type

The “root” or top of the hierarchy represents the common characteristics of all the subtypes. For now, let’s assume that the only things that books and serials have in common are a library-assigned identification number and some kind of filing title. We can create an object type for catalog items using the following SQL statement ...