CSC-468 Advance Database
Lecture 10
Object Oriented Database
Methods
Aniqa Naeem
Books In Library
•
“Fundamentals of Database
Systems", 7th Ed, by Ramez Elmasri
and Shamkant B. Navathe. 2017
•
“Database System: A Practical
Approach to design,
Implementation and
Management” , 4
thEd, by Thomas
Last lecture
• Inheritance: inherits all the attributes and methods of that super type or base type. declare an object type to be “under” another type.
• Method Overiding: when methods have same prototype in base
class as well as derived class, occurs when one class is inherited from another class.
Outline
•
Types Of Method
•
Member Method
•
Static Method
•
Constructor
•
Comparison Method
• There are four types of methods in PL/SQL:
1. Member. 2. Static.
3. Constructor. 4. Comparison.
• You’ll call these methods using an [object variable (for member
methods) and dot notation] or [the object name (for static methods) and dot notation]
• You can’t call a method on a null object variable, because no
object exists!
• A member method is a procedure or function designated with the keyword MEMBER.
• Calling programs may invoke such a method only on objects that have been instantiated.
• To invoke a method, use the following syntax:
<instant_name>.<method_name>([parameter_list]) V_car.print(a);
1. Member Methods
• Like packaged subprograms, methods have two parts:
– a specification
– a body.
• The specification (spec for short) consists of a method name, an
optional parameter list, and, for functions, a return type.
– MEMBER FUNCTION print RETURN varchar2
• The body is the code that executes to perform a specific task.
MEMBER FUNCTION print RETURN varchar2
Is Begin
Example:
Step-1:
Creating an object type
create or replace type car as object
(
price number,
color varchar2(25),
member function print
return varchar2
Step-2:
creating an object type body
Create or replace type body car
as
Member function print return varchar2
Is
Begin
Return ‘this is toyota’;
End;
Step-3:
Calling a member function
DECLARE
v_car car := car(1800000, 'Black');
/* v_car is an instance of type Car. Its attributes has been initialized with the CONSTRUCTOR method */
v_message1 varchar2(100); BEGIN
v_message1 := v_car.print;
dbms_output.put_li ne(v_message1);
• Such a method is declared using the keyword STATIC.
• You invoke a static method by using dot notation to qualify the method call with the name of the object type, for example:
type_name.method()
• Static Methods are invoked on the object type, not its instances.
• You use a static method for operations that are global to the type and
do not need to reference the data of a particular object instance.
• A static method has no SELF parameter.
• Static methods are declared using STATIC FUNCTION or STATIC PROCEDURE .
Example:
Step-1: Creating Function Static
CREATE OR REPLACE TYPE test AS OBJECT
(
txt varchar2(100),
constructor function test(pat varchar2, repeat number)
return self as result,
member procedure print,
static procedure my_print(a in varchar2)
);
Step-2: Defining Body of Function
create or replace type body test as
constructor function test(pat varchar2, repeat number) return self as result
is
begin
for a in 1 .. repeat loop
member procedure print is
begin
dbms_output.put_line(txt);
end print;
static procedure my_print(a in varchar2) is
begin
dbms_output.put_line(a);
end my_print;
Step-3 :Declare the values and instance
Declare
v_test_1 test;
v_test_2 test;
begin
v_test_1 := test('bla', 5);
v_test_1.print;
test.my_print('Hello’);
/*Static methods are invoked on the object type, not its instances*/
• Constructor will always be a function not a procedure.
• A constructor is a special kind of method and is a function which returns a new instance of the object and sets up the initial values of the object type.
• The RETURN clause of the constructor's header must be RETURN SELF AS RESULT.
• Even if you don't declare any methods, every instantiable object has a
default constructor method which allows a calling program to create
new objects of that type.
• This built-in method:
o Has the same name as the object type
o Is a function that returns an object of that type Accepts attributes in named or positional notation
o Must be called with a value (or NULL) for every attribute?there is no DEFAULT clause for object attributes
o Cannot be modified
• Programmers can replace this default constructor with their own using the CONSTRUCTOR FUNCTION syntax.
• This method must have the same name as the object type, but there are no restrictions on its parameter list.
• Oracle supports the overloading of programmer-defined
constructors.
• All non-static methods have the implied parameter SELF, which
refers to the current instance of the object. The default mode for the SELF parameter is IN for functions and IN OUT for
procedures.
create or replace type test as object
(
txt varchar2(100), constructor function
test(pat varchar2, repeat number) return self as result,
member procedure print );
/
Example 1
Step-2: Body defining function and procedure
create or replace type body test as
constructor function test(pat varchar2, repeat number) return self as result
is begin
for a in 1 .. repeat loop txt := txt || pat; end loop;
return; end;
member procedure print
Is
begin
dbms_output.put_line(txt); end print;
Step-3:
declare
v_test_1 test; begin
v_test_1 := test('bla', 5); -- without dot operator v_test_1.print; -- with dot operator
Example-2:
Display the empno,ename and sal of the input empno through constructor
Step-1:
create or replace type my_type as object
(
v_empno number,
V_ename varchar(25), V_sal number,
constructor function
my_type(p_empno number) return self as result,
member procedure display );
Step-2: creating Body
create or replace type body my_type as
constructor function my_type(p_empno number) return self as result is
begin
select empno,ename,sal into v_empno,v_ename,v_sal from emp
where empno = p_empno; return;
end;
member procedure display is
begin
dbms_output.put_line(v_empno || ' ' ||v_ename || ' ' ||v_sal);
Step-3:
25
declare
a my_type:= my_type(7369);
begin
a.display;
end;
• There are two types of comparison
methods:
1. Map Method 2. Order Method
• The comparison methods, ORDER and MAP, establish ordinal positions of objects for comparisons such as "<" or
"between“.
• Oracle invokes a comparison method automatically whenever it needs to perform such an operation.
• The MAP functions do not accept any formal parameters and must return a scalar data type, either CHAR, VARCHAR2, NUMBER or DATE, which can be easily compared by the system.
• The MAP member functions are used for validating object
instances with a single attribute.
• The Map method is a function implemented in such a way that its
value depends upon the value of the attributes.
• For example, for a customer object, if the customer code is same for two customers, both customers could be the same. So the relationship between these two objects would depend upon the value of customer code.
i. MAP member functions
Example-1: Step-1:
28
Step-2: Creating the type body
CREATE OR REPLACE TYPE BODY rectangle AS
MEMBER FUNCTION enlarge(inc number) return rectangle IS BEGIN
return rectangle(self.length + inc, self.width + inc); END enlarge;
MEMBER PROCEDURE display IS BEGIN
dbms_output.put_line('Length: '|| length); dbms_output.put_line('Width: '|| width);
END display;
MAP MEMBER FUNCTION measure return number IS BEGIN return (length*width); END measure;
END;
Step-3: Now using the rectangle object and its member functions − DECLARE
r1 rectangle; r2 rectangle; r3 rectangle;
inc_factor number := 5; BEGIN
r1 := rectangle(3, 4); r2 := rectangle(5, 7);
r3 := r1.enlarge(inc_factor); r3.display;
IF (r1 > r2) THEN – map member function will be called automatically r1.display;
ELSE
When the above code is executed at the SQL prompt, it produces the following result –
Length: 8 Width: 9
Length: 5 Width: 7
Example-2:
Display the senior most employee information
Step-1:
create or replace type new_type as object
(
empno number,
hiredate date,
member procedure display,
map member function measure return date
);
Step-2:
33
create or replace type body new_type as member procedure display
is begin
dbms_output. put_line('emp no: ' ||
empno);
dbms_output. put_line('hire date: ' ||
hiredate); end;
map member function measure return date is begin return(hiredate); end;
Step-3:
declare
emp_1 new_type:= new_type(101,'5-Jan-2016'); emp_2 new_type:= new_type(102,'10-Mar-2016'); begin
if(emp_1 < emp_2) then emp_1.display; else
emp_2.display; end if;
OUTPUT:
35
empno: 101
• An ORDER function accepts two parameters: SELF and another object of the same type.
• It must return an INTEGER value as explained in the following table:
Return value Object comparison
Any negative integer (commonly -1) SELF < second object
0 SELF = second object
Any positive integer (commonly 1) SELF > second object
NULL Undefined comparison: attributes needed for
the comparison are NULL
Example: Step-1:
37
CREATE OR REPLACE TYPE rectangle AS OBJECT
(
length number,
width number,
member procedure
display,
order member
function measure(r
rectangle) return
number
Step-2: Creating the type body
CREATE OR REPLACE TYPE BODY rectangle AS MEMBER PROCEDURE display IS
BEGIN
dbms_output.put_line('Length: '|| length); dbms_output.put_line('Width: '|| width); END display;
ORDER MEMBER FUNCTION measure(r rectangle) return number IS BEGIN return(1); return(-1); return(0); IF((self.length*self.width)>(r.length*r.width)) then ELSif((self.length*self.width)<(r.length*r.width)) then ELSif((self.length*self.width)=(r.length*r.width))
Step-3:
Using the rectangle object and its member functions
DECLAREr1 rectangle; r2 rectangle; BEGIN
r1 := rectangle(5, 4); r2 := rectangle(7, 6); r1.display;
r2.display;
IF (r1 > r2) THEN -- calling measure function r1.display;
ELSE if (r1=r2) then r2.display; else if
(r1<r2) then R2.display; END IF; END;
When the above code is executed at the SQL prompt, it produces the following result −
Length: 5
Width: 4
Length: 7
Width: 6
• A programmer can alter the mode by explicitly including SELF in the formal parameter list.
• Used to initialize a new instance of a TYPE (object)
• A default constructor is created with all attributes as parameters
in declared order
• An instance of an object knows about it-SELF
• SELF is always the first parameter passed to the method whether it is declared or not!
• SELF does not use the NOCOPY hint by default, but you should Always define a no attribute constructor for convenience
• Always define an all attribute (user-defined) constructor for
control (parameters must be named exactly the same as the attributes)
