In this blog, we’ll discuss Object-Oriented PL/SQL. PL/SQL allows defining an object type, which helps in designing electronic database in Oracle. An object type allows you to make composite types. Using objects allow you to implement world objects with specific structure of knowledge and methods for operating it. Objects have attributes and methods. Attributes are properties of an object and are used for storing an object’s state; and methods are used for modelling its behaviour.
Objects are created using the CREATE [OR REPLACE] TYPE statement. Following is an example to make an easy address object consisting of few attributes −
CREATE OR REPLACE TYPE address AS OBJECT

(house_no varchar2(10), 
street varchar2(30), 
city varchar2(20), 
state varchar2(10), 
pincode varchar2(10) 
); 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
Type created.
Let’s create another object customer where we’ll wrap attributes and methods together to possess object-oriented feeling −
CREATE OR REPLACE TYPE customer AS OBJECT

(code number(5), 
name varchar2(30), 
contact_no varchar2(12), 
addr address, 
member procedure display 
); 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
Type created.
Instantiating an Object
Defining an object type provides a blueprint for the thing . To use this object, you would like to make instances of this object. you’ll access the attributes and methods of the thing using the instance name and therefore the access operator (.) 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; 
/

When the above code is executed at the SQL prompt, it produces the subsequent result −
House No: 103A
Street: M.G.Road
City: Jaipur
State: Rajasthan
Pincode: 201301

PL/SQL procedure successfully completed.
Member Methods
Member methods are used for manipulating the attributes of the thing . You provide the declaration of a member method while declaring the thing type. the thing body defines the code for the member methods. the thing body is made using the CREATE TYPE BODY statement.
Constructors are functions that return a replacement object as its value. Every object features a system defined constructor method. The name of the constructor is same because the object type. for instance −
residence := address(‘103A’, ‘M.G.Road’, ‘Jaipur’, ‘Rajasthan’,’201301′);
The comparison methods are used for comparing objects. There are two ways to match objects −
Map method
The Map method may be a function implemented in such how that its value depends upon the worth of the attributes. for instance , for a customer object, if the customer code is same for 2 customers, both customers might be an equivalent . therefore the relationship between these two objects would depend on the worth of code.
Order method
The Order method implements some internal logic for comparing two objects. for instance , for a rectangle object, a rectangle is greater than another rectangle if both its sides are bigger.
Using Map method
Let us attempt to understand the above concepts using the subsequent rectangle 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
);
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Type created.
Creating 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;
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Type body created.
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 — calling measure function
r1.display;
ELSE
r2.display;
END IF;
END;
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Length: 8
Width: 9
Length: 5
Width: 7

PL/SQL procedure successfully completed.
Using Order method
Now, an equivalent effect might be achieved using an order method. allow us to recreate the rectangle object using an order method −
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
member procedure display,
order member function measure(r rectangle) return number
);
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Type created.
Creating 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;
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Type body created.
Using the rectangle object and its member functions −
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;
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Length: 23
Width: 44
Length: 15
Width: 17
Length: 23
Width: 44

PL/SQL procedure successfully completed.
Inheritance for PL/SQL Objects
PL/SQL allows creating object from the prevailing base objects. To implement inheritance, the bottom objects should be declared as NOT FINAL. The default is FINAL.
The following programs illustrate the inheritance in PL/SQL Objects. allow us to create another object named TableTop, this is often inherited from the Rectangle object. For this, we’d like to make the bottom rectangle 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
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Type created.
Creating the bottom 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;
END;
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Type body created.
Creating the kid object tabletop −
CREATE OR REPLACE TYPE tabletop UNDER rectangle
(
material varchar2(20),
OVERRIDING member procedure display
)
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Type created.
Creating 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;
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Type body created.
Using the tabletop object and its member functions −
DECLARE
t1 tabletop;
t2 tabletop;
BEGIN
t1:= tabletop(20, 10, ‘Wood’);
t2 := tabletop(50, 30, ‘Steel’);
t1.display;
t2.display;
END;
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
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 clause allows you to declare an abstract object. you can’t use an abstract object because it is; you’ll need to create a subtype or child sort of such objects to use its functionalities.
For example,
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)
NOT INSTANTIABLE NOT FINAL
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Type created.
So, this brings us to the end of blog. This Tecklearn ‘Object Oriented PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

499