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 map member function mapping_function return varchar2
10 )
11 /
Type created.
SQL> create or replace type body Address_Type as
2 member function toString return varchar2
3 is
4 begin
5 if ( street_addr2 is not NULL )
6 then
7 return street_addr1 || ' ' ||
8 street_addr2 || ' ' ||
9 city || ', ' || state || ' ' || zip_code;
10 else
11 return street_addr1 || ' ' ||
12 city || ', ' || state || ' ' || zip_code;
13 end if;
14 end;
15
16 map member function mapping_function return varchar2
17 is
18 begin
19 return to_char( nvl(zip_code,0), 'fm00000' ) ||
20 lpad( nvl(city,' '), 30 ) ||
21 lpad( nvl(street_addr1,' '), 25 ) ||
22 lpad( nvl(street_addr2,' '), 25 );
23 end;
24 end;
25 /
Type body created.
SQL>
SQL> create table people
2 ( name varchar2(10),
3 home_address address_type,
4 work_address address_type
5 )
6 /
Table created.
SQL>
SQL> set echo on
SQL> set linesize 73
SQL>
SQL> create or replace type Address_Array_Type as varray(25) of Address_Type
2 /
Type created.
SQL> alter table people add previous_addresses Address_Array_Type
2 /
Table altered.
SQL> set describe depth all
SQL>
SQL> desc people
Name Null? Type
------------------------------------- -------- -------------------------
NAME VARCHAR2(10)
HOME_ADDRESS ADDRESS_TYPE
STREET_ADDR1 VARCHAR2(25)
STREET_ADDR2 VARCHAR2(25)
CITY VARCHAR2(30)
STATE VARCHAR2(2)
ZIP_CODE NUMBER
METHOD
------
MEMBER FUNCTION TOSTRING RETURNS VARCHAR2
METHOD
------
MAP MEMBER FUNCTION MAPPING_FUNCTION RETURNS VARCHAR2
WORK_ADDRESS ADDRESS_TYPE
STREET_ADDR1 VARCHAR2(25)
STREET_ADDR2 VARCHAR2(25)
CITY VARCHAR2(30)
STATE VARCHAR2(2)
ZIP_CODE NUMBER
METHOD
------
MEMBER FUNCTION TOSTRING RETURNS VARCHAR2
METHOD
------
MAP MEMBER FUNCTION MAPPING_FUNCTION RETURNS VARCHAR2
PREVIOUS_ADDRESSES ADDRESS_ARRAY_TYPE
STREET_ADDR1 VARCHAR2(25)
STREET_ADDR2 VARCHAR2(25)
CITY VARCHAR2(30)
STATE VARCHAR2(2)
ZIP_CODE NUMBER
METHOD
------
MEMBER FUNCTION TOSTRING RETURNS VARCHAR2
METHOD
------
MAP MEMBER FUNCTION MAPPING_FUNCTION RETURNS VARCHAR2
SQL>
SQL> select name, length from sys.col$
2 where obj# = ( select object_id from user_objects where object_name = 'PEOPLE' )
3 /
NAME LENGTH
---------- ----------
NAME 10
HOME_ADDRE 1
SS
SYS_NC0000 25
3$
SYS_NC0000 25
4$
SYS_NC0000 30
5$
SYS_NC0000 2
6$
SYS_NC0000 22
7$
WORK_ADDRE 1
SS
SYS_NC0000 25
9$
SYS_NC0001 25
0$
drop table people;
NAME LENGTH
---------- ----------
SYS_NC0001 30
1$
SYS_NC0001 2
2$
SYS_NC0001 22
3$
PREVIOUS_A 3042
DDRESSES
14 rows selected.
SQL> drop type Address_Array_Type;
SQL> drop type address_type;
drop type address_type