Ask the Expert

Comparing user-defined objects, part 2

Continued from Part 1...

    Requires Free Membership to View

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
The (nonzero) magnitude or the number you return doesn't make any difference: -100, -1 and -.5 all mean exactly the same thing, namely "SELF is less 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.


This was first published in August 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: