When an object type is instantiated (i.e. assigned a non-null value) it will execute initialization code. This initialization code is called a CONSTRUCTOR function. Every object type includes an implicit constructor which simply assigns values to the object attributes according to the values provided in the object assignment.
For example:
CREATE OR REPLACE TYPE simpleobject AS OBJECT(a NUMBER, b NUMBER, c NUMBER);
I can create an instance of the object by providing it’s name and values for the attributes.
SQL> select simpleobject(1,2,3) example from dual; EXAMPLE(A, B, C) ---------------------- SIMPLEOBJECT(1, 2, 3)
or, in pl/sql
SQL> set serveroutput on SQL> DECLARE 2 v_obj simpleobject := simpleobject(1, 2, 3); 3 BEGIN 4 DBMS_OUTPUT.put_line(v_obj.a); 5 DBMS_OUTPUT.put_line(v_obj.b); 6 DBMS_OUTPUT.put_line(v_obj.c); 7 END; 8 / 1 2 3
That’s easy enough, but what if we don’t always know all of the values and only want to define some of them. In that case we define our own constructor by creating a function with the CONSTRUCTOR key word and naming it the same as our object. It’s not strictly necessary to declare the first parameter (SELF) as it will be implicit in any constructor, but it is common practice to do so.
So, either of these versions will provide equivalent functionality. First, with the SELF parameter explicitly defined:
CREATE OR REPLACE TYPE simpleobject AS OBJECT
(
a NUMBER,
b NUMBER,
c NUMBER,
CONSTRUCTOR FUNCTION simpleobject(self IN OUT simpleobject, p_a IN NUMBER)
RETURN SELF AS RESULT
);
CREATE OR REPLACE TYPE BODY simpleobject
IS
CONSTRUCTOR FUNCTION simpleobject(self IN OUT simpleobject, p_a IN NUMBER)
RETURN SELF AS RESULT
IS
BEGIN
self.a := p_a;
RETURN;
END;
END;
or using the implicit parameter:
CREATE OR REPLACE TYPE simpleobject AS OBJECT
(
a NUMBER,
b NUMBER,
c NUMBER,
CONSTRUCTOR FUNCTION simpleobject(a IN NUMBER)
RETURN SELF AS RESULT
);
CREATE OR REPLACE TYPE BODY simpleobject
IS
CONSTRUCTOR FUNCTION simpleobject(a IN NUMBER)
RETURN SELF AS RESULT
IS
BEGIN
self.a := a;
RETURN;
END;
END;
Now we have the option of instantiating with just the a parameter, or using all three attributes.
SELECT simpleobject(1), simpleobject(1, 2, 3) FROM DUAL;
DECLARE
v_obj simpleobject := simpleobject(1, 2, 3);
BEGIN
DBMS_OUTPUT.put_line(v_obj.a);
DBMS_OUTPUT.put_line(v_obj.b);
DBMS_OUTPUT.put_line(v_obj.c);
END;
DECLARE
v_obj simpleobject := simpleobject(1);
BEGIN
DBMS_OUTPUT.put_line(v_obj.a);
DBMS_OUTPUT.put_line(v_obj.b);
DBMS_OUTPUT.put_line(v_obj.c);
END;
Those are fairly simple. Let’s get a little more complicated. I’ll create a triangle object. It will have 6 attributes containing the 3 sides and 3 angles. Since the angles can be calculated if we have the 3 sides, we’ll create a constructor that only requires the side values and we’ll calculate the angles ourselves.
The triangle object then would look like this with attributes for the sides X,Y,Z and the corresponding angles opposite those sides, also X,Y, and Z.
The constructor accepts 3 values for the sides.
CREATE OR REPLACE TYPE my_triangle AS OBJECT
(
x_side NUMBER,
y_side NUMBER,
z_side NUMBER,
x_angle NUMBER,
y_angle NUMBER,
z_angle NUMBER,
CONSTRUCTOR FUNCTION my_triangle(self IN OUT my_triangle,
p_aside IN NUMBER,
p_bside IN NUMBER,
p_cside IN NUMBER)
RETURN SELF AS RESULT
);
In the body we can our constructor is quite a bit more complicated than the previous “simpleobject”. First I sort the inputs by size, and then using some rules from trigonometry we can calculate the values of the angles and set the attributes. I also add some parameter checking to make sure the input values can form a valid triangle. If they don’t then raise the VALUE_ERROR exception.
CREATE OR REPLACE TYPE BODY my_triangle
IS
CONSTRUCTOR FUNCTION my_triangle(self IN OUT my_triangle,
p_aside IN NUMBER,
p_bside IN NUMBER,
p_cside IN NUMBER)
RETURN SELF AS RESULT
IS
-- sides
a NUMBER;
b NUMBER;
c NUMBER;
-- angles
aa NUMBER;
bb NUMBER;
cc NUMBER;
BEGIN
-- All sides of a triangle must have positive length
IF p_aside <= 0 OR p_bside <= 0 OR p_cside <= 0
THEN
RAISE VALUE_ERROR;
END IF;
-- Assign a,b,c in descending order of parameter values
-- Using Cosine rule. solve for angle AA, opposite side a.
-- Then using Sine rule, solve for angle BB, opposite side b.
-- Once we know AA and BB, CC is calculated simply with 180 - AA - BB
-- The Oracle "acos" function return values in radians,
-- so convert all angles to degrees before assigning to object attributes
CASE GREATEST(p_aside, p_bside, p_cside)
WHEN p_aside
THEN
a := p_aside;
b := GREATEST(p_bside, p_cside);
c := LEAST(p_bside, p_cside);
WHEN p_bside
THEN
a := p_bside;
b := GREATEST(p_aside, p_cside);
c := LEAST(p_aside, p_cside);
WHEN p_cside
THEN
a := p_cside;
b := GREATEST(p_aside, p_bside);
c := LEAST(p_aside, p_bside);
END CASE;
-- Check Triangle Inequality Theorem
-- That is, the sum of the lengths of any two sides must be greater than the length of the third side.
-- Since we have ordered sides such that a >= b >= c, it is sufficient to test b+c > a
IF b + c <= a
THEN
RAISE VALUE_ERROR;
END IF;
aa := ACOS((b * b + c * c - a * a) / (2 * b * c)); -- cosine rule
bb := ASIN(b * SIN(aa) / a); -- sine rule
aa := aa * 180 / ACOS(-1); -- convert radians to degrees
bb := bb * 180 / ACOS(-1); -- convert radians to degrees
cc := 180 - aa - bb;
self.x_side := a;
self.y_side := b;
self.z_side := c;
self.x_angle := aa;
self.y_angle := bb;
self.z_angle := cc;
RETURN;
END;
END;
Trying out the object with some sample values, we see the sides are populated in descending order of length and each sides opposite angle is calculated correctly.
SQL> SELECT my_triangle(3, 4, 2) FROM DUAL; MY_TRIANGLE(3,4,2)(X_SIDE, Y_SIDE, Z_SIDE, X_ANGLE, Y_ANGLE, Z_ANGLE) ---------------------------------------------------------------------- MY_TRIANGLE(4, 3, 2, 104.477512, 46.5674634, 28.9550244) SQL> SELECT my_triangle(4, 3, 5) FROM DUAL; MY_TRIANGLE(4,3,5)(X_SIDE, Y_SIDE, Z_SIDE, X_ANGLE, Y_ANGLE, Z_ANGLE) ---------------------------------------------------------------------- MY_TRIANGLE(5, 4, 3, 90, 53.1301024, 36.8698976) SQL> SELECT my_triangle(7, 7, 7) FROM DUAL; MY_TRIANGLE(7,7,7)(X_SIDE, Y_SIDE, Z_SIDE, X_ANGLE, Y_ANGLE, Z_ANGLE) ---------------------------------------------------------------------- MY_TRIANGLE(7, 7, 7, 60, 60, 60)
As you can see, defining your own constructors allows for a lot of versatility in how you can use and populate your object’s attributes and I highly recommend exploring their use, looking for opportunities where they may benefit your object. From simply defaulting to nulls as shown in the simpleobject, to calculated results, or possibly just some formatting, such as using UPPER, LOWER, ROUND, etc. on your values at instantiation time to act as data cleanup.