The ORDER method

The alternative to MAP is an ORDER member function, which compares two methods: SELF, and another object of the same type that you supply as an argument. You want to program the function to return an integer that is positive, zero, or negative, indicating the ordering relationship of the second object to SELF. Table 21-2 illustrates the behavior you need to incorporate.

Table 21-2. Desired behavior of ORDER member functions
For these desired semantics...	Your ORDER member function must return
SELF < argumentObject	Any negative number (typically -1)
SELF = argumentObject
SELF > argumentObject	Any positive number (typically +1)
Undefined comparison	NULL

Let's take a look at a nontrivial example of an ORDER method.

1 ALTER TYPE catalog_item_t 2 DROP MAP MEMBER FUNCTION mapit RETURN NUMBER 3 CASCADE; 4 5 ALTER TYPE catalog_item_t 6 ADD ORDER MEMBER FUNCTION orderit (obj2 IN catalog_item_t) 7 RETURN INTEGER 8 CASCADE; 9 10 CREATE OR REPLACE TYPE BODY catalog_item_t 11 AS ... 12 ORDER MEMBER FUNCTION orderit (obj2 IN catalog_item_t) 13 RETURN INTEGER 14 IS 15 self_gt_o2 CONSTANT PLS_INTEGER := 1; 16 eq CONSTANT PLS_INTEGER := 0; 17 o2_gt_self CONSTANT PLS_INTEGER := -1; 18 l_matching_count NUMBER; 19 BEGIN 20 CASE 21 WHEN obj2 IS OF (book_t) AND SELF IS OF (serial_t) THEN 22 RETURN o2_gt_self; 23 WHEN obj2 IS OF (serial_t) AND SELF IS OF (book_t) THEN 24 RETURN self_gt_o2; 25 ELSE 26 IF obj2.title = SELF.title 27 AND obj2.publication_date = SELF.publication_date 28 THEN 29 IF obj2.subject_refs IS NOT NULL 30 AND SELF.subject_refs IS NOT NULL 31 AND obj2.subject_refs.COUNT = SELF.subject_refs.COUNT 32 THEN 33 SELECT COUNT(*) INTO l_matching_count FROM 34 (SELECT * 35 FROM TABLE(SELECT CAST(SELF.subject_refs AS subject_refs_t) 36 FROM dual) 37 INTERSECT 38 SELECT * 39 FROM TABLE(SELECT CAST(obj2.subject_refs AS subject_refs_t) 40 FROM dual)); 41 IF l_matching_count = SELF.subject_refs.COUNT 42 THEN 43 RETURN eq; 44 END IF; 45 END IF; 46 END IF; 47 RETURN NULL; 48 END CASE; 49 END; 50 ... 51 END;

Here are the important things to note:

Lines 21-24

This means that "books sort higher than serials."

Lines 26-46

This is an equality test that uses a very cool feature. Because Oracle doesn't know how to compare collections, this code uses Oracle's ability to select from a collection as if it were a table. By checking to make sure that the relational intersection of these two collections has the expected number of elements, I can determine whether every element in the first collection has an equal counterpart in the second (which is my definition of "equality").

Overall, however, my ORDER method is still inadequate because it fails to treat the subtype-specific attributes, but anything longer would just be too unwieldy for this book.