Oracle 11g :Inheritance in Oralce PLSQL

Get real time news update from your favorite websites.
Don't miss any news about your favorite topic.
Personalize your app.

Check out NTyles.

Get it on....

In my previous post I discussed a little about object oriented programming in oracle plsql. In previous post I gave a little example using OOP in oracle 11g.

In this post I will show you and give some example of one of the feature of the OOP, i.e Object Oriented Programming. The feature that I am going to show about is inheritance. I know all of you who worked with Java and .Net and other OOP languages must be familiar with Inheritance. And for those who don't know inheritance let me write something about it for you :

"Inheritance is that concept in OOP in oracle plsql, where a object or a relation inherits the specs i.e attributes (variables and methods) of another object. In oracle, inheritance relationship is established using primary-key and foriegn-key relationships(shared ID) in order to simulate the relationship between derived class and inherited class."

And if you guys are still wondering what an inheritance is , isn't there any one line sentence that I can understand whole thing, then I have one sentence for you :

We, humans are inherited from apes.

I now think you get the whole picture.


Let me take an opportunity to design a database for eBay,hehe, using Inheritance feature in oracle. Along the design process I will explain different types of inheritance features available in Oracle 11g. What I think about eBay is Users logs in, a logged in user may be buyer or bidder. Buyer is one who creates bid and Bidders are those who bids on the bids created by Buyer. (Everybody knows I am lazy and I am only talking this much for eBay. There are many other features too in eBay, but I think I will be able to describe inheritance in oracle plsql using user, buyer and bidder).

User logged in may be buyer and also a bidder. In inheritance this type of condition is know as
Union Inheritance.

1. Implementing Union Inheritance in Oracle 11g.

This is first design. All bidders and buyers are users and a user can be bidder or a buyer.

Now let me show you the script in oracle plsql using OOP implementing UNION INHERITANCE:

CREATE OR REPLACE TYPE users_obj AS OBJECT  --super object
(
    id       VARCHAR2(10),
    userName VARCHAR2(20),
    email    VARCHAR2(100),
    address  VARCHAR2(35)
) NOT FINAL
/  

CREATE TABLE users OF users_obj--table for users of eBay
(
    id NOT NULL,
    PRIMARY KEY (id)
); 

CREATE OR REPLACE TYPE buyers_obj UNDER users_obj  --inherited object for buyers
(
    bidscreated number
)
/

CREATE TABLE buyers OF buyers_obj  --table of buyers
(
    id NOT NULL,
    PRIMARY KEY (id)
);

CREATE OR REPLACE TYPE bidders_obj UNDER users_obj  --inherited object for bidders
(
    bidsapplied number
)
/

CREATE TABLE bidders OF bidders_obj  --table of bidders
(
    id NOT NULL,
    PRIMARY KEY (id)
);  

There may be users who just creates account and leave them without using them, being static. So, in this case there should be table to store these static users so as to inform them for the news like 'Hey, staic user you won a 1 month vacation to los angeles.'.

Now if the database was designed in classic way think of the problems that one has to face. One of the main advantage of Object Oriented Programming (OOP) is its reuseability. There's a requirement change that you have to face it and solve it.

 In inheritance terms this condition is known as Mutual Exclusion Inheritance.

2. Implementing Mutual Exclusion Inheritance in Oracle 11g.

Now let's move to second design to add table for static users. As we have created table Users I am use this to store information for static users who are neither buyer nor bidder.

Now there will be little change in script for creating Users table.Here's the script:

CREATE OR REPLACE TYPE users_obj AS OBJECT  --super object
(
    id        VARCHAR2(10),
    userName  VARCHAR2(20),
    email     VARCHAR2(100),
    address   VARCHAR2(35),
    user_type VARCHAR2(10)
) NOT FINAL
/  

CREATE TABLE users OF users_obj--table for users of eBay
(
    id NOT NULL,
    user_type CHECK (user_type in ('bidders', 'buyers', 'NULL')), --handling static users
    PRIMARY KEY (id)
); 

CREATE OR REPLACE TYPE buyers_obj UNDER users_obj  --inherited object for buyers
(
    bidscreated number
)
/

CREATE TABLE buyers OF buyers_obj  --table of buyers
(
    id NOT NULL,
    PRIMARY KEY (id)
);

CREATE OR REPLACE TYPE bidders_obj UNDER users_obj  --inherited object for bidders
(
    bidsapplied number
)
/

CREATE TABLE bidders OF bidders_obj  --table of bidders
(
    id NOT NULL,
    PRIMARY KEY (id)
);

Also, if eBay decides to make a new policy that every user must full into any of the role of the bidder, buyer or watchers then this situation is know as Partition Inheritance.

3. Implementing Partition Inheritance in Oracle 11g.

Third design includes the new table for watchers.

CREATE OR REPLACE TYPE users_obj AS OBJECT  --super object
(
    id        VARCHAR2(10),
    userName  VARCHAR2(20),
    email     VARCHAR2(100),
    address   VARCHAR2(35),
    user_type VARCHAR2(10)
) NOT FINAL
/  

CREATE TABLE users OF users_obj--table for users of eBay
(
    id NOT NULL,
    user_type CHECK (user_type in ('bidders', 'buyers', 'watchers')), --handelling watcher users
    PRIMARY KEY (id)
); 

CREATE OR REPLACE TYPE buyers_obj UNDER users_obj  --inherited object for buyers
(
    bidscreated number
)
/

CREATE TABLE buyers OF buyers_obj  --table of buyers
(
    id NOT NULL,
    PRIMARY KEY (id)
);

CREATE OR REPLACE TYPE bidders_obj UNDER users_obj  --inherited object for bidders

(
    bidsapplied number
)
/

CREATE TABLE bidders OF bidders_obj  --table of bidders
(
    id NOT NULL,
    PRIMARY KEY (id)
);

CREATE OR REPLACE TYPE watchers_obj UNDER users_obj  --inherited object for bidders
(
    favItems number
)
/

CREATE TABLE watchers OF watchers_obj  --table of bidders
(
    id NOT NULL,
    PRIMARY KEY (id)
);

Designing database using Object Oriented Features needs a bit high level thinking and needs model thinking. Designers should try to make database model according to the real world model and tables and design should encompass with the requirement change and changes in surrounding.

Well I finished some level of eBay database design , hehe.

 Happy Objecting In Oracle!!

Oracle 11g : Object Oriented Programming In Oracle PLSQL

Get real time news update from your favorite websites.
Don't miss any news about your favorite topic.
Personalize your app.

Check out NTyles.

Get it on....

Before I took the job as the Associate Software Engineer, I was a JAVA and .NET guy.
All the projects that I did before my current job, I did them JAVA or either .NET. In those time object was my play thing. Everything I do in my project I used to think in object way.I know how powerful  object oriented programming is.

Currently I am a oracle plsql developer. And as soon as I get this job I tried to model data in my own way as I used to do in JAVA and .NET. And I was very happy to find out that Oracle too has object oriented features.

Let me show you a quick code demo that uses oracle's object oriented features.
The gist of code is  :

"If I am two numbers then I can be added, subtracted, multiplied and divided."

Now here goes the code:

--this is object type with two number variables. 
--as in java one can define constructor, member functions
--and yes, setters and getters.
--i am not showing all of them coz i am lazy

CREATE OR REPLACE TYPE numbers AS object
(
    a NUMBER,
    b NUMBER
);

Upto this what I did is I created a number Object.
Think something like this :

"If I have a car, I can drive it, and of course I can sell it too." 

So, If I have numbers then I can add them, divided them , subtract them and yes multiply them. Here's the code that does all the above mentioned.

 (See think objectly , everything will look simple and programming would be so easy.)

--This is another object that does all the operation for numbers object.
--This is definition spec.
CREATE OR REPLACE TYPE numbersOp AS object
(
    n numbers, --my numbers object

    --this is how one defines member functions in oracle.
    member FUNCTION plus RETURN NUMBER ,    --add
    member FUNCTION sub RETURN NUMBER,      --subtract 
    member FUNCTION multiply RETURN NUMBER, --multiply
    member FUNCTION divide RETURN NUMBER    --divide
);
/

--This is body spec.
CREATE OR REPLACE TYPE BODY numbersOp as
  member FUNCTION plus RETURN NUMBER AS
   vblsum NUMBER ;
   BEGIN
     vblsum := n.a + n.b;
     RETURN vblsum;
   --EXCEPTION
   --  WHEN Others THEN
   END plus ;

   member FUNCTION sub RETURN NUMBER AS
    vblsub NUMBER;
     BEGIN
       vblsub := n.b - n.a;
       RETURN vblsub;
     --EXCEPTION
     --  WHEN Others THEN
     END sub;

   member FUNCTION multiply RETURN NUMBER AS
    vblmul NUMBER ;
     BEGIN
       vblmul := n.a * n.b;
       RETURN vblmul;
     --EXCEPTION
     --  WHEN Others THEN
     END;

   member FUNCTION divide RETURN NUMBER AS
    vbldiv NUMBER (10, 3);
     BEGIN
       vbldiv := n.a / n.b ;
       RETURN vbldiv;
     --EXCEPTION
     --  WHEN Others THEN
     END;

END ;
 / 

Now everything has been coded for numbers and it operations. Now let see some demo. Here's the demo code:

DECLARE

  a numbers := numbers(25, 60); --my numbers
  b numbersOp := numbersOp(a);  --I should able to operate them
BEGIN

   Dbms_Output.Put_Line(b.plus);
   Dbms_Output.Put_Line(b.sub);
   Dbms_Output.Put_Line(b.multiply);
   Dbms_Output.Put_Line(b.divide);
--EXCEPTION
--  WHEN Others THEN
END;

If you really find this stuff interesting, then my next blog will make u addicted to the object oriented concepts in oracle.
Till then
HAPPY OBJECTING IN ORACLE!!.