Table of Contents |
As of this writing, Oracle8 is finally in beta and due to be released any day. Chances are that Oracle8 will have been released by the time you read this text. Hopefully, the information contained in this appendix will give you a step up on understanding the drastic changes accompanying Oracle8. You ve almost certainly heard by now that Oracle8 supports the object concept, but there are other important changes, as well.
Any rumors you ve heard about people seeing this information with glazed eyes are entirely true. In some ways, the changes between Oracle7 and Oracle8 are more radical than the changes that occurred between Oracle6 and Oracle7. This appendix outlines some of the more striking changes implemented in Oracle8.
Oracle8 introduces several new datatypes. Some of these datatypes are used to support the object-relational database model. In this model, data structures are based on object-oriented techniques for representing the real world. Relational operations are still usable in this model, meaning that all Oracle7 databases are forward compatible with Oracle8.
Several other new datatypes serve a variety of purposes, including:
Obviously, these are radical new types of constructs for an Oracle database. We ll discuss each new type individually. In addition to these new datatype definitions, the concept of a ROWID has been altered , as well.
In Oracle8, an object is a self-contained construction that holds information about real-world data and the operations that can be performed on the data. Information about the data is stored in attributes, while the operations that can be performed on the data are methods .
An attribute is simply a declaration of an element of data using one of Oracle8 s recognized datatypes ( include objects, varray s, and large object datatypes). A method is a code module that operates against one or more attributes of the object.
For instance, let s say you re trying to represent a student as an object. There are certain characteristics common to every student. Some of these characteristics might be as follows :
Obviously, this isn t a complete list, but it will do for the sake of this example. All of these elements would be attributes of a student object. Looking at the preceding list, it s likely that some attributes can be broken down further, like the address. An address has the following components :
Each individual student object will also have certain operations that have to be performed, such as:
This list could go on forever. But, at this point, we have enough information to present Listing E.1, which shows what a complex object declaration for a student might look like in Oracle8.
Listing E.1 A complex object declaration in Oracle8.
CREATE TYPE Address_TYPE AS object (street_name_or_route varchar2 (40), house_or_box_number integer, city_name varchar2 (30), state_code varchar2 (2), zip_code integer); CREATE TYPE Degree_TYPE AS object (department_name varchar2 (30), degree_level integer, degree_name varchar2 (30), degree_field varchar2 (40)); CREATE TYPE Housing_TYPE AS object (dormitory_name varchar2 (30), occupancy_code varchar2 (1), room_cost number (6,2)); CREATE TYPE Student_TYPE AS object (first_name varchar2 (20), middle_name varchar2 (20), last_name varchar2 (30), ssn varchar2 (9), address Address_TYPE, application_date date, acceptance_date date, gpa number (3,2), degree_plan Degree_TYPE, father_name varchar2 (60), mother_name varchar2 (60), mother_maiden_name varchar2 (20), account_number integer, housing_assignment Housing_TYPE, MEMBER FUNCTION Get_SSN RETURN varchar2, MEMBER FUNCTION Calculate_GPA RETURN number, MEMBER FUNCTION Award_Financial_Aid RETURN number, MEMBER FUNCTION Calculate_Account_Interest RETURN number);
Storing this information in a straight relational model would require the definition of several tables. Now, all that s needed is a single STUDENTS table with the following definition:
CREATE TABLE STUDENTS OF Student_TYPE;
Every row created in the STUDENTS table is an object of the type Student_TYPE and has all of the attributes and methods in Listing E.1. The INSERT statement that creates a new student would look like this:
INSERT INTO STUDENTS VALUES ('John', 'Joseph', 'Doe', '999999999', address('North Main Street', 23, 'Philadelphia', 'PA', '45032'), SYSDATE, SYSDATE, NULL, degree_plan(), 'William Robinson Doe', 'Jane Elizabeth Doe', 'Martin', ACCOUNT_SEQ.NEXTVAL, housing_assignment('Elam Arms', 'S', 795.43));
Every attribute of the defined type can be given constraints ( NOT NULL constraints, DEFAULT value constraints, and so forth). These attributes can also be indexed like a column in a table.
The PL/SQL functions Get_SSN() , Calculate_GPA() , Award_Financial_Aid() , and Calculate_Account_Interest() are all methods in this example. Keep in mind that a method isn t restricted to being a PL/SQL procedure or function. Methods can also be calls to a library of objects stored in the database and written in a language like C or C++. The functionality of these methods is defined (or referenced) in the type body.
The varray datatype enables you to create structures that are essentially arrays, without having to deal with the crude limitations of PL/SQL tables. Like object datatypes, a varray is a datatype declaration, not a variable declaration. A declaration of a varray might look like this:
CREATE TYPE Name_Array_TYPE AS varray (10) OF varchar2 (60)
You must declare a maximum size for the datatype when it is declared. As with a PL/SQL table, referencing a NULL or nonexistent element of a varray variable causes a NO_DATA_FOUND exception to be raised. Elements in the variable are referenced like the elements of a PL/SQL table. For instance:
Names_array (7)
Using a varray type, you could easily implement a multivalue column in a single row of a table. For example:
CREATE TYPE Courses_Array_TYPE as varray (9) OF number; CREATE TABLE STUDENTS (first_name varchar2 (20), middle_name varchar2 (20), last_name varchar2 (20), ssn varchar2 (9), address Address_TYPE, application_date date, acceptance_date date, gpa number (3,2), degree_plan Degree_TYPE, father_name varchar2 (60), mother_name varchar2 (60), mother_maiden_name varchar2 (20), account_number integer, housing_assignment Housing_TYPE, courses Courses_Array_TYPE);
This defines a row in the STUDENTS table that stores course information. Each student could have as many as nine courses, all contained within a single row in the STUDENTS table.
Oracle8 implements four new large object datatypes:
Each of these datatypes is intended for a specific use, but there are certain characteristics that these datatypes all have in common:
For instance, the following is a valid table definition in Oracle8:
employee_num number first_name varchar2 (20) middle_name varchar2 (20) last_name varchar2 (20) photo bfile resume bfile
This table ” EMPLOYEES ”contains two columns with a datatype of bfile . These files are not stored within the database but reside on the file system at the OS level. For instance, the resume column might always point to an employee s resume that is continually updated in Microsoft Word.
Values of these new datatypes are always populated with a LOB locator , which is comparable to a pointer. Actual data is not stored in the table but at another location inside the database. In the case of data with type bfile , the actual data is a file maintained outside of Oracle by the host operating system.
With the exception of the nclob datatype, you may also include multiples of these datatypes in object types that you create. To manipulate objects of the LOB datatypes, the DBMS_LOB package has been provided.
A nested table is actually a table that is a column in another table. Consider again the sample student object presented earlier in this appendix. Instead of defining the datatype Address_TYPE , address information could have been stored in the STUDENTS table like this:
CREATE TABLE STUDENTS (student Student_TYPE, address_list ADDRESSES_TABLE)
Using this table definition, a single student could have multiple addresses. As with the varray datatypes, this datatype could be used to implement a multivalue column in a row of data. The nested table method has the advantage of not requiring a maximum number of values for the column.
Oracle8 has a new pseudocolumn called an extended ROWID . This pseudocolumn contains all the elements of the ROWID from Oracle7, plus a data object number that uniquely identifies a particular segment. An Oracle7 ROWID is now referred to as a restricted ROWID .
The DBMS_ROWID Package
Oracle8 introduces the DBMS_ROWID package, which contains functions that operate on the ROWID and extended ROWID pseudocolumns . Among the functions provided in this package are functions that can convert ROWID values between the restricted and extended formats.
Obviously, such drastic changes to the supported datatypes require changes to SQL*Plus and PL/SQL to support the new datatypes. The following sections take a brief look as some of the changes made to SQL*Plus and PL/SQL.
In addition to allowing columns of the new datatypes to be defined, Oracle has added several commands to be used with the new datatypes:
In addition to these new commands, Oracle8 offers some new built-in functions and object views. While a complete discussion of the new commands isn t necessary, you may want to know a little more about some new built-in functions and a new type of object called an object view.
New Built-In Functions
Oracle8 has added several new built-in trigonometric functions. These functions are listed in Table E.1.
| |
---|---|
Function | Purpose |
acos() | Returns the arc cosine of a parameter, n , in radians. |
asin() | Returns the arc sine of a parameter, n , in radians. |
atan() | Returns the arc tangent of a parameter, n , in radians. |
atan2() | Returns the arc tangent of two parameters, x and y , in radians. |
|
Object Views
An object view is a construct implemented in Oracle8 to ease the migration from the relational database to the object-relational database. Object views allow you to query and manipulate relational data as if the data were object data.
PL/SQL fully supports all the new Oracle8 datatypes. For instance, you may declare object and varray types in your functions, procedures, and packages just like variables of any other datatype.
This appendix hasn t covered everything you need to know about Oracle8, nor could it do so without taking an enormous amount of space. Instead, this appendix provides an overview of some of the new concepts contained in Oracle8. Just like Oracle7, Oracle8 promises to revolutionize the way databases are created using Oracle.
Table of Contents |