In this tutorial, we will introduce SQL* Plus and learn how to connect it to the database. After...
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.
In this tutorial - you will learn,
PL/SQL object type contains mainly two components.
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 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.
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; . . ); /
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; /
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; /
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
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_line('Employee Number:'||emp_no); Dbms_output.put_line('Salary':'||salary); Dbms_output.put_line('Manager:'||manager); END: END: /
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;
Employee Name: RRR
Employee Number: 1005
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; /
Employee Name:PPP Employee Number:1006 Salary:20000 Manager:1001
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; /
CREATE TYPE<object_type_name_sub>UNDER<object_type_name_parent> ( <attribute_l><datatype>, . ); /
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.
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; /
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; /
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; /
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.
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; . . ); /
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.
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; /
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; /
Salary of second instance is greater
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.
In this tutorial, we will introduce SQL* Plus and learn how to connect it to the database. After...
What is PL/SQL block? In PL/SQL, the code is not executed in single line format, but it is always...
What is For Loop? "FOR LOOP" statement is best suitable when you want to execute a code for a...
What are TCL Statements in PL/SQL? TCL stands for Transaction Control Statements. It will either save...
What is While Loop? WHILE loop statement works similar to the Basic loop statement except the EXIT...
What is CASE Statement? A CASE statement is similar to IF-THEN-ELSIF statement that selects one...