Oracle PL/SQL Object Types Tutorial with EXAMPLES

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.

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.

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.

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:

• The above syntax shows the creation of 'OBJECT' with attributes and 'OBJECT-BODY' with methods.
• The methods can also be overloaded in the object body.

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.

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

Syntax Explanation:

• The above syntax shows the declaration of a variable as an object type in the declarative section.

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.

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

Syntax Explanation:

• The above syntax shows the initialization of the object type instance with a null value.
• Now the object itself is not null as it has been initialized, but the attributes inside the object will be null as we have not assigned any values to these attributes.

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.

• Step1:
  • Create Object type
  • Object type body
• Step2: Creating an anonymous block to call created object type through implicit constructor for emp_no 1005.
• Step3: Creating an anonymous block to call created object type through explicit constructor for emp_no 1006.

Step 1) Create Object type and Object type body

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);
/

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_lme('Employee Number:'||emp_no);
Dbms_output.put_lme('Salary':'||salary);
Dbms_output.put_lme('Manager:'||manager);
END:
END:
/

Code Explanation

• Code line 1-9: Creating the 'emp_object' object type with 4 attributes and 3 members. It contains the definition of constructors with only 3 parameters. (Actual implicit constructor will contain the number of parameters equal to the number of attributes present in the object type)
• Code line 10: Creating the type body.
• Code line 11-21: Defining the explicit constructor. Assigning the parameter value to the attributes and assigning the value for attribute 'manager' with the default value '1001'.
• Code line 22-26: Defining the member 'insert_records' in which the attributes values are inserted into 'emp' table.
• Code line 27-34: Defining the member 'display_records' in which displaying the values of the object type attributes.

Output

Type created

Type body created

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

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

• Code line 37-45: Inserting the records using the implicit constructor. Call to constructor contains the actual number of attributes values.
• Code line 38: Declares the guru_emp_det as object type of 'emp_object'.
• Code line 41: Statement 'guru_emp_det.display_records' called the 'diplay_records' member function and the attributes values are displayed
• Code line 42: Statement 'guru_emp_det.insert_records' called the 'insert_records' member function and the attributes values are inserted into the table.

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

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:

• Code line 46-53: Inserting the records using the explicit constructor.
• Code line 46: Declares the guru_emp_det as object type of 'emp_object'.
• Code line 50: Statement 'guru_emp_det.display_records' called the 'display_records' member function and the attributes values are displayed
• Code line 51: Statement 'guru_emp_det.insert_records' called the 'insert_records' member function and the attributes values are inserted into the table.

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.

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

Syntax Explanation:

• The above syntax show the creation of SUPER type.

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

Syntax Explanation:

• The above syntax shows creation of SUB type. It contains all the members and attributes from the parent object type.

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

• Step1: Create SUPER type.
• Step2: Create SUB type and body.
• Step3: Creating an anonymous block to call the SUB type.

Step 1) Create SUPER type or Parent type.

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:

• Code line 1-9: Creating the 'emp_object' object type with 4 attributes and 3 members. It contains the definition of constructors with only 3 parameters. It has been declared as 'NOT FINAL' so it is parent type.

Step 2) Create SUB type under SUPER type.

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:

• Code line 10-13: Creating the sub_emp_object as inherited type with additional one attribute 'default_manager' and member procedure declaration.
• Code line 14: Creating the body for the inherited object type.
• Code line 16-21: Defining the member procedure which is inserting the records into "emp" table with the values from 'SUPER' object type, except for manager value. For manager value, it is using the 'default_manager' from 'SUB' type.

Step 3) Creating anonymous block to call the SUB type

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:

• Code line 25: Declaring 'guru_emp_det' as 'sub_emp_object' type.
• Code line 27: Initializing the object with the implicit constructor. The constructor is having 5 parameters (4 attributes from PARENT type and 2 attributes from SUB type). The last parameter (1002)defines the value for default_manager attribute
• Code line 28: Calling the member 'insert_default_mgr' to insert the records with the default manager id passed in the constructor.

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.

• Positive value represents that the SELF object instance is greater than another instance.
• Negative value represents that the SELF object instance is lesser than another instance.
• Zero represents that the SELF object instance is equal to another instance.
• If any of the instances is null, then this function will return null.

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:

• The above syntax shows the ORDER function that needs to be included in the type body for equality check.
• The parameter for this function should be an instance of the same object type.
• The above function can be called as "obj_instance_1.match(obj_instance_2)" and this expression will return the numerical value as shown, where obj_instance_1 and obj_instance_2 are the instance of object_type_name.

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.
• Step 2: Creating the anonymous block to call compare the object instance.

Step 1) Creating the Object type and body.

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 line 1-4: Creating the 'emp_object_equality' object type with 1 attributes and 1 member.
• Code line 6-16: Defining the ORDER function which compares the 'salary' attribute of SELF instance and parameter instance type. It returns negative if SELF salary is lesser or positive if SELF salary is greater and 0 if salaries are equal.

Code Output:

Type created

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

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:

• Code line 20: Declaring the l_obj_1 of emp_object_equality type.
• Code line 21: Declaring the l_obj_2 of emp_object_equality type.
• Code line 23: Initializing l_obj_1 with salary value as '15000'
• Code line 24: Initializing l_obj_1 with salary value as '17000'
• Code line 25-33: Print the message based on the return number from the ORDER function.

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.

Next