Q

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
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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close