Calling member function in oracle 11g

Question

I have a type called budget defined as

create type budget as object ( 
    year number,
    amount number,
    member function left_over (year in number) return number
)

Body:

create type body budget as
    member function left_over(year in number) return number is
    begin
        return amount;
    end left_over;
end;

And an object table

create table budget_table of budget;

How do I use the member function to return the amount? Something like:

select b.left_over(2010) from budget_table b;

Thanks

Answer 1

You don't need a parameter to the method:

SQL> create or replace type budget as object (
  2      year number,
  3      amount number,
  4      member function left_over return number
  5  )
  6  /

Type created.

SQL> create or replace type body budget as
  2      member function left_over return number is
  3      begin
  4          return amount;
  5      end left_over;
  6  end;
  7  /

Type body created.

SQL> create table budget_table of budget;

Table created.

SQL> insert into budget_table values (budget(2010,99));

1 row created.

SQL> commit;

Commit complete.

SQL> select b.left_over() from budget_table b;

B.LEFT_OVER()
-------------
           99

(I assume this is an academic exercise, as it would make no sense to create tables like this in a real business database!)

To restrict to the budget for a particular year:

SQL> insert into budget_table values (budget(2010,99));

1 row created.
SQL> select b.left_over() from budget_table b;

B.LEFT_OVER()
-------------
           88
           99

SQL> select b.left_over() from budget_table b
  2  where b.year = 2010;

B.LEFT_OVER()
-------------
           99

Answer 2

It is a scoping issue. Your function left_over() is a method on Budget, which is an individual thing. However, you want to have a method which does a look up from a range of budgets. The method as you have written it cannot do this, because an instance can only know about itself. How can the Budget for 2009 possibly know the figures for 2010?

What you need is an object Budgets which has a collection of Budget as an attribute, and a member function left_over() which returns the whatever for a given Budget in its collection. Of course, the only way of getting that information is for the method to iterate over the collection, which will perform far less efficiently than a WHERE clause on a regular table but seems to be the standard approach in OO data practices.