• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

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 as 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 as 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 about 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 subprogram 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 example 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.

Creation of Objects

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

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 through the variable name. By this way, a single object type can store multiple values under different instances.

Object Types in PL/SQL

Syntax Explanation:

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

Once the variable is declared as an object type in a subprograms, 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 show the initialization of the object type.

Object Types in PL/SQL

Syntax Explanation:

  • The above syntax shows the initialization of the object type instance with 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

Object Types in PL/SQL

Object Types in PL/SQL

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

Object Types in PL/SQL

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

Object Types in PL/SQL

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.

Object Types in PL/SQL

Syntax Explanation:

  • The above syntax show the creation of SUPER type.

Object Types in PL/SQL

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.

Object Types in PL/SQL

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.

Object Types in PL/SQL

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

Object Types in PL/SQL

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.

Object Types in PL/SQL

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.

Object Types in PL/SQL

Object Types in PL/SQL

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.

Object Types in PL/SQL

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.

 

YOU MIGHT LIKE:
PL-SQL

Packages in PL/SQL

PL/SQL package is nothing but a logical grouping of a related subprogram (procedure/function) into a...