1.2. The Origins of PL/SQL
Oracle 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 "paint-your-screen" approach in which extensive default capabilities allow developers to avoid heavy customized programming efforts.
1.2.1. The 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 get "under the skin" of the products. They 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.
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 a far cry from a full-fledged programming language.
But for all its limitations, PL/SQL was warmly, even enthusiastically, 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 overwhelming.
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.
1.2.2. Improved Application Portability
The concern about portability might seem odd to those of us familiar with Oracle Corporation’s marketing and technical strategies. One of the hallmarks of the Oracle solution from the early 1980s was its portability. At the time that PL/SQL came along, the C-based RDBMS ran on many different operating systems and hardware platforms. SQL*Plus and SQL*Forms adapted easily to a variety of terminal configurations. Yet for all that coverage, there were still many applications that needed the more sophisticated and granular control offered by such host languages as COBOL, C, and FORTRAN. As soon as a developer stepped outside the port-neutral Oracle tools, the resulting application would no longer be portable.
The PL/SQL language was (and is) intended to widen the range of application requirements that can be handled entirely in operating system-independent programming tools. Today, Java and other programming languages offer similar portability. Yet PL/SQL stands out as an early pioneer in this field and, of course, it continues to allow developers to write highly portable application code.
1.2.3. Improved Execution Authority and Transaction Integrity
An even more fundamental issue than portability was execution authority. The RDBMS and the SQL language let you tightly control access to, and changes in, any particular database table. For example, with the GRANT command, you can make sure that only certain roles and users can perform an UPDATE on a given table. On the other hand, this GRANT statement can’t ensure that the full set of UPDATEs performed by a user or application is done correctly. In other words, the database can’t guarantee the integrity of a transaction that spans more than one table, as is common with most business transactions.
The PL/SQL language provided tight control and management over logical transactions. One way PL/SQL does this is with the implementation of execution authority. Instead of granting to a role or user the authority to update a table, you grant privleges only to execute a procedure, which controls and provides access to the underlying data structures. The procedure is owned by a separate Oracle RDBMS account (the "definer" of the program), which, in turn, is granted the actual update privileges on those tables needed to perform the transaction. The procedure therefore becomes the "gatekeeper" for the transaction. The only way that a program (whether it’s an Oracle Forms application or a Pro*C executable) can execute the transfer is through the procedure. In this way, the overall application transaction integrity is guaranteed.
In Oracle8i Database, Oracle added considerable flexibility to the execution authority model of PL/SQL by offering the AUTHID clause. With AUTHID, you can continue to run your programs under the definer rights model described earlier, or you can choose AUTHID CURRENT_USER (invoker rights), in which case the programs run under the authority of the invoking (current) schema. Invoker rights is just one example of how PL/SQL has matured and become more flexible over the years.
1.2.4. Humble Beginnings, Steady Improvement
As powerful as SQL is, it simply does not offer the flexibility and power developers need to create full-blown applications. Oracle’s PL/SQL language ensures that we can stay entirely within the operating system-independent Oracle environment and still write highly efficient applications that meet our users’ requirements.
PL/SQL has come a long way from its humble beginnings. With PL/SQL 1.0, it was not uncommon for a developer to have to tell his or her manager, "You can’t do that with PL/SQL." Today, that statement has moved from fact to excuse. If you are ever confronted with a requirement and find yourself saying, "There’s no way to do that," please don’t repeat it to your manager. Instead, dig deeper into the language, or explore the range of PL/SQL packages offered by Oracle. It is extremely likely that PL/SQL today will, in fact, allow you to do pretty much whatever you need to do.
Over the years, Oracle has demonstrated its commitment to PL/SQL, its flagship proprietary programming language. With every new release of the database, Oracle has also made steady, fundamental improvements to the PL/SQL language itself. It has added a great variety of supplied (or built-in) packages that extend the PL/SQL language in numerous ways and directions. It has introduced object-oriented capabilities, implemented a variety of array-like data structures, enhanced the compiler to both optimize our code and provide warnings about possible quality and performance issues, and in general improved the breadth and depth of the language.
1.2.5. The Significance of Oracle Database 10g PL/SQL
Oracle Database 10g PL/SQL is impressive, but not particularly because it offers many new language features and extended capabilities. Instead, it demonstrates Oracle’s continuing strong commitment to supporting this language and the millions of PL/SQL developers and DBAs around the world who use it.
The PL/SQL development team spent over five years redesigning the compiler to support three critical capabilities:
-
Automatic, transparent optimization of code
-
Starting with Oracle Database 10g Release 1, Oracle automatically restructures our code to improve its performance. After extensive testing and benchmarking, Oracle estimates that the non-SQL portions of your PL/SQL program can run twice as fast as they did in Oracle9i Database.
-
Compile-time warnings
-
Starting with Oracle Database 10g Release 1, the compiler can detect potential runtime problems with your code, such as identifying lines of code that will never be run. This process, also known as lint checking, has long been desired by PL/SQL developers.
-
Preprocessor support
-
Starting with Oracle Database 10g Release 2, within your PL/SQL programs you can now specify conditional logic that is processed as your code is compiled. This "ifdef" feature allows you to, among other things, specify different code to be compiled for different versions of Oracle (which will come in handy primarily in future releases of Oracle).
These topics, as well as other new PL/SQL features in Oracle Database 10g, are described briefly later in this chapter and more fully in the appropriate chapters of the book.
In the next section, we take a look at some examples of PL/SQL programs that will familiarize you with the basics of PL/SQL programming.
Kaynak: Oracle PL/SQL Programming, Fourth Edition
belgesi-1151
