Comparing user-defined objects, part 2

Continued from Part 1...

With a map function you can:

• compare objects in PL/SQL
• use >, >=, < and <=
• ORDER BY the object
• deal with NULL members intelligently
• make your own rules about comparisons

Order functions provide the same benefits, but they are usually slower than map functions. An order function takes a single argument (another instance of your TYPE) and compares it to SELF, returning

• a negative number if SELF is "less than" the argument
• 0 if they are considered equal
• a positive number if SELF is "greater than" the argument

CREATE OR REPLACE TYPE  rec_mgach
AS OBJECT
(
    gach_id         NUMBER (10),
    gach_dt_modif   DATE,
    mgach_bat_plais VARCHAR2 (2),
    ORDER MEMBER FUNCTION  compare 
    (
        in_mgach  IN  rec_mgach
    )
    RETURN PLS_INTEGER
);

CREATE OR REPLACE TYPE BODY  rec_mgach
AS

-- *********************
-- **  c o m p a r e  **
-- *********************

--  compare returns compares two rec_mgach 
--  objects and returns
--    -1 if the SELF is considered "less than" 
--       the argument
--     0 if they are equal
--    +1 if SELF is "greater than" the argument

--  The rules are
--    (1) Compare mgach_bat_plais first, using normal 
--        (case-sensitive) string comparison
--    (2) In case of a tie, compare gach_dt_modif
--        (date only, ignore time of day)
--    (3) gach_id does not count in comparisons
--  In all cases, NULL values are considered "less" than any non-NULL value.

ORDER MEMBER FUNCTION  compare 
(
    in_mgach  IN  rec_mgach
)
RETURN PLS_INTEGER
IS
    othr_dt     DATE;    -- Date component of in_mgach
    return_val  PLS_INTEGER := 0;
    self_dt     DATE;    -- Date component of SELF
BEGIN
    -- Start by comparing mgach_bat_plais
    IF  SELF.mgach_bat_plais < in_mgach.mgach_bat_plais  OR
        (SELF.mgach_bat_plais      IS NULL      AND
         in_mgach.mgach_bat_plais  IS NOT NULL)
    THEN
        return_val := -1;
    ELSIF  SELF.mgach_bat_plais > in_mgach.mgach_bat_plais  OR
        (SELF.mgach_bat_plais      IS NOT NULL  AND
         in_mgach.mgach_bat_plais  IS NULL)
    THEN
        return_val := 1;
    ELSE
        -- tie-breaker: compare gach_dt_modif
        self_dt := TRUNC (SELF.gach_dt_modif);
        othr_dt := TRUNC (in_mgach.gach_dt_modif);

        IF  self_dt < othr_dt  OR
            (self_dt  IS NULL      AND
             othr_dt  IS NOT NULL)
        THEN
            return_val := -1;
        ELSIF  self_dt > othr_dt  OR
            (self_dt  IS NOT NULL  AND
             othr_dt  IS NULL)
        THEN
            return_val := 1;
        ELSE
            return_val := 0;
        END IF;
    END IF;

    RETURN  return_val;
END   compare;

END;

You can define a map function or an order function in the TYPE header, but not both. There's nothing to stop you from defining a map function and member function that meets all the conditions of an order function (or vice-versa), but the second function would be used only when you called it explicitly.