Object-PL/SQL: Difference between revisions

Revision as of 21:38, 20 April 2012

Latest revision as of 13:05, 19 July 2025

(29 intermediate revisions by 19 users not shown)

Line 1:

'''Object-PL/SQL''' ('''[[Object-oriented programming|Object]]-Procedural Language/[[Structured Query Language]]''' or simply '''O-PL/SQL''') is a methodology of using the [[Oracle Corporation]]'s [[Procedural programming|procedural]] extension [[programming language|language]] for [[SQL]] and the [[Oracle Database|Oracle relational database]].<ref name="CITDK">{{cite web|url=http://www.cit.dk/cot/reports/reports/Case4/18/cot-4-18.pdf|title=Experiences with Object Oriented Development in PL/SQL|author=Lassan, Alan R., |last2=Due, Jacob Steen|date=13 June 2000|publisher=The danish NationlNational Center for IT Research|accessdateurl-status=dead|archive-url=https://web.archive.org/web/20101224020624/http://www.cit.dk/cot/reports/reports/Case4/18/cot-4-18.pdf|archive-date=24 December 2010|access-date=15 April 2012}}</ref> <ref>{{Cite journal|last1=Centre For|last2=Allan R. Lassen|last3=Jacob Steen Due|date=2000|title=Experiences with Object Oriented Development in PL/SQL|citeseerx=10.1.1.38.5122}}</ref> The additional features from version 7 and other improvements, lead to one of the large-scale environment implementations of the [[object-oriented database]] paradigm.<ref name="DBAORA">{{cite web|url=http://www.dba-oracle.com/t_edb_pl_sql_features_release.htm|title=PL/SQL Features by Release|author=Cunningham, Lewis|publisher=Burleson Consulting|accessdateaccess-date=15 April 2012}}</ref>

Although PL/SQL's general [[Syntax (programming languages)|syntax]] formerly used to resemble that of [[Ada (programming language)|Ada]] or [[Pascal (programming language)|Pascal]], there were many improvements that mainly include the ''Java embedding code''<ref name="STKOVER">{{cite web|url=httphttps://stackoverflow.com/questions/70072/when-should-you-use-java-stored-procedures-with-an-oracle-database-what-are|title=When Should you use Java Stored Procedures with an Oracle Database, what are the Drawbacks?|publisher=Stack Overflow|accessdateaccess-date=15 April 2012}}</ref> and the ''object-oriented syntax''<ref name="ETUT">{{cite web|url=http://etutorials.org/SQL/Oracle+PL+SQL+Language+Pocket+Reference/Chapter+1.+Oracle+PLSQL+Language+Pocket+Reference/1.16+Oracles+Object-Oriented+Features/|title=Oracle's Object-Oriented Features|publisher=etutorial.org|accessdateaccess-date=16 April 2012}}</ref> inside the SQL.

The mixing and embedding of [[Database trigger|triggers]] and [[stored procedure]]s was one of the breakthrough points up to support the use of [[PL/SQL]] in a ''OO'' paradigm.<ref name="SAMS">Benett, 2002:144</ref> The inclusion in the SQL syntax of statements such as <nowiki>{{mono|[class].[object]</nowiki>}}, and the implementation of the object ''type''<ref name="TOP10"/> (like any OO language), completed the minimum requisites to a mapping approach in an extended SQL language without use of [[Persistence framework|specific mapping software]].<ref name="BALES">Bales, 2007:107-209</ref>

== Autonomy, notoriety and importance of O-PL/SQL ==

The O-PSL/SQL isn't identifiedsimply withthe thisuse ora those versionsversion of thea programming language PL/SQL, but it's hasidentified inas its''how charactersto techniquesuse it'', and it defines the autonomy of usagethe theme.<ref name="JAVA2">{{cite web|url=http://www.java2s.com/Tutorial/Oracle/0620__Object-Oriented/UseObjectPLSQL.htm|title=Use Object PL/SQL|publisher=java2s.com|accessdateaccess-date=19 April 2012}}</ref> 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. soSo big is thethat 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.<ref name="STEVE">Feuerstein, 2009</ref>

== A confusing of ''objects'' ==

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 model|relational]] or sequential databases and persist being valid in new models. ''Tables'', ''triggers'', ''columns'', ''indexes'' are examples of database objects,<ref name="TOP10">{{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|accessdateaccess-date=19 April 2012}}</ref> which are present in O-PL/SQL, but with the same meaning of the notion of [[Java (programming language)|Java]] objects, specifically an element of a set that has its existence beginning from an instantiation of a class.

== The PL/SQL ==

{{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|TimesTen in-memory database]] (since version 11.2.1), and [[IBM DB2Db2]] (since version 9.7).<ref name="IBM">{{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|accessdateaccess-date=20 April 2012}}</ref>

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

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

th

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

== Examples of uses of syntax of O-PL/SQL ==

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

=== Example 1 ===

A simple example of object-oriented PL/SQL<ref>{{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|accessdate=19 April 2012}}</ref><source lang="SQL">

A simple example of object-oriented PL/SQL<ref>{{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|accessdateaccess-date=19 April 2012}}</ref><source lang="SQL">

<syntaxhighlight lang="plpgsql">

create or replace type base_type as object (

a number,

Line 36 ⟶ 33:

member procedure proc (n number)

) instantiable not final;

/</sourcesyntaxhighlight>

Now, the type's implementation is created. The implementation defines how the type's functions, procedures and how explicit constructors behave:<sourcesyntaxhighlight lang="SQLplpgsql">

create or replace type body base_type as

constructor function base_type return self as result is

Line 55 ⟶ 52:

end proc;

end;

/</sourcesyntaxhighlight>

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.<sourcesyntaxhighlight lang="SQLplpgsql">

create or replace type deriv_type under base_type (

m number,

overriding member function func return number

);

/</sourcesyntaxhighlight>

As is the case with base types, the overridden methods in the derived type must be implemented:<source lang="SQL">

<syntaxhighlight lang="plpgsql">

create or replace type body deriv_type as

overriding member function func return number is

Line 69 ⟶ 67:

end;

end;

/</sourcesyntaxhighlight>

The created types can be instantiadedinstantiated and methods can be called:
<sourcesyntaxhighlight lang="SQLplpgsql">

declare

b1 base_type :=base_type();

Line 84 ⟶ 83:

dbms_output.put_line(d2.func);

end;

/</sourcesyntaxhighlight>

Results

* 0

* 4

* 24

* 30

The created types have become real types and can be used in tables:<source lang="SQL">

<syntaxhighlight lang="plpgsql">

create table table_base (

b base_type

Line 103:

/

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

</syntaxhighlight>

T.B.FUNC()</source>

Results:

* 0

* 72

<sourcesyntaxhighlight lang="SQLplpgsql">

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

</syntaxhighlight>

AVG(T.B.FUNC())</source>

Result:

* 36

=== Example 2 ===

Another example of stored procedure in Java embedded in Oracle Documentation<ref name="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|accessdate=20 April 2012}}</ref>

== Bibliography ==

* {{cite book|url=http://books.google.com.br/books?id=1RwFA-vlwX8C&pg=PA144&lpg=PA144&dq=mixing+java+in+pl/sql+procedures+and+triggers&source=bl&ots=Vw7l1H7TUe&sig=bykIRLXpPkVPYucWr3fJhpYjzfY&hl=pt-BR&sa=X&ei=L2WMT5ukDYio8gST1JHFCQ&ved=0CCMQ6AEwAA#v=onepage&q=mixing%20java%20in%20pl%2Fsql%20procedures%20and%20triggers&f=false|last=Bennett|first=Mathew|title=Programming Oracle Developer|edition=1|place=Indianapolis|publisher=Sams|year=2002|pages=348|isbn=0672321106}}

* {{cite book|url=http://books.google.com.br/books?id=FqUKI3MSuHYC&printsec=frontcover&hl=pt-BR&source=gbs_ge_summary_r&cad=0#v=onepage&q&f=false|last=Bales|first=Donals|title=PL/SQL from Novice to Professional|edition=1|place=New York|publisher=Apress|year=2007|isbn=10590598822|pages=469}}

* {{cite book|url=http://my.safaribooksonline.com/book/databases/oracle-pl-sql/9780596805401|title=Oracle PL/SQL Programming|last=Feuerstein|first=Steven|last2=Pribyl|first2=Bill|place=Sebastopol|publisher=O'Reilly Media, Inc.|year=2009|pages=978-0-596-51446-4|isbn=9780596514464|chapter=26}}

*{{cite book|url=http://218.92.71.5:1085/trebook/book/book36/2009986047703.pdf|last=Rahayu|first=Wenny|last2=taniar|first2=David|last3=Pardede|first3=Eric|title=Object-Oriented Oracle|place=Hershey|publisher=IRM Press|year=2006|pages=345|isbn=1591406080}}

==See also==

* [[Relational database management system]]

Line 127 ⟶ 117:

* [[Transact-SQL]]

* [[Database management system]]

== Bibliography ==

* {{cite book|url=httphttps://books.google.com.br/books?id=1RwFA-vlwX8C&pg=PA144&lpg=PA144&dqq=mixing+java+in+pl/sql%2Fsql+procedures+and+triggers&sourcepg=bl&ots=Vw7l1H7TUe&sig=bykIRLXpPkVPYucWr3fJhpYjzfY&hl=pt-BR&sa=X&ei=L2WMT5ukDYio8gST1JHFCQ&ved=0CCMQ6AEwAA#v=onepage&q=mixing%20java%20in%20pl%2Fsql%20procedures%20and%20triggers&f=falsePA144|last=Bennett|first=Mathew|title=Programming Oracle Developer|edition=1|place=Indianapolis|publisher=Sams|year=2002|pages=348|isbn=0672321106}}

* {{cite book|url=httphttps://books.google.com.br/books?id=FqUKI3MSuHYC&printsec=frontcover&hl=pt-BR&source=gbs_ge_summary_r&cad=0#v=onepage&q&f=false|last=Bales|first=Donals|title=PL/SQL from Novice to Professional|edition=1|place=New York|publisher=Apress|year=2007|isbn=10590598822978-1590598825|pages=469}}

* {{cite book|chapter-url=http://my.safaribooksonline.com/book/databases/oracle-pl-sql/9780596805401|title=Oracle PL/SQL Programming|lastlast1=Feuerstein|firstfirst1=Steven|last2=Pribyl|first2=Bill|place=Sebastopol|publisher=O'Reilly Media, Inc.|year=2009|pagesisbn=9789780596514464|chapter=26|archive-0date=2013-59612-5144625|access-date=2012-04-420|isbnarchive-url=9780596514464https://web.archive.org/web/20131225213946/http://my.safaribooksonline.com/book/databases/oracle-pl-sql/9780596805401|chapterurl-status=26dead}}

*{{cite book|url=http://218.92.71.5:1085/trebook/book/book36/2009986047703.pdf|lastlast1=Rahayu|firstfirst1=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}}

==References==

Line 132 ⟶ 127:

== External sources ==

* [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<ref: name="{{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|accessdateaccess-date=20 April 2012}}</ref>

<!-- NOTE

This is not the place for links to each and every website that may be helpful