In this section, we will talk about Object-Oriented PL/SQL. PL/SQL permits characterizing an item type, which helps in planning object-arranged information base in Oracle. An article type permits you to make composite sorts. Utilizing objects permit you to actualize certifiable articles with explicit construction of information and techniques for working it. Items have characteristics and strategies. Traits are properties of an article and are utilized for putting away an item's state; and techniques are utilized for demonstrating its conduct.
Items are made utilizing the CREATE [OR REPLACE] TYPE articulation. Following is a guide to make a basic location object comprising of not many ascribes −
CREATE OR REPLACE TYPE address AS OBJECT
(house_no varchar2(10),
street varchar2(30),
city varchar2(20),
state varchar2(10),
pincode varchar2(10)
);
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
Type created.
We should make one more item client where we will wrap credits and strategies together to have object-situated inclination −
CREATE OR REPLACE TYPE customer AS OBJECT
(code number(5),
name varchar2(30),
contact_no varchar2(12),
addr address,
member procedure display
);
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Type created.
Instantiating an Object
Characterizing an article type gives a diagram to the item. To utilize this item, you need to make occasions of this article. You can get to the ascribes and strategies for the article utilizing the occurrence name and the entrance administrator (.) as follows −
DECLARE
residence address;
BEGIN
residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');
dbms_output.put_line('House No: '|| residence.house_no);
dbms_output.put_line('Street: '|| residence.street);
dbms_output.put_line('City: '|| residence.city);
dbms_output.put_line('State: '|| residence.state);
dbms_output.put_line('Pincode: '|| residence.pincode);
END;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
House No: 103A
Street: M.G.Road
City: Jaipur
State: Rajasthan
Pincode: 201301
PL/SQL procedure successfully completed.
Member Methods
Part techniques are utilized for controlling the credits of the item. You give the announcement of a part strategy while proclaiming the item type. The article body characterizes the code for the part techniques. The item body is made utilizing the CREATE TYPE BODY articulation.
Constructors are capacities that return another article as its worth. Each article has a framework characterized constructor technique. The name of the constructor is same as the item type. For instance −
residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');
The examination strategies are utilized for contrasting articles. There are two different ways to analyze objects −
Map method
The Map technique is a capacity actualized so that its worth relies on the estimation of the credits. For instance, for a client object, if the client code is same for two clients, the two clients could be the equivalent. So the connection between these two articles would rely on the estimation of code.
Order method
The Order strategy actualizes some inward rationale for looking at two items. For instance, for a square shape object, a square shape is greater than another square shape if the two its sides are greater.
Using Map method
Allow us to attempt to comprehend the above ideas utilizing the accompanying square shape object −
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
member function enlarge( inc number) return rectangle,
member procedure display,
map member function measure return number
);
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Type created.
Making the sort 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 (sqrt(length*length + width*width));
END measure;
END;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
Type body made.
Presently utilizing the square shape item and its part capacities −
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 -- calling measure function
r1.display;
ELSE
r2.display;
END IF;
END;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Length: 8
Width: 9
Length: 5
Width: 7
PL/SQL procedure successfully completed.
Using Order method
Presently, a similar impact could be accomplished utilizing a request technique. Allow us to reproduce the square shape object utilizing a request strategy −
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
member procedure display,
order member function measure(r rectangle) return number
);
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Type created.
Making the sort 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
IF(sqrt(self.length*self.length + self.width*self.width)>
sqrt(r.length*r.length + r.width*r.width)) then
return(1);
ELSE
return(-1);
END IF;
END measure;
END;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Type body created.
Utilizing the square shape item and its part capacities −
DECLARE
r1 rectangle;
r2 rectangle;
BEGIN
r1 := rectangle(23, 44);
r2 := rectangle(15, 17);
r1.display;
r2.display;
IF (r1 > r2) THEN -- calling measure function
r1.display;
ELSE
r2.display;
END IF;
END;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
Length: 23
Width: 44
Length: 15
Width: 17
Length: 23
Width: 44
PL/SQL procedure successfully completed.
Inheritance for PL/SQL Objects
PL/SQL permits making object from the current base items. To execute legacy, the base articles ought to be pronounced as NOT FINAL. The default is FINAL.
The accompanying projects show the legacy in PL/SQL Objects. Allow us to make another article named TableTop, this is acquired from the Rectangle object. For this, we need to make the base square shape object −
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
member function enlarge( inc number) return rectangle,
NOT FINAL member procedure display) NOT FINAL
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Type created.
Making the base sort 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;
END;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Type body created.
Making the youngster object tabletop −
CREATE OR REPLACE TYPE tabletop UNDER rectangle
(
material varchar2(20),
OVERRIDING member procedure display
)
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Type created.
Making the sort body for the kid object tabletop
CREATE OR REPLACE TYPE BODY tabletop AS
OVERRIDING MEMBER PROCEDURE display IS
BEGIN
dbms_output.put_line('Length: '|| length);
dbms_output.put_line('Width: '|| width);
dbms_output.put_line('Material: '|| material);
END display;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
Type body created.
Utilizing the tabletop article and its part capacities −
DECLARE
t1 tabletop;
t2 tabletop;
BEGIN
t1:= tabletop(20, 10, 'Wood');
t2 := tabletop(50, 30, 'Steel');
t1.display;
t2.display;
END;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Length: 20
Width: 10
Material: Wood
Length: 50
Width: 30
Material: Steel
PL/SQL procedure successfully completed.
Abstract Objects in PL/SQL
The NOT INSTANTIABLE proviso permits you to announce a theoretical item. You can't utilize a theoretical article all things considered; you should make a subtype or kid sort of such items to utilize its functionalities.
For instance,
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)
NOT INSTANTIABLE NOT FINAL
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Type created.