Oracle PL/SQL/Object Oriented Database/treat

Treat type as

   <source lang="sql">

SQL> SQL> SQL> create or replace

 2  type person as object (
 3   first_name varchar2(100),
 4   last_name varchar2(100),
 5   dob date,
 6   phone varchar2(100),
 7   member function get_last_name return varchar2,
 8   member function get_phone_number return varchar2 )
 9  not final
10  /

Type created. SQL> SQL> SQL> create or replace

 2  type body person as
 3    member function get_last_name return varchar2 is
 4    begin
 5      return self.last_name;
 6    end;
 7    member function get_phone_number return varchar2 is
 8    begin
 9      return self.phone;
10    end;
11  end;
12  /

Type body created. SQL> SQL> create table person_table( p person ); Table created. SQL> SQL> SQL> SQL> create or replace

 2  type new_employee under person (
 3    empno number,
 4    hiredate date,
 5    work_phone varchar2(100),
 6    overriding member function get_phone_number return varchar2,
 7    member function get_home_phone_number return varchar2 )
 8  not final
 9  /

Type created. SQL> SQL> create or replace

 2  type body new_employee as
 3    overriding member function get_phone_number return varchar2 is
 4    begin
 5      return self.work_phone;
 6    end;
 7    member function get_home_phone_number return varchar2 is
 8    begin
 9      return self.phone;
10    end;
11  end;
12  /

Type body created. SQL> SQL> SQL> select treat( x.p as new_employee ).empno empno,

 2         x.p.last_name last_name
 3     from person_table x
 4    where p is of ( new_employee )
 5  /

no rows selected SQL> SQL> select * from person_table; no rows selected SQL> SQL> drop table person_table; Table dropped. SQL> SQL> drop type new_employee; Type dropped. SQL> SQL> drop type person; Type dropped. SQL> SQL>

</source>
   
  

Use treat to convert varible to a type in select statement

   <source lang="sql">

SQL> SQL> SQL> SQL> create or replace

 2  type person as object (
 3   first_name varchar2(100),
 4   last_name varchar2(100),
 5   dob date,
 6   phone varchar2(100),
 7   member function get_last_name return varchar2,
 8   member function get_phone_number return varchar2 )
 9  not final
10  /

Type created. SQL> SQL> SQL> create or replace

 2  type body person as
 3    member function get_last_name return varchar2 is
 4    begin
 5      return self.last_name;
 6    end;
 7    member function get_phone_number return varchar2 is
 8    begin
 9      return self.phone;
10    end;
11  end;
12  /

Type body created. SQL> SQL> create table person_table( p person ); Table created. SQL> SQL> select treat( p as person).get_last_name() from person_table p; no rows selected SQL> SQL> drop table person_table; Table dropped. SQL> SQL> drop type person; Type dropped. SQL>

</source>