Create type body : 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     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