Object Oriented Oracle, example 2

Oracle has OO capabilities. This example tries to show how to create Object types derive from them and insert/select instances from/to tables.

First, the base type is created. All methods are not instantiable, so, not type bode will have to be created.

create or replace type math_type as object (
  num  number(4),
  not instantiable member function  func(n number) return number,
  not instantiable member function  oper           return varchar2
) not instantiable not final;
/

Then, a adding type is derived from math_type.

create or replace type add_type under math_type (
  overriding member function func(n number) return number,
  overriding member function oper           return varchar2
) instantiable final;
/

create or replace type body add_type as
  overriding member function func(n number) return number is begin
    return n + num;
  end;

  overriding member function oper return varchar2 is begin
    return '+';
  end;
end;
/

A multiplying type is created. The type's specification (except its name) looks exactly like add_type's:

create or replace type mult_type under math_type (
  overriding member function func(n number) return number,
  overriding member function oper           return varchar2
);
/

create or replace type body mult_type as
  overriding member function func(n number) return number is begin
    return n * num;
  end;

  overriding member function oper return varchar2 is begin
    return '*';
  end;
end;
/

A table with the base type and a number as attributes is created:

create table t_(m math_type, o number(3));

In order to fill the table, two derived types are created and inserted:

declare
  a add_type  := add_type (49);
  m mult_type := mult_type(15);
begin
  insert into t_ values(a, 100);
  insert into t_ values(m,  10);
end;
/

This is now the fun part: the select statement. Note that depending on the previously inserted type a different func(..) and oper(..) are executed:

column oper format a1
column num  format 999999

select t.o o, t.m.oper() oper, t.m.num num, '=' eq,  t.m.func(t.o) res from t_ t;

Here's the resulting output:

         O O        NUM E     RES
---------- - ---------- - -------
       100 +         49 =     149
        10 *         15 =     150

Cleaning up...

drop table t_;
drop type add_type;
drop type mult_type;
drop type math_type;


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger