Mega Code Archive
Save type hierarchy to one table with parent type as the column type
<![CDATA[
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 "RNTSOFT.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>
]]>