Type with order function : type body « Object Oriented « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Object Oriented
3. type body

SQL>
SQL> create or replace type Address_Type
  2  as object
  3  (  street_addr1   varchar2(25),
  4     street_addr2   varchar2(25),
  5     city           varchar2(30),
  6     state          varchar2(2),
  7     zip_code       number,
  8     member function toString return varchar2,
  9     order member function order_function( compare2 in Address_type )
 10     return number
 11  )
 12  /

Type created.

SQL>
SQL> create or replace type body Address_Type
  2  as
  3      member function toString return varchar2
  4      is
  5      begin
  6          if ( street_addr2 is not NULL )
  7          then
  8              return street_addr1 || ' ' ||
  9                     street_addr2 || ' ' ||
 10                     city || ', ' || state || ' ' || zip_code;
 11          else
 12              return street_addr1 || ' ' ||
 13                     city || ', ' || state || ' ' || zip_code;
 14          end if;
 15      end;
 16
 17      order member function order_function(compare2 in Address_type)
 18      return number
 19      is
 20      begin
 21          if (nvl(self.zip_code,-99999) <> nvl(compare2.zip_code,-99999))
 22          then
 23              return sign(nvl(self.zip_code,-99999) - nvl(compare2.zip_code,-99999));
 24          end if;
 25          if (nvl(self.city,chr(0)) > nvl(compare2.city,chr(0)))
 26          then
 27              return 1;
 28          elsif (nvl(self.city,chr(0)) < nvl(compare2.city,chr(0)))
 29          then
 30              return -1;
 31          end if;
 32          if ( nvl(self.street_addr1,chr(0)) > nvl(compare2.street_addr1,chr(0))  )
 33          then
 34              return 1;
 35          elsif ( nvl(self.street_addr1,chr(0)) < nvl(compare2.street_addr1,chr(0)) )
 36          then
 37              return -1;
 38          end if;
 39          if ( nvl(self.street_addr2,chr(0)) > nvl(compare2.street_addr2,chr(0))  )
 40          then
 41              return 1;
 42          elsif ( nvl(self.street_addr2,chr(0)) < nvl(compare2.street_addr2,chr(0)) )
 43          then
 44              return -1;
 45          end if;
 46          return 0;
 47      end;
 48  end;
 49  /

Type body created.

SQL>
SQL>
SQL> drop type Address_Type;

Type dropped.

SQL>