Object-PL/SQL

The O-PSL/SQL isn't simply the use a version of a programming language but it's identified as how to use it, and it defines the autonomy of the theme.{{cite web|url=http://www.java2s.com/Tutorial/Oracle/0620__Object-Oriented/UseObjectPLSQL.htm|title=Use Object PL/SQL|publisher=java2s.com|access-date=19 April 2012}} Each version of PL/SQL, starting from 7, brings so many innovations that it's impossible to treat such usages as sub-themes of PL/SQL. So big is that revolution that it establishes a real borderline between the language, that can be used as formerly, and the OO approach inside itself. It's just this approach that makes the theme important and the large-scale using has brought its notoriety.Feuerstein, 2009

There can be confusion of the notions of object of DBMS and of class object. This is very important as we live with both significances in one language. It's necessary to identify when the documentation refers to an object as one of the two definitions.

Database objects are concepts that refer to relational or sequential databases and persist being valid in new models. Tables, triggers, columns, indexes are examples of database objects,{{cite web|url=http://www.codeproject.com/Articles/43629/Top-10-steps-to-optimize-data-access-in-SQL-Server|title=Optimize Database Files and Apply Partitioning|author=Shubho, Al-Farooque|date=8 November 2009|publisher=The Code Project|access-date=19 April 2012}} which are present in O-PL/SQL, but with the same meaning of the notion of Java objects, specifically an element of a set that has its existence beginning from an instantiation of a class.

{{Main article|PL/SQL}}

PL/SQL is the extended SQL language used by Oracle Database.

PL/SQL is available in Oracle Database (since version 7), TimesTen in-memory database (since version 11.2.1), and IBM Db2 (since version 9.7).{{cite web|url=http://www.ibm.com/developerworks/data/library/techarticle/dm-0907oracleappsondb2/index.html|title=DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows|publisher=IBM|access-date=20 April 2012}}

O-PL/SQL allows the definition of classes and instantiating these as objects,

thus creating user-defined datatypes as writing constructors, beyond using Java in stored procedures and triggers.

Here is a small set of examples of O-PL/SQL syntax, extracted from the official documentation{{cite web|url=http://docs.oracle.com/|title=Oracle Documentatio|publisher=Oracle|access-date=19 April 2012}} and other sources:

A simple example of object-oriented PL/SQL{{cite web|url=http://www.adp-gmbh.ch/ora/plsql/oo/example_1.html|title=Object Oriented Oracle, example 1|publisher=René Nyffenegger's collection of things on the web|access-date=19 April 2012}}

create or replace type base_type as object (

a number,

constructor function base_type return self as result,

member function func return number,

member procedure proc (n number)

) instantiable not final;

/

Now, the type's implementation is created. The implementation defines how the type's functions, procedures and how explicit constructors behave:

create or replace type body base_type as

constructor function base_type return self as result is

begin

a:=0;

return;

end base_type;

member function func return number is

begin

return a;

end func;

member procedure proc (n number) as

begin

a:=n;

end proc;

end;

/

We're ready to derive from base_type. The keyword for deriving is under. The derived type defines a new attribute (named: m) and overrides func.

create or replace type deriv_type under base_type (

m number,

overriding member function func return number

);

/

As is the case with base types, the overridden methods in the derived type must be implemented:

create or replace type body deriv_type as

overriding member function func return number is

begin

return m*a;

end;

end;

/

The created types can be instantiated and methods can be called:

declare

b1 base_type :=base_type();

b2 base_type :=base_type(4);

d1 deriv_type:=deriv_type(5,6);

d2 deriv_type:=deriv_type(5,6);

begin

dbms_output.put_line(b1.func);

dbms_output.put_line(b2.func);

d1.proc(4);

dbms_output.put_line(d1.func);

dbms_output.put_line(d2.func);

end;

/

Results

0

4

24

30

The created types have become real types and can be used in tables:

create table table_base (

b base_type

);

declare

base base_type := base_type();

deriv deriv_type:= deriv_type(8,9);

begin

insert into table_base values(base);

insert into table_base values(deriv);

end;

/

select t.b.func() from table_base t;

Results:

0

72

select avg(t.b.func()) from table_base t;

Result:

36

• Relational database management system
• Component-oriented database
• Transact-SQL
• Database management system

• {{cite book|url=https://books.google.com/books?id=1RwFA-vlwX8C&q=mixing+java+in+pl%2Fsql+procedures+and+triggers&pg=PA144|last=Bennett|first=Mathew|title=Programming Oracle Developer|edition=1|place=Indianapolis|publisher=Sams|year=2002|pages=348|isbn=0672321106}}
• {{cite book|url=https://books.google.com/books?id=FqUKI3MSuHYC|last=Bales|first=Donals|title=PL/SQL from Novice to Professional|edition=1|place=New York|publisher=Apress|year=2007|isbn=978-1590598825|pages=469}}
• {{cite book|chapter-url=http://my.safaribooksonline.com/book/databases/oracle-pl-sql/9780596805401|title=Oracle PL/SQL Programming|last1=Feuerstein|first1=Steven|last2=Pribyl|first2=Bill|place=Sebastopol|publisher=O'Reilly Media, Inc.|year=2009|isbn=9780596514464|chapter=26}}
• {{cite book|url=http://218.92.71.5:1085/trebook/book/book36/2009986047703.pdf|last1=Rahayu|first1=Wenny|last2=taniar|first2=David|last3=Pardede|first3=Eric|title=Object-Oriented Oracle|place=Hershey|publisher=IRM Press|year=2006|pages=345|isbn=1591406080|access-date=2012-04-20|archive-url=https://web.archive.org/web/20160303223746/http://218.92.71.5:1085/trebook/book/book36/2009986047703.pdf|archive-date=2016-03-03|url-status=dead}}

{{reflist}}

• [https://web.archive.org/web/20120917012254/http://elearning.algonquincollege.com/coursemat/dat2355d/oracle-oo.html Examples of O-Pl/SQL]
• Another example of stored procedure in Java embedded in Oracle Documentation: {{anchor|ORA2}}{{cite web|url=http://docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm#CACFGDCI|title=Calling Java Methods in Oracle Database|publisher=Oracle|access-date=20 April 2012}}

{{DEFAULTSORT:Object-PlSql}}

Category:Oracle software

Category:SQL

Category:Object-oriented programming