Oracle PL/SQL Object Types Tutorial with EXAMPLES

Details

What is Object Type in PL/SQL?

Object-Oriented Programming is especially suited for building reusable components and complex applications. They are organized around "objects" rather than "actions" i.e. the programs are designed to work and interact with the entire object rather than the single action. This concept allows the programmer to populate and manipulate the details at object entities level.

Below picture depicts an example of the object type in which a bank account is considered an object entity. The object attributes include things which are holding some attribute values, for example in Bank Account; it is Account number, bank balance, etc. while object methods describe the things like calculating interest rate, generating bank statement, etc. which requires certain process to be completed.

Object Types in PL/SQL

In PL/SQL object-oriented programming is based on object types.

An object type can represent any real-world entity. We are going to discuss more object types in this chapter.

In this tutorial - you will learn,

Components of Object Types

PL/SQL object type contains mainly two components.

  1. Attributes
  2. Members/Methods

Attributes

Attributes are the column or field in which data are stored. Each attribute will be mapped to the datatype that defines the processing and storage type for that attribute. The attribute can be of any valid PL/SQL datatype, or it can be of another object type.

Members/Methods

Members or Methods are subprograms that is defined in the object type. They are not used to store any data. They are mainly used to define process inside the object type. For examples validating data before populating the object type. They are declared in the object type section and defined in the object type body section of the object type. Body section in object type is an optional part. If no members are present, then an object type will contain no body part.

Create Object in Oracle

An Object type cannot be created at subprogram level, They can be created only at the schema level. Once the object type is defined in the schema, then the same can be used in subprograms. The object type can be created using 'CREATE TYPE'. The type body can be created only after creating its object type.

Object Types in PL/SQLObject Types in PL/SQL 

CREATE TYPE<object_type_name> AS OBJECT
(
<attribute_l><datatype>,
.
.
);
/
CREATE TYPE BODY<object_type_name> AS OBJECT
(
MEMBER[PROCEDURE|FUNCTION]<member_name> 
IS
<declarative section>
BEGIN
<execution part>
END;‭
.
.	‬
);
/

Syntax Explanation:

Declaration Initialization of Object Type

Like other components in PL/SQL, object types are also needed to be declared before using them in the program.

Once the object type is created it can be used in subprogram declarative section to declare a variable of that object type.

Whenever any variable is declared in the subprogram as object type, at run-time a new instance of the object type will be created, and this newly created instance can be referred to the variable name. By this way, a single object type can store multiple values under different instances.

Object Types in PL/SQL 

DECLARE
<variable_name> <object_type_name>;
BEGIN
.
.
END;
/

Syntax Explanation:

Once the variable is declared as an object type in a subprogram, it will be atomically null i.e. the entire object itself a null. It needs to be initialized with values to use them in the program. They can be initialized using constructors.

Constructors are the implicit method of an object that can be referred with the same name as that of the object type. The below syntax shows the initialization of the object type.

Object Types in PL/SQL 

DECLARE
<variable_name> <object_type_name>; 
BEGIN
<variable_name>:=<object_type_name>();
END;
/

Syntax Explanation:

Constructors

Constructors are the implicit method of an object that can be referred with the same name as that of the object type. Whenever the object is referred for the first time, this constructor will be called implicitly.

We can also initialize the objects using these constructor. The constructor can be defined explicitly by defining the member in object type body with the same name of the object type.

Example 1: In the following example we are going to use the object type member to insert the record into emp table with values ('RRR', 1005, 20000, 1000) and ('PPP', 1006, 20000, 1001). Once the data is inserted, we are going to display the same using object type member. We are also going to use the explicit constructor to populate the manager id by default with 1001 value for the second record.

We are going to execute it in below steps.

Step 1) Create Object type and Object type body

Object Types in PL/SQL 

CREATE TYPE emp_object AS OBJECT(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager NUMBER,
CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER, p_emp_name VARCHAR2,
p_salary NUMBER) RETURN SELF AS RESULT),
MEMBER PROCEDURE insert_records,
MEMBER PROCEDURE display_records);
/

Object Types in PL/SQL 

CREATE OR REPLACE TYPE BODY emp_object AS
CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER,p_emp_name VARCHAR2,
p_salary NUMBER)
RETURN SELF AS RESULT
IS
BEGIN
Dbms_output.put_line(’Constructor fired..');
SELF.emp_no:=p_emp_no;|
SELF.emp_name:=p_emp_name;
SELF.salary:=p_salary;
SELF.managerial:=1001;
RETURN;
END:
MEMBER PROCEDURE insert_records
IS
BEGIN
INSERT INTO emp VALUES(emp_noemp_name,salary,manager);
END
MEMBER PROCEDURE display_records
IS
BEGIN
Dbms_output.put_line('Employee Name:'||emp_name);
Dbms_output.put_line('Employee Number:'||emp_no);
Dbms_output.put_line('Salary':'||salary);
Dbms_output.put_line('Manager:'||manager);
END:
END:
/

Code Explanation

Output

Type created

Type body created

Step 2) Creating anonymous block to call created object type through implicit constructor for emp_no 1005

Object Types in PL/SQL 

DECLARE
guru_emp_det emp_object;
BEGIN
guru_emp_det:=emp_object(1005,’RRR',20000,1000);
guru_emp_det.display_records;
guru_emp_det.insert_records;
COMMIT;
END;

Code Explanation

Output

Employee Name: RRR

Employee Number: 1005

Salary: 20000

Manager : 1000

Step 3) Creating anonymous block to call created object type through explicit constructor for emp_no 1006

Object Types in PL/SQL 

DECLARE
guru_emp_det emp_object;
BEGIN
guru_emp_det:=emp_object(1006,'PPP',20000);
guru_emp_det.display_records;
guru_emp_det.insert_records;
COMMIT;
END;
/

Output 

Employee Name:PPP 
Employee Number:1006 
Salary:20000 
Manager:1001

Code Explanation:

Inheritance in Object Type

Inheritance property allows the sub-object type to access all the attribute and members of the super object type or parent object type.

The sub-object type is called inherited object type, and the super object type is called parent object type. The below syntax shows the how to create parent and inherited object type.

Object Types in PL/SQL 

CREATE TYPE <object_type_name_parent> AS OBJECT
(
<attribute_l><datatype>,
.
.
)NOT FINAL;
/

Syntax Explanation:

Object Types in PL/SQL 

CREATE TYPE<object_type_name_sub>UNDER<object_type_name_parent>
(
<attribute_l><datatype>,
.
);
/

Syntax Explanation:

Example1: In the below example, we are going to use the inheritance property to insert the record with manager id as '1002' for the following record ('RRR', 1007, 20000).

We are going to execute the above program in the following steps

Step 1) Create SUPER type or Parent type.

Object Types in PL/SQL 

CREATE TYPE emp_object AS OBJECT(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager NUMBER,
CONSTRUCTOR FUNCTION emp_object(p_emp_no NUMBER,p_emp_name VARCHAR2(50),
p_salary NUMBER)RETURN SELF AS RESULT),
MEMBER PROCEDURE insert_records,
MEMBER PROCEDURE display_records)NOT FINAL;
/

Code Explanation:

Step 2) Create SUB type under SUPER type.

Object Types in PL/SQL 

CREATE OR REPLACE TYPE sub_emp_object 
UNDER emp_object
(default_manager NUMBER,MEMBER PROCEDURE insert_default_mgr);
/


CREATE OR REPLACE TYPE BODY sub_emp_object 
AS
MEMBER PROCEDURE insert_default_mgr 
IS
BEGIN
INSERT INTO emp
VALUES(emp_no,emp_name:salary,manager):
END;
END;
/

Code Explanation:

Step 3) Creating anonymous block to call the SUB type

Object Types in PL/SQL 

DECLARE
guru_emp_det sub_emp_object;
BEGIN
guru_emp_det:= sub_emp_object(1007,'RRR',20000,1000,1002);
guru_emp_det.insert_default_mgr;
COMMIT;
END;
/

Code Explanation:

Equality of PL/SQL Objects

The object instance that belongs to the same objects can be compared for equality. For this, we need to have the special method in the object type called 'ORDER' method.

This 'ORDER' method should be the function that returns numerical type. It takes two parameters as input, (first parameter: id of the self-object instance, second parameter: id of another object instance).

The id of the two object instance is compared, and the result is returned in numerical.

Object Types in PL/SQL 

CREATE TYPE BODY<object_type_name_ 1>AS OBJECT
(
  ORDER MEMBER FUNCTION match(<parameter> object_type_name_ 1)
  RETURN INTEGER IS		
  BEGIN
    IF <attribute_name>parameter <attribute_name>THEN
      RETURN -1; --any negative number will do
    ELSIF id>c.id THEN
      RETURN 1; —any positive number will do
    ELSE
      RETURN 0;
    END IF;
  END;
  .
  .
);
/

Syntax Explanation:

Example1: In the following example we are going to see how to compare two objects. We are going to create two instances and we are going to compare attribute 'salary' between them. We are going to do int two steps.

Step 1) Creating the Object type and body.

Object Types in PL/SQL

Object Types in PL/SQL 

CREATE TYPE emp_object_equality AS OBJECT(
salary NUMBER,
ORDER MEMBER FUNCTION equals(c emp_object_equality)RETURN INTEGER);
/
CREATE TYPE BODY emp_object_equality AS
ORDER MEMBER FUNCTION equals(c emp_object_equality)RETURN INTEGER 
IS
BEGIN‭	‬
IF salary<c.salary
THEN RETURN -1;
ELSIF salary>c.salary
THEN RETURN 1;
ELSE
RETURN 0;
END IF:‭	‬
END;
END;
/

Code Explanation:

Code Output:

Type created

Step 2) Creating the anonymous block to call compare the object instance.

Object Types in PL/SQL 

DECLARE
l_obj_l emp_object_equality;
l_obj_2 emp_object_equality;
BEGIN
l_obj_l:=emp_object_equality(15000); 
l_obj_2:=emp_object_equality(17000);
IF l_obj_1.equalS(l_obj_2)>0
THEN
Dbms_output.put_line(’Salary of first instance is greater’):
ELSIF l_obj_l.equalS(l_obj_2)<0
THEN
Dbms_output.put_line(’Salary of second instance is greater’); 
ELSE
Dbms_output.put_line(’Salaries are equal’);
END IF;
END;
/

Output 

Salary of second instance is greater

Code Explanation:

Summary

In this chapter, we have seen the object type and their properties. We have also discussed about Constructors, Members, Attributes, Inheritance and Equality in PL/SQL objects.

 

YOU MIGHT LIKE: