Oracle 8: The Complete Reference

Datatypes Abstract datatypes are datatypes that consist of one or more subtypes. Rather than being constrained to the standard Oracle datatypes of NUMBER, DATE, and VARCHAR2, abstract datatypes can more accurately describe your data. For example, an abstract datatype for addresses may consist of the following columns: Street VARCHAR2(50) City VARCHAR2(25) State CHAR(2) Zip NUMBER When you create a table that uses address information, you could create a column that uses the abstract datatype for addresses—and thus contains the Street, City, State, and Zip columns that are part of that datatype. Abstract datatypes can be nested; they can contain references to other abstract datatypes. You will see a detailed example of nested abstract datatypes in the next section of this chapter. Two of the benefits listed earlier for objects—reuse and standards adherence— are realized from using abstract datatypes. When you create an abstract datatype, you create a standard for the representation of abstract data elements (such as addresses, people, or companies). If you use the same abstract datatype in multiple places, then you can be sure that the same logical data is represented in the same manner in those places. The reuse of the abstract datatype leads to the enforcement of standard representation for the data. You can use abstract datatypes to create object tables. In an object table, the columns of the table map to the columns of the abstract datatype. Nested Tables A nested table is a table within a table. A nested table is a collection of rows, represented as a column within the main table. For each record within the main table, the nested table may contain multiple rows. In one sense, it’s a way of storing a one-to-many relationship within one table. Consider a table that contains information about departments, each of which may have many projects in progress at any one time. In a strictly relational model, you would create two separate tables—DEPARTMENT and PROJECT. Nested tables allow you to store the information about projects within the DEPARTMENT table. The PROJECT table records can be accessed directly via the DEPARTMENT table, without the need to perform a join. The ability to select the data without traversing joins may make the data easier to access for users. Chapter 4: The Basics of Object-Relational Databases 73 ORACLE Series / Oracle 8i: TCR / Loney / 2364-8 / Chapter 4 Blind Folio 4:73 C:\work\CD\Book.vp Wednesday, April 12, 2000 3:56:10 PM Color profile: Disabled Composite Default screen