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;
/

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;


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