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
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation