Order Methods

Order methods are used for specialized ordering. They can perform almost any type of calculation to determine the ordering of objects. The order method looks at two objects at a time. Values from each object are used to determine a return value. The order method is much slower than a map method because the database must compare each object to all the other objects. Listing 15.4 ranks employees by salary. It looks at the salary of each employee and compares it to the salary of all other employees. If the difference between the two salaries is less than $10,000, the function considers both employees to be equal.

Listing 15.4. Using an order method to compare employee salaries.

CREATE OR REPLACE TYPE employee_type

AS OBJECT(

employee_id number,

first_name first_name_type,

last_name last_name_type,

address address_type,

date_of_hire date,

rank number,

salary number,

ORDER MEMBER FUNCTION EmployeeOrderFunction(my_input IN employee_type)

 RETURN NUMBER

);

/



CREATE OR REPLACE TYPE BODY employee_type AS

ORDER MEMBER FUNCTION EmployeeOrderFunction (my_input IN employee_type)

RETURN NUMBER IS

    BEGIN

        IF (ABS(salary - my_input.salary)) < 10000  THEN

            RETURN 0;

        ELSE

            RETURN salary - my_input.salary;

        END IF;

    END;

END;

/

The new object ordering methods do not replace the old ways of ordering. Oracle still has the ability to sort on the attributes of an object. Attribute ordering is still the most common type of ordering in applications. Object ordering, however, allows the designer to imply ordering to the user. The user may choose to order based on an attribute (or attributes) or may use the object-ordering methods. The object-ordering methods are invoked with the ORDER BY VALUE() clause. Listing 15.5 orders the employee table by using the order method. The table name must have an alias for the VALUE parameter.

Listing 15.5. Ordering the Employee table by using the order method.

CREATE TABLE employee OF employee_type;

INSERT INTO employee VALUES (101,

first_name_type(‘Marti’),last_name_type(‘Hester’), address_type

(‘101 Oak St.’,’Denver’,’CO’,’USA’,’Mail Drop 32’,’Moving in six

 months’),to_date(‘01-86’,’MM-YY’),1,20000);

INSERT INTO employee VALUES (102,

first_name_type(‘George’),last_name_type(‘Smith’), address_type(‘3

Lincoln St.’,’NewYork’,’NY’,’USA’,null,null),to_date(‘01-94’,’MM-YY’)

,1,40000);

SELECT * FROM employee e ORDER BY VALUE;

SQL> SELECT * FROM employee e ORDER BY VALUE;



EMPLOYEE_ID

FIRST_NAME(FIRST_NAME)

LAST_NAME(LAST_NAME)

ADDRESS(STREET, CITY, STATE, COUNTRY_CODE, MISC_MAIL_INFO,

MISC_NON_MAIL_INFO)

DATE_OF_H      RANK    SALARY



        101

FIRST_NAME_TYPE(‘Marti’)

LAST_NAME_TYPE(‘Hester’)

ADDRESS_TYPE(‘101 Oak St.’, ‘Denver’, ‘CO’, ‘USA’, ‘Mail Drop 32’, ‘

 Moving in six months’)

01-JAN-86         1     20000



        102

FIRST_NAME_TYPE(‘George’)

LAST_NAME_TYPE(‘Smith’)

ADDRESS_TYPE(‘3 Lincoln St.’, ‘New York’, ‘NY’, ‘USA’, NULL, NULL)

01-JAN-94         1     40000

Type Versus Instantiation of a Type

It is important to understand the difference between a type and the instantiation of a type. The CREATE TYPE and CREATE TYPE BODY statements do not create objects in the database. Instead, they create a blueprint for future objects. A type is similar to the standard datatypes.

•  number

•  varchar2

•  char

•  date

•  long

•  long raw

•  and others…

The following example will clarify. Consider a university that uses Oracle8. Figure 15.3 shows the types that are used as building blocks for the course and professor types.

Figure 15.3.  Course and Professor types based on other building block types.

In order to create the book and author types, the building block types must first be created. The designer must start at the lowest level of types and work up (see Listing 15.6).

Listing 15.6. Building-block types.

— This is the lowest level sub-type in this diagram.

— It must be created before the types above it.

CREATE OR REPLACE TYPE address_type

AS OBJECT(

street varchar2(200),

city varchar2(200),

state char(2),

zip varchar2(20)

);

/



CREATE OR REPLACE TYPE professor_type

AS OBJECT(

department varchar2(20),

first_name varchar2(200),

last_name varchar2(200),

address address_type  —The address type is used from above.

);

/



CREATE OR REPLACE TYPE course_type

AS OBJECT(

course_number number,

description varchar(255),

number_of_credits number,

professor REF professor_type  —The professor type is used

);

/





CREATE TABLE professor_table OF professor_type;





CREATE TABLE course_table OF course_type

(professor with rowid scope is professor_table); 

Notice that none of the subtypes were ever used directly in a CREATE TABLE statement. Only course_type and professor_type were used to create physical objects. However, the tables are fully dependent on all the building-block types. Although the creation of type can be complicated, the creation of the tables becomes amazingly simple. The details of the underlying type are hidden from the person creating the table.

This listing also shows an example of REFs.