The following example demonstrates how to use method overloading. There are two versions of GETSALARY method. First version doesn’t take any parameter and returns total salary of the employee. Second version takes the no. of days and returns the salary for the given number of days.
create or replace type Employee_Type as Object
( empno number(5),
ename varchar2(20),
bs number(5),
member function GetSalary return number,
member function GetSalary(ndays number) return number
);
create or replace type body Employee_Type as
member function getsalary return number is
begin
return bs;
end;
member function getsalary(ndays number) return number is
begin
return bs / 30 * ndays;
end;
end;
Here is a PL/SQL program to call both the methods. E is declared as an object of EMPLOYEE_TYPE. Then constructor of object type is used to initialize members of the object. When GETSALARY method is called without any parameter, first version is called. When GETSALARY invoked with a parameter then second version is called.
declare
e employee_type;
begin
e := employee_type(101,'Praneeth',10000);
-- get total salary
dbms_output.put_line( e.getsalary);
-- get salary for 25 days
dbms_output.put_line( e.getsalary(25));
end;
In the following cases methods can be overloaded:
A static method doesn’t have SELF parameter so it cannot access any data related to either object or invoke any non-static method.
The following example shows how to create and use static method. GETMINBAL method is a static method that takes the type of account and returns the minimum balance to be maintained for account of that type. This method can be called without creating any object of the object type.
create or replace type account_type as object
( ano number(10),
customer varchar2(30),
curbal number(10),
static function GetMinBal(atype varchar2) return number
);
create or replace type body account_type as
static function GetMinBal(atype varchar2) return number
is
begin
if atype = 'S' then
return 1000;
else
return 2000;
end if;
end;
end;
The following PL/SQL block invokes the static method to get minimum balance for account of type C.
Begin
dbms_output.put_line ( account_type.getminbal('C'));
end;
The process of changing an Object Type according to the requirement is called as Type Evolution. This allows applications to be evoluved without having to be desinged entirely in advance.
The following few example will show how to alter an object type.
Frist, let us create a new object type called - STUDENT_TYPE as follows:
create or replace type student_type as object ( rollno number(5), name varchar2(30) );Then let us create an object based on this object type.
create table students of student_type;The object type STUDENT_TYPE doesn't contain some details that we need to have for a student. For example, it doesn't have course that student has taken and fee the student has paid so far. So the following ALTER TYPE command will add two attributes to STUDENT_TYPE object type. As the object type is evolving based on the need it is called as Type Evolution.
alter type student_type
add attribute( course varchar2(5),
feepaid number(6) ) cascade;
CASCADE specifies that all dependents of STUDENT_TYPE object type must be affected by the change.
Now, the structure of the object type looks like the following:
SQL> desc student_type Name Null? Type ----------------------------------------- -------- ---------------------------- ROLLNO NUMBER(5) NAME VARCHAR2(30) COURSE VARCHAR2(5) FEEpaid NUMBER(6)The values in attributes - COURSE and FEEPAID in STUDENTS table that is created on STUDENT_TYPE will be null for existing rows.
Then let us see how to add a method to an existing object type. Say, we have to add a method that returns the course fee based on the course. The following ALTER TYPE followed by CREATE TYPE BODY command will achieve it.
alter type student_type
add member function GetFee return number cascade;
create or replace type body student_type as
member function GetFee return number
is
begin
return case course
when 'Ora' then 10000
when 'ODba' then 20000
when 'Vb' then 12000
end;
end;
end;
The following select command makes use of attributes and method of STUDENT_TYPE to provide the complete information.
SQL>select name,course, value(s).getfee() totalfee, fee from students s; NAME COURS TOTALFEE FEE ------------------------------ ----- ---------- ---------- Srikanth ODba 20000 15000 Praneeth Ora 10000 5000
The new object type is called as SUBTYPE and the existing one is called as SUPERTYPE.
All the attributes and methods of supertype are inherited into subtype. In addition to that, subtype can add new attributes and methods or even override existing methods.
Oracle9i supports single inheritance. It means a subtype can be derived from only one supertype.
The following example creates a PERSON_TYPE and then STUDENT_TYPE that is derived from PERSON_TYPE.
First create PERSON_TYPE that represent a person.
create or replace type person_type as object
(
name varchar2(20),
address varchar2(100),
age number(3),
member function ToString return varchar2,
final member function GetType return varchar2
) NOT FINAL;
create or replace type body person_type as
member function ToString return varchar2
is
begin
return name || ':' || address || ':' || to_char(age);
end;
final member function GetType return varchar2
is
begin
if age < 15 then
return 'Child';
elsif age < 25 then
return 'Teens';
elsif age < 45 then
return 'Middleaged';
else
return 'Old';
end if;
end;
end;
If an object type is to be used as super type then it must be declared as NOT FINAL.
FINAL object types are final and they cannot be inherited. When a method is declared FINAL then the method cannot be overridden in subtype. GETTYPE function of PERSON_TYPE is declared as FINAL and it cannot be overridden.
Methods are not final by default. They can be overridden in the subtype.
A member can also be defined as FINAL; it is not to be overridden by subtypes.
Note: By default object type is final and subtypes cannot be derived from it. So giving NOT FINAL for objects that are to be inherited is mandatory.
The following is the creation of subtype called STUDENT_TYPE that is derived from PERSON_TYPE. It adds two attributes – COURSE and BALANCE and a method – PAYMENT.
It also overrides TOSTRING method of PERSON_TYPE. Whenever TOSTRING method is called using an object of STUDENT_TYPE then TOSTRING method of subtype is invoked and not that of supertype.
create or replace type student_type under person_type
(
course varchar2(20),
balance number(5),
member procedure payment(amt number),
overriding member function ToString return varchar2
) NOT FINAL;
create or replace type body student_type
as
member procedure payment(amt number)
is
begin
balance := balance - amt;
end;
overriding member function ToString return varchar2
is
begin
return name || ':' || to_char(age) || ':' || course || ':' || to_char(balance);
end;
end;
UNDER keyword is used to create a new object type from an existing object type. Oracle9i uses this keyword to implement inheritance.
OVERRIDING keyword specifies that the subtype is overriding a method with the same signature in the supertype.
STUDENT_TYPE is adding two attributes and a method to PERSON_TYPE. The following table show attributes and methods of these two object types.
| Object Type | Attributes | Methods |
|---|---|---|
| Person_type | Name, address, age | GetType |
| Student_type | Name, address, age, course,balance | GetType, payment |
The following PL/SQL block shows how to use supertype and subtype.
declare
p person_type;
s student_type;
begin
p := person_type('Larry Ellison','California', 50);
s := student_type('Alen king','Newyork',22,'MS CS',10000);
dbms_output.put_line( p.gettype);
dbms_output.put_line( s.gettype);
dbms_output.put_line( p.tostring);
dbms_output.put_line( s.tostring);
s.payment(2000);
dbms_output.put_line( s.tostring);
end;
The output of the above block is as follows:
Old Teens Larry Ellison:50 Alen king:22:MS CS:10000 Alen king:22:MS CS:8000The constructor of STUDENT_TYPE takes five parameters as it inherits 3 attributes from PERSON_TYPE and added two more to them. When you call TOSTRING function of P then TOSTRING function of PERSON_TYPE is called. But when TOSTRING is invoked using S then TOSTRING function of STUDENT_TYPE is invoked. So Oracle calls the method from the object type whose object is used to invoke the method.
As Oracle determines to which method – supertype’ method or subtype’s method – the control should be dispatched at runtime, it called as dynamic method dispatch. The term dynamic means the activity that happens at runtime and not at compile time.
The following code uses the same object to refer to an object of PERSON_TYPE first and then an object of STUDENT_TYPE.
Oracle allows an object reference of supertype to refer to an object of subtype. However, we can invoke only the method that are common between supertype and subtype using this object reference.
declare
p person_type;
begin
p := person_type('Larry Ellison','California', 50);
dbms_output.put_line( p.tostring);
p := student_type('Alen king','Newyork',22,'MS CS',10000);
dbms_output.put_line( p.tostring);
end;
In the above example, we created an object reference (P) of PERSON_TYPE. Then first we made it referring to an object of PERSON_TYPE and then we call TO_STRING method using P. As it references an object of PERSON_TYPE, method TO_STRING of that object type is called.
After that we made P pointing to an object of STUDENT_TYPE. This is permitted in Oracle (in any other object oriented programming for that matter) as P is an object reference of supertype and STUDENT_TYPE is a subtype of PERSON_TYPE. When we call TOSTRING function, Oracle calls TOSTRING function of STUDENT_TYPE.
Whenever we invoke P.TOSTRING, Oracle determines which method to invoke depending on the type of object P is referencing.
However, note that you can invoke only the methods that are part of supertype (PERSON_TYPE) alone and methods that are overridden in subtype. In the above example, calling PAYMENT method using P even though P is referencing an object of STUDENT_TYPE is invalid.
1 declare
2 p person_type;
3 begin
4 p := student_type('Alen king','Newyork',22,'MS CS',10000);
5 dbms_output.put_line( p.tostring);
6 p.payment(2000);
7* end;
8 /
p.payment(2000);
*
ERROR at line 6:
ORA-06550: line 6, column 6:
PLS-00302: component 'PAYMENT' must be declared
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored
When NOT INSTANTIABLE is used with a method then that method is to be overridden in all subtypes of the object type. If a not instantiable method is not overridden in subtype then subtype is also to be declared as NOT INSTANTIABLE.
The following example shows how to use NOT INSTANTIABLE types and methods.
EMPLOYEE_TYPE is a NOT INSTANTIABLE object type. It also contains a method GETSALARY that is NOT INSTANTIABLE.
You need not define body for non instantiable methods.
create or replace type employee_type as object
( empno number(5),
ename varchar2(20),
NOT INSTANTIABLE member function GetSalary return number
) NOT INSTANTIABLE NOT FINAL;
Now let us create TEMPEMP_TYPE that is derived from EMPLOYEE_TYPE. As the supertype has a non instantiable method, that method must be overridden in this subtype.
create or replace type tempemp_type UNDER employee_type
(
nohours number(3),
hourrate number(4),
overriding member function GetSalary return number
);
create or replace type body tempemp_type
as
overriding member function GetSalary return number
as
begin
return nohours * hourrate;
end;
end;
If TEMPEMP_TYPE doesn’t override GETSALARY method of EMPLOYEE_TYPE then this object type also becomes non instantiable.
The following PL/SQL code is used to create an object of TEMPEMP_TYPE and call GETSALARY method.
declare
e1 tempemp_type;
begin
e1 := tempemp_type(1,'xyz',10,20);
dbms_output.put_line( e1.getsalary );
end;
If you try to create an object EMPLOYEE_TYPE then it will result in the following error, as it not an object type that can have an object.
SQL> declare
2 e1 employee_type;
3 begin
4 e1 := employee_type(1,'xyz');
5 end;
6 /
e1 := employee_type(1,'xyz');
*
ERROR at line 4:
ORA-06550: line 4, column 12:
PLS-00713: attempting to instantiate a type that is NOT INSTANTIABLE
ORA-06550: line 4, column 6:
PL/SQL: Statement ignored
However, it is possible to create an object reference of EMPLOYEE_TYPE and make it pointing to one of its subtypes such as TEMPEMP_TYPE and call the common methods of supertype and subtype.
SQL> declare 2 e1 employee_type; 3 begin 4 e1 := tempemp_type(1,'xyz',20,100); 5 dbms_output.put_line( e1.getsalary()); 6 end; 7 / 2000 PL/SQL procedure successfully completed.
Good Luck,