For an introduction to the table types and list visit : What are Index Organized Tables?
The era of Relational Database Management System (RDBMS) is now left far behind and since last around a decade or so we have been treading the era of Object Relational Database Management System (ORDBMS) when some of the commercial database systems (Informix) and Oracle in particular (since version 8) provided the SQL to implement "Object Relational Model" in the databases running out of their systems. Before we delve into "Object Relational Extension" again with reference to Oracle as our model DBMS, we need to have a brief primer in what "Object Oriented" is all about.
Object Oriented Concepts -
"Object Oriented" programming approach was introduced in late 80s but if going on the spiritual lines one should say the creator of this universe had this same approach in designing this universe; this statement I am making on the same lines as "this world is worth living because it's non-procedural" I made in an earlier post. Now I must also say "the world is worth living because it runs the object oriented way" (of course apart from being non-procedural).
Technically, object oriented approach is defined as "encapsulation" of attributes (variables) and methods (programs) which manipulate those variables to produce certain output depending upon which of the methods have been revoked. An example is the need of the order for this definition to make sense to the beginners or juniors.
What do you do when you feel like having a "brick oven pizza" on your lunch? You call a nearby pizza hut service, right? and then you provide the service with the toppings (technically each topping is a variable and you provide a value for it) of your choice and then tell the service the type of pizza you want, in this case "brick oven pizza". Now you know each of those classified pizzas are made in certain way or process (read Method), so technically you invoke the method. And you get the pizza delivered (the output). And so call "pizza hut" an object.
Actually we may use and extend this same example to understand various properties of an "object" like inheritance, polymorphism, overloading etc. but not for now, as we have got enough of dose of object oriented concept for our job at hand "creating an Object Table".
One important point before we leave this item is that, in Oracle, we actually create a "type" rather than a "class" (a terminology used by other object oriented languages). In a way class and type have no difference conceptually, as both are a "template" (read definition) of an object. The Oracle's "type" terminology is picked from the idea of "datatype" as technically declaring a variable of a datatype and assigning a value to it, if studied carefully, will reveal that it is very much an object.
Defining an Oracle Object Type -
Oracle programmers use PL/SQL back end language to define an "object type", which is written in two parts - header and body. Simply put the header contains all the "declarations" and the body, "code" for all the methods declared in the header. The header has to be successfully compiled before you attempt to write the body. Here in this example we are creating an object type "empitax" containing the salary details of employee along with the methods to calculate the CTC, taxable income and tax amount. -
-- Type Header
create or replace type empitax as object (
empno number(4),
ename varchar2(15),
job varchar2(15),
salary number(4),
darate number(3),
itxrate number(3),
xemplim number(8),
member function ctc return number,
member function taxincome return number,
member function taxamt return number);
/
-- Type Body
create or replace type body empitax is
member function ctc return number is
totearning number;
begin
totearning := self.salary * 12; -- annual salary
totearning := totearning * (toearning * self.darate / 100);-- sal + allowance
return totearning;
end;
member function taxincome return number is
tincome number;
begin
tincome := self.ctc - xemplim; -- taxable income salary minus exemption limit
return tincome;
end;
member function taxamt return number is
tamt number;
begin
tamt := self.taxincome * self.itxrate / 100; -- tax amount x taxrate percent
return tamt;
end;
end;
/
Some explanations - When an object type has to refer to its own element then it must be qualified with "self" (self.salary, self.darate, self.ctc, etc). The calculation formulas are arbitrary and not standard. The "or replace" clause is optional; used to overwrite the old definition with new one.
Creating Object Table -
Once you have created the object type now you may create the object table simply by using following form of "create table" statement -
create table emptaxtab of empitax;
This statement will create a table named emptaxtab with the attributes of empitax to become the columns of the table. The table is called "Object Table" and each row stored in the table is called as "row object". All the normal SQL statements may be used to handle the data in this table.
Inserting rows -
insert into emptaxtab values (101, 'peter', 'manager', 3000, 120, 10, 24000);
insert into emptaxtab values (101, 'diana', 'analyst', 2500, 110, 8, 20000);
Observe that the insert statement is same as would be used against a normal heap organized table. Other statements, select, update or delete would also be same. But then what is so special about it? Well, remember? There are some methods integrated in the object from which the table has been created. So we can write a statement -
select ename, x.taxamt() as taxamount from emptaxtab x;
and thus you could list the names of the employees along with their tax amount (or even CTC or taxable income by calling appropriate methods) so easily. The methods are integrated and remain part of the table. (you may use appropriate predicate, "where clause" to list only the names of choice also in this select statement).
Object Oriented is a great concept; so next time when order a pizza of your choice, thank the god for creating this world the object oriented way, and enjoy it. Click Here to continue reading on "Nested Tables" another implementation of Object Oriented concepts.
Object Oriented Concepts -
"Object Oriented" programming approach was introduced in late 80s but if going on the spiritual lines one should say the creator of this universe had this same approach in designing this universe; this statement I am making on the same lines as "this world is worth living because it's non-procedural" I made in an earlier post. Now I must also say "the world is worth living because it runs the object oriented way" (of course apart from being non-procedural).
Technically, object oriented approach is defined as "encapsulation" of attributes (variables) and methods (programs) which manipulate those variables to produce certain output depending upon which of the methods have been revoked. An example is the need of the order for this definition to make sense to the beginners or juniors.
What do you do when you feel like having a "brick oven pizza" on your lunch? You call a nearby pizza hut service, right? and then you provide the service with the toppings (technically each topping is a variable and you provide a value for it) of your choice and then tell the service the type of pizza you want, in this case "brick oven pizza". Now you know each of those classified pizzas are made in certain way or process (read Method), so technically you invoke the method. And you get the pizza delivered (the output). And so call "pizza hut" an object.
Actually we may use and extend this same example to understand various properties of an "object" like inheritance, polymorphism, overloading etc. but not for now, as we have got enough of dose of object oriented concept for our job at hand "creating an Object Table".
One important point before we leave this item is that, in Oracle, we actually create a "type" rather than a "class" (a terminology used by other object oriented languages). In a way class and type have no difference conceptually, as both are a "template" (read definition) of an object. The Oracle's "type" terminology is picked from the idea of "datatype" as technically declaring a variable of a datatype and assigning a value to it, if studied carefully, will reveal that it is very much an object.
Defining an Oracle Object Type -
Oracle programmers use PL/SQL back end language to define an "object type", which is written in two parts - header and body. Simply put the header contains all the "declarations" and the body, "code" for all the methods declared in the header. The header has to be successfully compiled before you attempt to write the body. Here in this example we are creating an object type "empitax" containing the salary details of employee along with the methods to calculate the CTC, taxable income and tax amount. -
-- Type Header
create or replace type empitax as object (
empno number(4),
ename varchar2(15),
job varchar2(15),
salary number(4),
darate number(3),
itxrate number(3),
xemplim number(8),
member function ctc return number,
member function taxincome return number,
member function taxamt return number);
/
-- Type Body
create or replace type body empitax is
member function ctc return number is
totearning number;
begin
totearning := self.salary * 12; -- annual salary
totearning := totearning * (toearning * self.darate / 100);-- sal + allowance
return totearning;
end;
member function taxincome return number is
tincome number;
begin
tincome := self.ctc - xemplim; -- taxable income salary minus exemption limit
return tincome;
end;
member function taxamt return number is
tamt number;
begin
tamt := self.taxincome * self.itxrate / 100; -- tax amount x taxrate percent
return tamt;
end;
end;
/
Some explanations - When an object type has to refer to its own element then it must be qualified with "self" (self.salary, self.darate, self.ctc, etc). The calculation formulas are arbitrary and not standard. The "or replace" clause is optional; used to overwrite the old definition with new one.
Creating Object Table -
Once you have created the object type now you may create the object table simply by using following form of "create table" statement -
create table emptaxtab of empitax;
This statement will create a table named emptaxtab with the attributes of empitax to become the columns of the table. The table is called "Object Table" and each row stored in the table is called as "row object". All the normal SQL statements may be used to handle the data in this table.
Inserting rows -
insert into emptaxtab values (101, 'peter', 'manager', 3000, 120, 10, 24000);
insert into emptaxtab values (101, 'diana', 'analyst', 2500, 110, 8, 20000);
Observe that the insert statement is same as would be used against a normal heap organized table. Other statements, select, update or delete would also be same. But then what is so special about it? Well, remember? There are some methods integrated in the object from which the table has been created. So we can write a statement -
select ename, x.taxamt() as taxamount from emptaxtab x;
and thus you could list the names of the employees along with their tax amount (or even CTC or taxable income by calling appropriate methods) so easily. The methods are integrated and remain part of the table. (you may use appropriate predicate, "where clause" to list only the names of choice also in this select statement).
Object Oriented is a great concept; so next time when order a pizza of your choice, thank the god for creating this world the object oriented way, and enjoy it. Click Here to continue reading on "Nested Tables" another implementation of Object Oriented concepts.
This is a good analogy, using familiar terms to explain concepts using the current vocabulary or insider jargon. I like pizza too. Thanks for doing your explanation this way.
ReplyDelete