PL/SQL stands for "Procedural Language extensions to the Structured Query Language." SQL is the language for both querying and updating relational databases. Oracle Corporation introduced PL/SQL to overcome some limitations in SQL and to provide a more complete programming solution for those who sought to build mission-critical applications to run against the Oracle database. This module introduces PL/SQL, its origins, and its various versions. It offers a quick summary of PL/SQL in the latest Oracle releases, Oracle Database
13c Release 1 and Release 2. Finally, it provides a guide to additional resources for PL/SQL developers and some words of advice.
Oracle's PL/SQL language has several defining characteristics:
- It is a highly structured, readable, and accessible language.
If you are new to programming, PL/SQL is a great place to start. You will find that it is an easy language to learn and is rich with keywords and structure that clearly express the intent of your code. If you are experienced in other programming languages, you will very easily adapt to the new syntax.
- It is a standard and portable language for Oracle development:
If you write a PL/SQL procedure or function to execute from within the Oracle database sitting on your laptop, you can move that same procedure to a database on your corporate network and execute it there without any changes (assuming compatibility of Oracle versions, of course!). "Write once, run everywhere" was the mantra of PL/SQL long before Java appeared. For PL/SQL, everywhere means everywhere there is an Oracle database.
- It is an embedded language: PL/SQL was not designed to be used as a standalone language, but instead to be invoked from within a host environment. So, for example, you can run PL/SQL programs from within the database (through, say, the SQL*Plus interface).
- Alternatively, you can define and execute PL/SQL programs from within an Oracle Developer form or report (this approach is called client-side PL/SQL). You cannot, however, create a PL/SQL executable that runs all by itself.
- It is a high-performance, highly integrated database language: These days, you have a number of choices when it comes to writing software to run against the Oracle database. You can use Java and JDBC; you can use Visual Basic and ODBC; you can go with PHP or C++. You will find, however, that it is easier to write highly efficient code to access the Oracle database in PL/SQL than it is in any other language.
In particular, Oracle offers certain PL/SQL specific enhancements such as the FORALL statement that can improve database performance by an order of magnitude or more.
Beginning Oracle PL/SQL
Origins of PL/SQL
Oracle Corporation has a history of leading the software industry in providing declarative, non-procedural approaches to designing both databases and applications. The Oracle Server technology is among the most advanced, powerful, and stable relational databases in the world. Its application development tools, such as Oracle Forms, offer high levels of productivity by relying heavily on a graphic-user-interface approach in which extensive default capabilities allow developers to avoid heavy customized programming efforts.
Early Years of PL/SQL
In Oracle's early years, the declarative approach of SQL, combined with its groundbreaking relational technology,
was enough to satisfy developers. But as the industry matured, expectations rose, and requirements became more stringent. Developers needed to build complicated formulas, exceptions, and rules into their forms and database scripts. In 1988, Oracle Corporation released Oracle Version 6, a major advance in its relational database technology. A key component of that version was the so-called procedural option or PL/SQL.
At roughly the same time, Oracle released its long-awaited upgrade to SQL*Forms Version 2.3 (the original name for the product now known as Oracle Forms or Forms Developer). SQL*Forms V3.0 incorporated the PL/SQL engine for the first time on the tools side, allowing developers to code their procedural logic in a natural, straightforward manner.
First Release
This first release of PL/SQL was very limited in its capabilities. On the server side, you could use PL/SQL only to build batch-processing scripts of procedural and SQL statements. You could not construct a modular application or store business rules in the server.
On the client side, SQL*Forms V3.0 did allow you to create procedures and functions, although support for functions was not documented, and was therefore not used by many developers for years. In addition, this release of PL/SQL did not implement array support and could not interact with the operating system (for input or output). It was not quite a full-fledged programming language.
But for all its limitations, PL/SQL was well received in the developer community. The hunger for the ability to code a simple IF statement inside SQL*Forms was strong. The need to perform multi-SQL statement batch processing was in high demand.
What few developers realized at the time was that the original motivation and driving
vision behind PL/SQL extended beyond the desire for programmatic control within products like SQL*Forms.
Very early in the life cycle of Oracle's database and tools, Oracle Corporation had recognized two key weaknesses in their architecture: lack of portability and problems with execution authority.