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 )
9 /
SQL>
SQL> alter type Address_Type
2 REPLACE
3 as object
4 ( street_addr1 varchar2(25),
5 street_addr2 varchar2(25),
6 city varchar2(30),
7 state varchar2(2),
8 zip_code number,
9 member function toString return varchar2,
10 map member function mapping_function return varchar2
11 )
12 /
Type altered.
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 map member function mapping_function return varchar2
18 is
19 begin
20 return to_char( nvl(zip_code,0), 'fm00000' ) ||
21 lpad( nvl(city,' '), 30 ) ||
22 lpad( nvl(street_addr1,' '), 25 ) ||
23 lpad( nvl(street_addr2,' '), 25 );
24 end;
25 end;
26 /
Type body created.
SQL> create table people
2 ( name varchar2(10),
3 home_address address_type,
4 work_address address_type
5 )
6 /
SQL> create or replace type Address_Array_Type as varray(50) of Address_Type
2 /
SQL>
SQL> alter table people add previous_addresses Address_Array_Type
2 /