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. In this tutorial – you will learn,
Components of Object Types Create Object in Oracle Declaration Initialization of Object Type Constructors Inheritance in Object Type Equality of PL/SQL Objects
Components of Object Types
PL/SQL object type contains mainly two components.
Attributes 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.
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.
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.
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
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
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
Output 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.
Syntax Explanation:
The above syntax show the creation of SUPER type.
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.
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.
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
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.
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.
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.
Output 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.
