Implementing Methods

To implement a method in a type object use the following syntax:
create or replace type newperson_ty as object
(firstname varchar2(25),
lastname varchar2(25),
birthdate date,
member function AGE(BirthDate in DATE) return NUMBER;

Everything in the syntax is fairly straightforward. The object type is defined, except we now use the line “member function” to define the function call as accepting an age parameter and returning a number. It is important that you make any modifications to object type before you use it in a table; Oracle does not allow an object type to be re-created or dropped if that object type is used in a table (Oracle, 2002d). Now we need to define the AGE function. This can be done using the following syntax (Oracle, 2002a, p. 12):
create or replace type body newperson_ty as
member function AGE(BirthDate in DATE) return NUMBER is
begin
RETURN ROUND(SysDate - BirthDate)
end;
end;

The AGE function is now available to use within a query. First, we need to set up a table holding the person_ty object type.
create table NEWPERSON of newperson_ty;

insert into NEWPERSON values
(newperson_ty('JOHN', 'DOE', TO_DATE('03-FEB-1970', 'DD-MON-YYYY')));

To test the AGE function we can do the following:

select P.PERSON.AGE(P.PERSON.Birthdate)
from NEWPERSON P;

P.PERSON.AGE(P.PERSON.Birthdate)
----------------------------------------
12005

The P.PERSON.AGE(P.PERSON.Birthdate) call executes the AGE method and the age of the person is returned in days. By using a method call, static data (like a birth date) can be used to obtain variable data at the time the variable data is needed. As a result, variable data such as age can be excluded from the database.