Method Basics

The previous listing will return a site_id value whenever comparison values are required from the site_t type. This allows GROUP BY, ORDER BY and other sort dependent calls against the type to function properly.

The ORDER type MEMBER function allows for ordering of entries that don't have simple solutions like returning a site_id that is already stored in the table. An ORDER function must return an integer value and is only used once per call. A MAP function is used recursively. An ORDER is most useful when you will be comparing a small number of instances against a single value. A MAP function is most useful when you are sorting large numbers of instances. An example of an ORDER function is shown below:

CREATE OR REPLACE TYPE site_t as object (
  site_id                          INTEGER,
  company                      company_t,
  address                       address_t,
  employee_id_r             REF emp_t,
MEMBER FUNCTION get_ref
   (emp_id IN integer)
   RETURN REF emp_t,
ORDER MEMBER FUNCTION give_order( site INTEGER)
RETURN INTEGER)
/

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.