1.1 What Is PL/SQL?
PL/SQL stands for "Procedural Language extensions to SQL." PL/SQL is available primarily as an "enabling technology" within other software products; it does not exist as a standalone language. You can use PL/SQL in the Oracle relational database, in the Oracle Server, and in client-side application development tools, such as Oracle Forms. PL/SQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to this standard relational database language. As you can see from the following code example, PL/SQL allows you to combine SQL statements with "standard" procedural constructs. This single program can either insert a company into or delete a company from the database. It relies on the IF statement (not a SQL statement) to determine which action to take:
PROCEDURE maintain_company
(action_in IN VARCHAR2,
id_in IN NUMBER,
name_in IN VARCHAR2 := NULL)
IS
BEGIN
IF action_in = 'DELETE'
THEN
DELETE FROM company WHERE company_id = id_in;
ELSIF action_in = 'INSERT'
THEN
INSERT INTO company (company_id, name)
VALUES (id_in, name_in);
END IF;
END;
PL/SQL is an unusual -- and an unusually powerful -- programming language. You can write programs that look just like traditional 3GL modules, but you can also include calls to SQL statements, manipulate data through cursors, and take advantage of some of the newest developments in programming languages. PL/SQL supports packages which allow you to perform object-oriented design. PL/SQL provides a powerful mechanism for trapping and, with exception handlers, resolving errors. The tight integration of PL/SQL with SQL provides developers with the best of both worlds -- declarative and procedural logic.
shows how PL/SQL fits within the client-server architecture of Oracle-based applications. It shows both an Oracle Forms client and a non-Oracle tool client, both executing against an Oracle Server database. Notice that the Oracle Forms client makes use of two versions of PL/SQL:
PL/SQL Release 1.1: a client-side PL/SQL engine that allows the application to execute local PL/SQL programs
PL/SQL Release 2.X: the server-based PL/SQL engine that executes stored programs
The third-party tool executes calls to stored programs, which are then run on the server.
In the above figure:
PL/SQL within the Oracle client-server architecture
Because PL/SQL is used both in the database (for stored procedures and database triggers) and in the application code (to implement logic within a form, for example), you can leverage the same programming language for both client-side and server-side development. You can even move programs from one component of the configuration to another.[1] For example, you might decide that a function which was originally coded for a single screen could be shared by all of the modules in an application. To make this happen, you simply move that function from the client-side screen component to the server-side database environment. You have to change neither the PL/SQL code nor any of the programs that call that function.
[1] As long as there are no conflicts between different versions of PL/SQL.
Of course, as an underlying technology in the Oracle constellation of products, PL/SQL is just one element of the total programming experience. In fact, building an Oracle-based application requires a combination of technology and techniques, as you'll see in the next section.
1.2 The Concept of Programming in Oracle Applications
The whole idea of "programming" in Oracle-based applications has a somewhat different meaning from what you might be used to in a third-generation, or even a wholly procedural fourth-generation, language.
Programming in Oracle generally entails a blending of technologies and programming styles. Suppose, for example, that you are building an application to maintain invoice information. You will first design and create a database, relying mostly on the declarative SQL language. However, you might also create database triggers, coded in PL/SQL, to implement complex business rules at the database level. From there you build the screens, charts, and reports that make up the user interface. You could use third-party tools, or you could rely on Oracle Developer/2000 (formerly the Cooperative Development Environment, or CDE), with such products as Oracle Forms, Oracle Graphics, and Oracle Reports.
Each of the Oracle Developer/2000 tools employs a nondeclarative or "fill-in-the-blanks" approach to development. Without writing any code in the traditional sense, you create a full-functioned screen with buttons, radio groups, menus, and pictures. These objects are, more or less, the graphical pieces of the program that the user views, touches, and acts upon. They are somewhat different in each tool. In Oracle Forms, these objects include items on the screen (buttons, radio groups, text fields), blocks (which correspond to tables in the database), and visual attributes (which control the way items appear to the user). In Oracle Reports, these objects include fields in the report, repeating frames of data, and parameters that are entered by the user to initiate the report. While there isn't any code per se for this part of your system, you have created objects that will be manipulated by the code you do write in PL/SQL.
Once you have built the graphical objects in your tools, you then associate PL/SQL procedural code with those objects. How? You use various kinds of triggers associated with those objects. Oracle Forms, for example, employs a sophisticated event-driven model, which is very conducive to programming in the GUI environment. You attach event triggers, such as When-Button-Pressed, to an object. Then, no matter how the user triggers an event (with the movement of a mouse, with the press of a key, or as an indirect result of another event), that trigger fires and executes the PL/SQL code you have written. The only way to apply a PL/SQL procedure or function to an object in Oracle Forms is through a trigger. All triggers are implemented in PL/SQL.
Conceptually, there are at least five layers of "code" in an Oracle Forms application, as Table 1.1 shows. Each of these different kinds of code requires a different approach to programming.
Code Level............................ Role of Code in Application
Graphical objects................. Accessed directly by the user. (You can, of course, create
objects which are "view only.")
Event triggers...................... Triggered by user activity; triggers are usually attached to
events associated with a specific graphical object
Form-based PL/SQL ........Executed by a trigger; implements nondeclarative, non-default
functions in an application
RDBMS-based PL/SQL..... Stored procedures and database triggers executed in response
to actions initiated in the form
SQL ......................................The data definition and data manipulation statements used to
create and maintain the data in the Oracle Server
You may find the blend of technologies and programming environments that make up Oracle-based applications to be a challenge. If you are an experienced programmer, you might have worked for years in a 100% procedural language environment like COBOL. There, you've learned methodologies for designing and building your code. If you are asked to review a program, you can display those lines of text on a screen and do your analysis. In Oracle, you cannot.
If you are new to computer programming, the situation can be even worse. Introduced to powerful new technologies without any formal grounding in development, you may find yourself churning out screen after screen, report upon report, with little regard for code reusability, quality assurance, and development methodologies. And why should you? No one ever told you these issues were important. Instead, you received a week of training and were expected to move immediately to "rapid application development."
How can you apply your knowledge and experience to the world of Oracle? No matter where you came from, you will need to reconcile the different kinds of code and programming techniques. In this book we will try to help you do that.
1.3 The Origins of PL/SQL
Oracle has a history of leading the software industry in providing declarative, nonprocedural 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, precisely by relying heavily on a "paint-your-screen" approach in which extensive default capabilities allow developers to avoid heavy customized programming efforts.
In Oracle's early years, this declarative approach, combined with its groundbreaking relational technology, was enough to satisfy developers. But as the industry matured, expectations and requirements became more demanding. Developers needed to get under the skin of the products. They needed to build complicated formulas, exceptions, and rules into their forms and database procedures.
In 1991, Oracle Corporation released Oracle Version 6.0, a major advance in its relational database technology. A key component of Oracle Version 6.0 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. SQL*Forms V3.0 incorporated the PL/SQL engine for the first time on the tool 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. In other words, you could not store procedures or functions for execution at some later time. You could not construct a modular application or store complex business rules in the server. On the client side, SQL*Forms V3.0 did allow you to create procedures and functions, though support for functions was not documented and 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.
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 to offer 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.3.1 Improved Application Portability with PL/SQL
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 which needed the more sophisticated and granular control offered by host languages like COBOL, C, and FORTRAN. As soon as a developer stepped outside of 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 which can be handled entirely in operating-system independent programming tools.
1.3.2 Improved Execution Authority and Transaction Integrity with PL/SQL
Even more fundamental an issue than portability was that of execution authority. The RDBMS and SQL language give you the capability to tightly control access to, and changes in, any particular table. For example, with the GRANT command you can make sure that only certain roles and users have the ability to 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.
Let's look at an example. In a typical banking transaction, you might need to transfer funds from account A to account B. The balance of account B must be incremented, and that of account A decremented. Table access is necessary, but not sufficient, to guarantee that both of these steps are always performed by all programmers who write code to perform a transfer. Without stored objects, the best you can do is require extensive testing and code review to make sure that all transactions are properly constructed. With stored objects, on the other hand, you can guarantee that a funds transfer either completes successfully or is completely rolled back -- regardless of who executes the process.
The secret to achieving this level of transaction integrity is the concept of execute authority (also known as run authority). Instead of granting to a role or user the authority to update a table, you grant privileges to only execute a procedure. This procedure controls and provides access to the underlying data structures. The procedure is owned by a separate Oracle RDBMS account, 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 transfer transaction. The only way a program (whether it's an Oracle Forms application or a Pro*C executable) can execute the transfer is through the procedure. The result is that transaction integrity is guaranteed.
The long-term objective of PL/SQL, realized with PL/SQL Version 2.0, was to provide a programming environment which would allow developers both to create reusable, callable modules and also to grant authority to those modules. Given Oracle Corporation's strategic technical investment in relational databases, it's only logical that these programs would reside in the database itself. This way, the same data dictionary that controls access to data could control access to the programs.
PL/SQL has come a long way from its humble beginnings. It is a rapidly maturing programming language that will play a prominent role in all future Oracle application development and database management efforts.
No comments:
Post a Comment