Object-PL/SQL

Last updated

Object-PL/SQL ( Object-Procedural Language/Structured Query Language or simply O-PL/SQL) is a methodology of using the Oracle Corporation's procedural extension language for SQL and the Oracle relational database. [1] [2] The additional features from version 7 and other improvements, lead to one of the large-scale environment implementations of the object-oriented database paradigm. [3]

Contents

Although PL/SQL's general syntax formerly used to resemble that of Ada or Pascal, there were many improvements that mainly include the Java embedding code [4] and the object-oriented syntax [5] inside the SQL.

The mixing and embedding of triggers and stored procedures was one of the breakthrough points up to support the use of PL/SQL in a OO paradigm. [6] The inclusion in the SQL syntax of statements such as [class].[object], and the implementation of the object type [7] (like any OO language), completed the minimum requisites to a mapping approach in an extended SQL language without use of specific mapping software. [8]

Autonomy, notoriety and importance of O-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. [9] 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. [10]

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 or sequential databases and persist being valid in new models. Tables, triggers, columns, indexes are examples of database objects, [7] 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.

The 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). [11]

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.

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 [12] and other sources:

A simple example of object-oriented PL/SQL [13] 

createorreplacetypebase_typeasobject(anumber,constructorfunctionbase_typereturnselfasresult,memberfunctionfuncreturnnumber,memberprocedureproc(nnumber))instantiablenotfinal;/

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

createorreplacetypebodybase_typeasconstructorfunctionbase_typereturnselfasresultisbegina:=0;return;endbase_type;memberfunctionfuncreturnnumberisbeginreturna;endfunc;memberprocedureproc(nnumber)asbegina:=n;endproc;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.

createorreplacetypederiv_typeunderbase_type(mnumber,overridingmemberfunctionfuncreturnnumber);/

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

createorreplacetypebodyderiv_typeasoverridingmemberfunctionfuncreturnnumberisbeginreturnm*a;end;end;/

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

declareb1base_type:=base_type();b2base_type:=base_type(4);d1deriv_type:=deriv_type(5,6);d2deriv_type:=deriv_type(5,6);begindbms_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:

createtabletable_base(bbase_type);declarebasebase_type:=base_type();derivderiv_type:=deriv_type(8,9);begininsertintotable_basevalues(base);insertintotable_basevalues(deriv);end;/selectt.b.func()fromtable_baset;

Results:

0 72
selectavg(t.b.func())fromtable_baset;

Result:

36

See also

Bibliography

Related Research Articles

A relational database is a digital database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relational database systems have an option of using the SQL for querying and maintaining the database.

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables. SQL offers two main advantages over older read–write APIs such as ISAM or VSAM. Firstly, it introduced the concept of accessing many records with one single command. Secondly, it eliminates the need to specify how to reach a record, e.g. with or without an index.

Object–relational mapping in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language. There are both free and commercial packages available that perform object–relational mapping, although some programmers opt to construct their own ORM tools.

Object–relational database Database management system

An object–relational database (ORD), or object–relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, just as with pure relational systems, it supports extension of the data model with custom data types and methods.

A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary.

The SQL SELECT statement returns a result set of records, from one or more tables.

Late binding or dynamic linkage—though not an identical process to dynamically linking imported code libraries—is a computer programming mechanism in which the method being called upon an object, or the function being called with arguments, is looked up by name at runtime. In other words, a name is associated with a particular operation or object at runtime, rather than during compilation. The name dynamic binding is sometimes used, but is more commonly used to refer to dynamic scope.

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries. Triggers can also be used to log historical data, for example to keep track of employees' previous salaries.

An XML database is a data persistence software system that allows data to be specified, and sometimes stored, in XML format. This data can be queried, transformed, exported and returned to a calling system. XML databases are a flavor of document-oriented databases which are in turn a category of NoSQL database.

A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. UDFs are usually written for the requirement of its creator.

The object–relational impedance mismatch is a set of conceptual and technical difficulties that are often encountered when a relational database management system (RDBMS) is being served by an application program written in an object-oriented programming language or style, particularly because objects or class definitions must be mapped to database tables defined by a relational schema.

In computing, a materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.

Database administration is the function of managing and maintaining database management systems (DBMS) software. Mainstream DBMS software such as Oracle, IBM DB2 and Microsoft SQL Server need ongoing management. As such, corporations that use DBMS software often hire specialized information technology personnel called database administrators or DBAs.

Oracle Data Mining (ODM) is an option of Oracle Database Enterprise Edition. It contains several data mining and data analysis algorithms for classification, prediction, regression, associations, feature selection, anomaly detection, feature extraction, and specialized analytics. It provides means for the creation, management and operational deployment of data mining models inside the database environment.

SQL PL stands for Structured Query Language Procedural Language and was developed by IBM as a set of commands that extend the use of SQL in the IBM Db2 database system. It provides procedural programmability in addition to the querying commands of SQL. It is a subset of the SQL Persistent Stored Modules (SQL/PSM) language standard.

Apache Empire-db is a Java library that provides a high level object-oriented API for accessing relational database management systems (RDBMS) through JDBC. Apache Empire-db is open source and provided under the Apache License 2.0 from the Apache Software Foundation.

jOOQ Object Oriented Querying, commonly known as jOOQ, is a light database-mapping software library in Java that implements the active record pattern. Its purpose is to be both relational and object oriented by providing a domain-specific language to construct queries from classes generated from a database schema.

In database management systems (DBMS), a prepared statement or parameterized statement is a feature used to pre-compile SQL code, separating it from data. Benefits of prepared statements are:

PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database, Times Ten in-memory database, and IBM Db2. Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.

The following is provided as an overview of and topical guide to databases:

References

  1. ↑ Lassan, Alan R.; Due, Jacob Steen (13 June 2000). "Experiences with Object Oriented Development in PL/SQL" (PDF). The danish National Center for IT Research. Archived from the original (PDF) on 24 December 2010. Retrieved 15 April 2012.
  2. ↑ Centre For; Allan R. Lassen; Jacob Steen Due (2000). "Experiences with Object Oriented Development in PL/SQL". CiteSeerX   10.1.1.38.5122 .{{cite journal}}: Cite journal requires |journal= (help)
  3. ↑ Cunningham, Lewis. "PL/SQL Features by Release". Burleson Consulting. Retrieved 15 April 2012.
  4. ↑ "When Should you use Java Stored Procedures with an Oracle Database, what are the Drawbacks?". Stack Overflow. Retrieved 15 April 2012.
  5. ↑ "Oracle's Object-Oriented Features". etutorial.org. Retrieved 16 April 2012.
  6. ↑ Benett, 2002:144
  7. 1 2 Shubho, Al-Farooque (8 November 2009). "Optimize Database Files and Apply Partitioning". The Code Project. Retrieved 19 April 2012.
  8. ↑ Bales, 2007:107-209
  9. ↑ "Use Object PL/SQL". java2s.com. Retrieved 19 April 2012.
  10. ↑ Feuerstein, 2009
  11. ↑ "DB2 10: Run Oracle applications on DB2 10 for Linux, UNIX, and Windows". IBM. Retrieved 20 April 2012.
  12. ↑ "Oracle Documentatio". Oracle. Retrieved 19 April 2012.
  13. ↑ "Object Oriented Oracle, example 1". René Nyffenegger's collection of things on the web. Retrieved 19 April 2012.

External sources


This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.