Save type hierarchy to one table with parent type as the column type : Type Hierarchy « Object Oriented Database « Oracle PL / SQL






Save type hierarchy to one table with parent type as the column type

  
SQL>
SQL> set serverout on
SQL>
SQL>
SQL> create or replace type employee as object(
  2    name varchar2(100),
  3    empno number,
  4    hiredate date,
  5    vacation_used number,
  6    final member procedure vacation( p_days number ),
  7    not instantiable member procedure give_raise( p_increase number ),
  8    not instantiable member function yearly_compensation return number
  9  )
 10  not instantiable
 11  not final
 12  /

Type created.

SQL>
SQL> create or replace
  2  type body employee as
  3    final member procedure vacation( p_days number ) is
  4    begin
  5      if p_days + self.vacation_used <= 10 then
  6        self.vacation_used := self.vacation_used + p_days;
  7      else
  8        raise_application_error(
  9          -20001,
 10          'You are ' || to_char(p_days + self.vacation_used - 10) ||
 11          ' days over your vacation limit.' );
 12      end if;
 13    end;
 14  end;
 15  /

Type body created.

SQL>
SQL> create or replace type sales_rep under employee(
  2    salary number,
  3    commission number,
  4    overriding member procedure give_raise( p_increase number ),
  5    member procedure give_commission( p_increase number ),
  6    overriding member function yearly_compensation return number
  7  )
  8  /

Type created.

SQL> create or replace
  2  type body sales_rep as
  3   overriding member procedure give_raise( p_increase number ) is
  4   begin
  5     self.salary := self.salary + (self.salary * (p_increase/100));
  6   end;
  7   member procedure give_commission( p_increase number ) is
  8   begin
  9     self.commission := self.commission + p_increase;
 10   end;
 11   overriding member function yearly_compensation return number is
 12   begin
 13     return self.salary + self.commission;
 14   end;
 15  end;
 16  /

Type body created.

SQL>
SQL> create or replace
  2  type consultant
  3  under employee(
  4    hourly_rate number,
  5    overriding member procedure give_raise( p_increase number ),
  6    overriding member function yearly_compensation return number
  7  )
  8  /

Type created.

SQL>
SQL>
SQL>
SQL> create or replace
  2  type body consultant as
  3    overriding member procedure give_raise( p_increase number ) is
  4    begin
  5      self.hourly_rate := self.hourly_rate + p_increase;
  6    end;
  7    overriding member function yearly_compensation return number is
  8    begin
  9      return self.hourly_rate * 40 * 52;
 10    end;
 11  end;
 12  /

Type body created.

SQL>
SQL> create table employees (e employee )
  2  /

Table created.

SQL>
SQL>
SQL>
SQL> declare
  2    l_consultant consultant;
  3    l_sales_rep sales_rep;
  4  begin
  5    l_consultant := consultant( 'D', 12345, sysdate, 0, 19.50 );
  6    l_sales_rep := sales_rep( 'J', 67890, sysdate, 0, 50000, 0 );
  7
  8    l_consultant.give_raise( 4.75 );
  9    l_sales_rep.give_raise( 3 );
 10    l_sales_rep.give_commission( 100 );
 11
 12    insert into employees values ( l_sales_rep );
 13    insert into employees values ( l_consultant );
 14
 15      for c in ( select emps.e.yearly_compensation() yc,
 16                        emps.e.name name
 17                   from employees emps )
 18      loop
 19  dbms_output.put_line(c.name ||' makes '|| to_char(c.yc) ||' a year.');
 20      end loop;
 21
 22      l_sales_rep.vacation( 5 );
 23      l_sales_rep.vacation( 7 );
 24   end;
 25   /
J makes 51600 a year.
D makes 50440 a year.
declare
*
ERROR at line 1:
ORA-20001: You are 2 days over your vacation limit.
ORA-06512: at "JAVA2S.EMPLOYEE", line 7
ORA-06512: at line 23


SQL>
SQL> select * from employees;

no rows selected

SQL>
SQL> drop table employees;

Table dropped.

SQL>
SQL> drop type consultant;

Type dropped.

SQL>
SQL> drop type sales_rep;

Type dropped.

SQL>
SQL> drop type employee;

Type dropped.

SQL>
SQL>

   
  








Related examples in the same category

1.Under a type
2.Constructors in hierarchical types
3.dependencies among object types.
4.Mutual-exclusion inheritance using "under"