Create toString function for a type : type body « Object Oriented « Oracle PL/SQL Tutorial






SQL>
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  )
 10  /

Type created.

SQL>
SQL>
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  end;
 17  /

Type body created.

SQL>
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> declare
  2      l_home_address address_type;
  3      l_work_address address_type;
  4  begin
  5      l_home_address := Address_Type( '1 Street', null,'R', 'VA', 45678 );
  6      l_work_address := Address_Type( '1 Way', null,'R', 'CA', 23456 );
  7
  8      insert into people( name, home_address, work_address )values ( 'Tom Kyte', l_home_address, l_work_address );
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select name, p.home_address.toString()
  2    from people P
  3  /


NAME
----------
P.HOME_ADDRESS.TOSTRING()
--------------------------------------------------------------------------------
Tom Kyte
1 Street R, VA 45678


SQL> drop type Address_Type;
drop type Address_Type
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


SQL>
SQL> drop table people;

Table dropped.

SQL>
SQL>
SQL>








32.4.type body
32.4.1.Create type body
32.4.2.Type with method
32.4.3.Static Method
32.4.4.Implement a function in type body
32.4.5.Create toString function for a type
32.4.6.Object table
32.4.7.Type with order function
32.4.8.Type with member procedure