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 last published in August 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close