Translated by Ilinca Topciu on 30.03.2018
In most modern programming languages, programming is object-oriented. Even though the scripts that are necessary in databases are usually quite simple and do specific things over a database, working with objects is allowed in PL/SQL.
Some of the OOP (C++ or Java) concepts that you already know are implemented here. You can create types, derive an object from other existing objects and so on. Like in OOP programming, objects have a set of properties (or attributes) and a set of methods. Attributes are used to represent the state of the object, while methods are used to simulate the behaviour of various objects.
Creating a class in PL/SQL
The "class" an object belongs to is, in fact, a type which we can declare using this syntax:
CREATE OR REPLACE TYPE class_name AS OBJECT ( ... );
In the section between parenthesis we declare the attributes of the object and their type, along with procedures and member functions. We have the following example (we will create a student type which we will use to "instantiate" students):
CREATE OR REPLACE TYPE student AS OBJECT (lname varchar2(10), fname varchar2(10), groupno varchar2(4), year number(1), date_of_birth date, member procedure display_report_card ); /
You cannot use %TYPE when declaring an object. This is not valid because if somebody wants to modify the table based on which the object was created, then the object would be damaged (and probably the subprograms that use the object as well).
When declaring an object, you need to declare the body of the object as well. In this body you can write the implementation of the methods declared in the object (in our case the procedure display_report_card).
To create the body section we have the following syntax:
CREATE OR REPLACE TYPE BODY student AS ................. END;
Between the header of the BODY and the word END we write the implementation of all methods. For the student object from above we could declare the following BODY:
CREATE OR REPLACE TYPE BODY student AS MEMBER PROCEDURE display_report_card IS BEGIN DBMS_OUTPUT.PUT_LINE('This procedure computes and displays the report card'); END display_report_card; END; /
We can create tables in which we introduce as a column instance-values of the previously declared type (student). If we execute the following command we will create a table of type key-value in which the key is the registration number of the student and the value is the object:
CREATE TABLE oop_students (registration_number VARCHAR2(4), object STUDENT);
Here is an example of declaring and using an object of type student and a command for inserting in the newly created table:
set serveroutput on; DECLARE v_student1 STUDENT; v_student2 STUDENT; BEGIN v_student1 := student('Smith', 'John', 'A2', 3, TO_DATE('11/04/1994', 'dd/mm/yyyy')); v_student2 := student('Jones', 'George', 'A4', 3, TO_DATE('22/03/1995', 'dd/mm/yyyy')); v_student1.display_report_card(); dbms_output.put_line(v_student1.lname); insert into oop_students values ('100', v_student1); insert into oop_students values ('101', v_student2); END;
You can also declare functions inside objects (similarly to how we created procedures but with the differences specific to functions). The header of the function from the body will be put in the declaration of the object. Do not forget the keyword "Member".
As you can see in the example given, an object has a constructor (which is called through the name of the object's type) which takes as parameters the values of the attributes from that object (in the order of the declaration) and assigns them to the object. If you want to have your own constructor (and not the implicit one), here is how you can do this:
In the declaration of the object add the constructor as a method:
CONSTRUCTOR FUNCTION student(lname varchar2, fname varchar2) RETURN SELF AS RESULT
Then, in the body, specify what you want to do in this new constructor-method:
CONSTRUCTOR FUNCTION student(lname varchar2, fname varchar2) RETURN SELF AS RESULT AS BEGIN SELF.lname := lname; SELF.fname := fname; SELF.date_of_birth := sysdate; SELF.year := 1; SELF.groupno := 'A1'; RETURN; END;
Declare a new variable of type student and instantiate it using the new constructor which we have just defined.
Beside the constructor, another "more special" method is the one that allows the comparison of two object of the same type. This function is necessary because, normally, the DBMS does not know which of the two variables (v_student1 and v_student2) is bigger. In case we want to sort the table oop_students (declared above) after the column object, what is the order taken into consideration (how would two elements from this column be compared? after lname, fname or age?)
Sorting and comparing
Next, we invite you to create, inside the student object, a function (called age_in_days) which returns the age of the student in days (decrease from sysdate the value of date_of_birth from the object). We will use this function to compare 2 students.
There are 2 methods of comparing 2 objects: through a MAP method or an ORDER method. The method of type MAP will return a value (of type number for example). If the first object returns a number which is less than the second object than it will be considered smaller.
Adding the word "MAP" in front of the function age_in_days (which you have previously created), will enable you to compare 2 objects of type student based on the number of days that have passed since they were born.
Add in the anonymous block the following code section:
if (v_student1 < v_student2) THEN DBMS_OUTPUT.PUT_LINE('Student '|| v_student1.lname || ' is younger.'); ELSE DBMS_OUTPUT.PUT_LINE('Student '|| v_student2.lname || ' is younger.'); END IF;
In most programming languages, the comparison function is not based on the object's representation as a value which could be compared to the one obtained through the similar representation of another object, but rather by returning -1, 0 or 1 (if the current object SELF in PLSQL or this in Java is -1: smaller, 0: equal, 1:bigger than the object given as parameter).
The difference between the two functions: MAP and ORDER is that while the MAP type one does not need any parameters because it returns the current object's representation as a comparable data type, the ORDER type function has as mandatory parameter an object of the same type as the one that it will be compared to. Both methods cannot be used at the same time, for the comparison that is going to be made using ORDER, the code above for MAP is still valid. Try to rewrite the MAP function in order for it to be of type ORDER (actually, when you compare (v_student1 < v_student2) you can imagine calling something like "v_student1.compareWith(v_student2)" and the function compareWith is the one that needs to be declared using ORDER).
Inheritance in PLSQL
In PLSQL, like in other object-oriented languages, we can create objects starting from existing objects. In order to create a child-object, the parent class (superclass) needs to be declared as NOT FINAL (predefined value is FINAL). Rewrite student type using the words NOT FINAL at the end. To allow method overriding, in front of its declaration we need to add the words NOT FINAL.
In our example we can create the declaration of the student as follows:
CREATE OR REPLACE TYPE student AS OBJECT (lname varchar2(10), fname varchar2(10), groupno varchar2(4), year number(1), date_of_birth date, NOT FINAL member procedure display_report_card, map member FUNCTION age_in_days RETURN NUMBER, CONSTRUCTOR FUNCTION student(lname varchar2, fname varchar2) RETURN SELF AS RESULT ) NOT FINAL; /
In this case, a new type (class) can be created as a subclass for student and in this new class we can override the procedure display_report_card.
In the following code, we create a student subclass which has an additional property "scholarship" and which overrides the display_report_card method. You also have the anonymous block used to declare and use this new object type.
drop type knowledgeable_student; CREATE OR REPLACE TYPE knowledgeable_student UNDER student ( scholarship NUMBER(6,2), OVERRIDING member procedure display_report_card ) / CREATE OR REPLACE TYPE BODY knowledgeable_student AS OVERRIDING MEMBER PROCEDURE display_report_card IS BEGIN dbms_output.put_line('scholar'); END display_report_card; END; / DECLARE v_knowledgeable_student knowledgeable_student; BEGIN v_knowledgeable_student := knowledgeable_student('Smith', 'John', 'A1', 2, TO_DATE('18/09/1996', 'dd/mm/yyyy'), 1000); dbms_output.put_line(v_knowledgeable_student.lname); v_knowledgeable_student.display_report_card(); END;
Create the 2 instances of knowledgeable_student and compare them. Is the comparison method used in comparing 2 objects of type student maintained?
Execute the command:
select * from oop_students order by object; -- does this command use the order established by the MAP/ORDER function?
To create an abstract class use NOT INSTANTIABLE after declaring the class (before NOT FINAL). Does it make sense to use NOT INSTANTIABLE without it being followed by NOT FINAL ?
Execute the code:
DECLARE s student; BEGIN SELECT object INTO s FROM oop_students WHERE registration_number='100'; dbms_output.put_line(s.lname); END;
Exercise 1 (10pt)
- (2pt) Create a class (any class). The class must have at least 2 methods and at least one additional constructor.(besides the implicit one)
- (2pt) For the class created at the previous point, implement a method that enables the comparison of 2 objects (MAP or ORDER), insert a series of objects in a table and try to sort the column where you have inserted the objects to demonstrate that the objects can really be compared.
- (2pt) Create a subclass that has as base class the class created at the first point. Overwrite at least one method from the existing ones(from point 1). Give an example of a method that cannot be overwritten.
- (2pt) Overloading is done by declaring functions that have the same name but different parameters. Exemplify this process for one of the classes that you have created (the one at point 1 or the one from point 3).
- (2pt) Create a PLSQL code and a Java code through which you will serialize/deserialize objects directly into/from a database: you will save the properties of the Java object in the database as a PLSQL object (in the table from point 2) and you will be able to recreate the objects in Java from the data stored in the database.
Example: I have a car object in Java: brand:Dacia, model: Logan, color: White, fabrication year: 2018. When I call the function java having as parameter the object of type car, the function will write in the database the information about the car and return an identifier from the database representing the car (an ID field automatically added by the PLSQL function. In the database, an object will be created with the properties of the java object (Dacia brand, Logan model, white color, and fabrication year 2018 - we will see these properties using an example PLSQL script- don't write a sequence of bits representing the Java standard serialization). When you deserialize, you will give an ID and you will get the object. As a more interesting exercise, you could implement the Serializable interface from Java in order to serialize and deserialize. Java is compulsory but the interface is not. It is enough to show the serialization and deserialization in the console.