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.
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments