Oracle Objects, Types and Collections: Part 2

This is a continuation of Object Orientation in Oracle. I guess I should have called it the Sorcerer of OOO. This is a technical entry.

Anyway, in part 1 I covered the basics of creating an Oracle object type and showing how it compared to a Java class. Today I’m going to cover the concepts of comparing objects and Inheritance. In part 3, I will cover type evolution and Polymorphism and in part 4 I will cover Object Views and Object Tables.

Comparing Objects

Many times, you will be working with multiple instances of the same object. You need a way to see if the instances, the instantiated object’s values, are the same. Oracle provides the ability to compare object instances using MAP or ORDER methods. In the type definition, you can declare a MAP method or an ORDER method but not both.

A MAP member function uses a scalar value for comparison. This function returns either a number, character or date column that can directly be compared with another object. You could, for instance, compare a primary key.

Continuing on with the sample class we developed in part 1, let’s modify our Calculator type and add a map member function. I’m going to drop the other methods for now to save space.

We drop the existing type:

DROP type calculator;

Create the new object:

CREATE OR REPLACE TYPE Calculator AS OBJECT (
  value NUMBER,
  MAP MEMBER FUNCTION comp RETURN NUMBER
  )
  NOT FINAL;
/

And the object Body:

CREATE OR REPLACE TYPE BODY Calculator AS
  MAP MEMBER FUNCTION comp RETURN NUMBER IS
  BEGIN
    RETURN value;
  END;
END;
/

Turn on serveroutput so that we can see the debug messages:

SET SERVEROUTPUT ON

And run the demo:

DECLARE
  calc Calculator;
  calc1 Calculator;
BEGIN

  -- Init 2 calculator with the same value and compare
  calc := Calculator(1);
  calc1 := Calculator(1);

  IF calc = calc1 THEN
    DBMS_OUTPUT.put_line( 'They are equal.' );
  ELSE
    DBMS_OUTPUT.put_line( 'They are not equal.' );
  END IF;

  -- Init 2 calculator with different values and compare
  calc := Calculator(1);
  calc1 := Calculator(2);

  IF calc = calc1 THEN
    DBMS_OUTPUT.put_line( 'They are equal.' );
  ELSE
    DBMS_OUTPUT.put_line( 'They are not equal.' );
  END IF;

END;
/

And that’s how a MAP function works. In a more complex type, you may add up all of the numeric values and create a hash total or any other functionality as long as it returns a single scalar value.

But what if you need to compare more complex criteria. What if you need to compare every attribute in an instance against another instance? In this case, you would use an ORDER member function. An ORDER function accepts a single parameter, an instance of the type, and compares that instance against the current instance. An ORDER method must always return either -1, 0 or 1. Let’s recreate our type and see how this works.

Drop the type and recreate with an Order Member Function:

DROP type calculator;

CREATE OR REPLACE TYPE Calculator AS OBJECT (
  value NUMBER,
  ORDER MEMBER FUNCTION comp(p_calc Calculator) RETURN INTEGER
  )
  NOT FINAL;
/

And the new body:

CREATE OR REPLACE TYPE BODY Calculator AS

  ORDER MEMBER FUNCTION comp( p_calc Calculator) RETURN INTEGER IS
    v_ret_val INTEGER;
  BEGIN
    CASE 
    WHEN SELF.value < p_calc.value THEN
      v_ret_val := -1;
    WHEN SELF.value = p_calc.value THEN
      v_ret_val := 0;
    WHEN SELF.value > p_calc.value THEN
      v_ret_val := 1;
    END CASE;

    RETURN v_ret_val;
  END;

END;
/

And then run the demo:

SET SERVEROUTPUT ON

DECLARE
  calc Calculator;
  calc1 Calculator;
  calc2 Calculator;
BEGIN
  calc := Calculator(1);
  calc1 := Calculator(1);
  calc2 := Calculator(2);

  -- They are the same, expect 0
  DBMS_OUTPUT.put_line( 'Calc(calc1): ' || 
        to_char(calc.comp(calc1)) );

  -- Calc less than calc2, expect -1
  DBMS_OUTPUT.put_line( 'Calc(calc2): ' || 
        to_char(calc.comp(calc2)) );

  -- Calc2 greater than calc1, expect 1
  DBMS_OUTPUT.put_line( 'Calc2(calc1): ' || 
        to_char(calc2.comp(calc1)) );

END;
/

Obviously, this is a very simple example. This particular type is better suited for a MAP function rather than an ORDER function. An ORDER function is useful when you have a complex comparison needs and/or need to compare multiple attributes. You might decide that if a key attribute is null, it will be less than a completely full instance and the ORDER will return a -1. You might also decide that an instance with all attributes filled is always a greater value than a partially full instance. The rule I follow is how complex the COMPARISON of the type is, not necessarily the complexity of the type itself.

And that’s type comparison in a nutshell. Let’s move on to Inheritance.

Inheritance

What is type inheritance? A child might inherit a parent’s eye or hair color. You could say that eye and hair color are attributes of both the parent and the child. Type inheritance works along the same lines.

Let’s say this is our type:

CREATE OR REPLACE TYPE Calculator AS OBJECT (
  value NUMBER,
  CONSTRUCTOR FUNCTION Calculator( value IN NUMBER ) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION Calculator RETURN SELF AS RESULT,
  MAP MEMBER FUNCTION comp RETURN NUMBER,
  MEMBER PROCEDURE print,
  MEMBER PROCEDURE add1( p_amt IN NUMBER ),
  MEMBER PROCEDURE subtract( p_amt IN NUMBER )
  )
  NOT FINAL;
/

We also have our associated type body.

CREATE OR REPLACE TYPE BODY Calculator AS

  CONSTRUCTOR FUNCTION Calculator( value IN NUMBER ) 
     RETURN SELF AS RESULT IS
  BEGIN
    SELF.value := value;
    print;
    RETURN;
  END;

  CONSTRUCTOR FUNCTION Calculator 
     RETURN SELF AS RESULT IS
  BEGIN
    SELF.value := 0;
    print;
    RETURN;
  END;

  MAP MEMBER FUNCTION comp RETURN NUMBER IS
  BEGIN
    RETURN value;
  END;

  MEMBER PROCEDURE print IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE( TO_CHAR( value ) );
  END;

  MEMBER PROCEDURE add1( p_amt IN NUMBER )  IS 
  BEGIN
    value := value + p_amt;
    print;
  END;

  MEMBER PROCEDURE subtract( p_amt IN NUMBER )  IS 
  BEGIN
    value := value - p_amt;
    print;
  END;

END;
/

Now we want to create a more advanced version of our type. Let’s call it the AdvancedCalculator. Our AdvancedCalculator has an additional attribute and two new methods. The new type will inherit all of the existing functionality of our base, or super type.

The last statement in our create type command is NOT FINAL. This allows us to subtype this type. If those keywords are not present, you cannot create a subtype.

CREATE TYPE AdvancedCalculator UNDER Calculator (
  pi FLOAT,
  CONSTRUCTOR FUNCTION AdvancedCalculator RETURN SELF AS RESULT,
  MEMBER PROCEDURE multiply( p_amt IN NUMBER ),
  MEMBER PROCEDURE divide( p_amt IN NUMBER )
   )
NOT FINAL;
/

If you describe the new AdvancedCalculator, you will see that everything available with Calculator is now available for AdvancedCalculator. I created a constructor for this type so that I won’t have to supply Pi when I use it.

Now we will create our body:

CREATE OR REPLACE TYPE BODY AdvancedCalculator AS

  CONSTRUCTOR FUNCTION AdvancedCalculator 
     RETURN SELF AS RESULT IS
  BEGIN

    SELF.value := 0;
    self.pi := 3.14159;
    SELF.print;

    RETURN;
  END;

  MEMBER PROCEDURE multiply( p_amt IN NUMBER )  IS 
  BEGIN
    value := value * p_amt;
    SELF.print;
  END;

  MEMBER PROCEDURE divide( p_amt IN NUMBER )  IS
  BEGIN
    value := value / p_amt;
    SELF.print;
  END;

END;
/

Our AdvancedCalculator has inherited the VALUE attribute and all of the methods of Calculator. It added a new PI attribute, a new constructor and 2 new member procedures. Below is a sample program:

DECLARE
  calc Calculator;
  calc1 AdvancedCalculator;
BEGIN

  calc := Calculator(1);
  calc1 := AdvancedCalculator();

  calc.add1(5);
  calc1.add1(3);
  calc1.multiply(10);
  calc1.divide(2);

  IF calc != calc1 THEN
    DBMS_OUTPUT.PUT_LINE( 'Calc, ' ||
          to_Char(calc.value) || ', does not equal calc1, ' ||
          to_char(calc1.value) );
  END IF;

  calc.value := 5;
  calc1.value := 5;

  IF calc = calc1 THEN
    DBMS_OUTPUT.PUT_LINE( 'Calc, ' ||
          to_Char(calc.value) || ', does equal calc1, ' ||
          to_char(calc1.value) );
  END IF;

END;
/

Notice that we used Calculator interchangeably with AdvancedCalculator. That is where the power of inheritance really kicks in. Oracle is aware of the type hierarchy and knows how to compare them. It also knows when a type can be used in place of a supertype and vice versa. In part 4, I will also talk about the CAST function.

And here is an important note: The subtype does not copy the attributes and methods from the supertype. They are permanently linked. If you change the behavior or attributes of the supertype, the subtype will reflect those changes, i.e. it will inherit them. And if that’s not scary enough, type can be implemented as physical structures in the database, i.e. table and views. More on that in the future though.

And that’s it for today. In Part 3 I will cover type evolution and polymorphism. In Part 4 I will cover object views and object tables.

If you’re interested, here’s a few OO, not Oracle, links I’ve read recently:

David Foderick’s Blog

Why Object Oriented Encapsulation is Obsolete

Teaching Object Oriented Programming

Intro to Object-Oriented Programming with Java

Have a good one,

Lewis

Technorati : , , , , , , , , , , , , , , , , , , , , , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.