Continued from Part 1...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.