Method Basics

CREATE TYPE BODY site_t AS
 MEMBER FUNCTION get_ref (emp_id IN integer)
 RETURN INTEGER IS
 status INTEGER:=0;
 BEGIN
    SELF.employee_id_r := emp_t.give_ref(emp_id);
    RETURN status;
 EXCEPTION
 WHEN others THEN
    status := 1;
    RETURN status;
 END;
 ORDER MEMBER FUNCTION give_order( site INTEGER)
 RETURN INTEGER IS
 BEGIN
IF site<SELF.site
   THEN
      RETURN 1;
ELSIF site>SELF.site
   THEN
       RETURN -1;
Else
       RETURN 0;
 END;
END;

The code just illustrated creates an ORDER function called give_order that returns -1, 1, or 0 depending on the comparison of the input value to the value of the instance attribute. Again, an ORDER function is used on a one time basis (it can only compare the input value to the current instance value) while a MAP method “maps” all instance values into scalars when it is called.

SEE CODE DEPOT FOR FULL SCRIPTS

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.