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

Object Oriented Oracle, example 1

Oracle has OO capabilities. This example demonstrates how to
  • create a type
  • derive a new type from it
  • and how to store instance of this type in a table
First the base type specification is created. The type consists of one function (named: func), one procedure (named: proc) and two constructors, of which on is implicetly defined and the other one explicitely defined.
create or replace type base_type as object (
  a number,
  constructor function base_type return self as result,
  member function  func return number,
  member procedure proc (n number)
) instantiable not final;
/
Now, the type's implementation is created. The implementation defines how the type's functions, procedures and explicit constructors behave:
create or replace type body base_type as 
  constructor function base_type return self as result is
  begin
    a:=0;
    return;
  end base_type;

  member function func return number is
  begin
    return a;
  end func;

  member procedure proc (n number) as
  begin
    a:=n;
  end proc;
end;
/
We're ready to derive from base_type. The keyword for deriving is under. The derived type defines a new attribute (named: m) and overrides func.
create or replace type deriv_type under base_type (
  m number,
  overriding member function func return number
);
/
As is the case with base types, the overridden methods in the derived type must be implemented:
create or replace type body deriv_type as
  overriding member function func return number is
  begin
    return m*a;
  end;
end;
/
The created types can be instantiaded and methods can be called:
declare
  b1 base_type :=base_type();
  b2 base_type :=base_type(4);
  d1 deriv_type:=deriv_type(5,6);
  d2 deriv_type:=deriv_type(5,6);
begin
  dbms_output.put_line(b1.func);
  dbms_output.put_line(b2.func);

  d1.proc(4);
  dbms_output.put_line(d1.func);
  dbms_output.put_line(d2.func);
end;
/
0
4
24
30
The created types have become real types and can be used in tables:
create table table_base (
  b base_type
);
declare
  base  base_type := base_type();
  deriv deriv_type:= deriv_type(8,9);
begin
  insert into table_base values(base);
  insert into table_base values(deriv);
end;
/
select t.b.func() from table_base t;
T.B.FUNC()
----------
         0
        72
select avg(t.b.func()) from table_base t;
AVG(T.B.FUNC())
---------------
             36
Declaring a cursor:
begin
  for r in (select b from table_base) loop
    dbms_output.put_line(r.b.func());
  end loop;
end;
/
0
72

Cleaning up

drop table table_base;

drop type deriv_type;
drop type base_type;