Mega Code Archive
Type with order function
<![CDATA[
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> drop type Address_Type;
Type dropped.
SQL>
]]>