Previous | Table of Contents | Next

Page 173

CHAPTER 10

PL/SQL Fundamentals

In this chapter

Page 174

Understanding PL/SQL

PL/SQL is a Procedural Language extension to Oracle's version of ANSI standard SQL. SQL is a non-procedural language; the programmer only describes what work to perform. How to perform the work is left to the Oracle Server's SQL optimizer. In contrast, PL/SQL, like any third-generation (3GL) procedural language, requires step-by-step instructions defining what to do next.

Like other industry-standard languages, PL/SQL provides language elements for variable declaration, value assignment, conditional test and branch, and iteration. Like C or Pascal, it is heavily block-oriented. It follows strict scoping rules, provides parameterized subroutine construction, and like Ada, has a container-like feature called a package to hide or reveal data and functionality at the programmer's discretion. It is a strongly typed language; data type mismatch errors are caught at compile and runtime. Implicit and explicit data type conversions can also be performed. Complex user-defined data structures are supported. Subroutines can be overloaded to create a flexible application programming environment.

Additionally, because it is a procedural wrapper for SQL, the language is well integrated with SQL. Certain language features enable it to interact with the Oracle RDBMS, performing set and individual row operations. The more you know about writing SQL, the better designed your PL/SQL programs will be.

PL/SQL provides a feature called Exception Handling to synchronously handle errors and similar events that may occur during processing. You'll learn how to embed exception handlers in your PL/SQL code to deal with error conditions gracefully.

PL/SQL is not an objected-oriented language. It does have some features found in languages such as Pascal and Ada. If you're familiar with the syntax of Pascal, you will have no trouble learning PL/SQL. Unlike languages such as C and Pascal, pointers are not supported. PL/SQL is primarily a back-end development tool, where it interacts strictly with database tables and other database objects. Interaction with the operating system and external software components is handled through the supplied database packages.

PL/SQL is highly portable; it is standardized across all Oracle platforms. Because its data types are based on the database server's, the language is completely machine independent. You do not need to learn various flavors for UNIX, Windows NT, NetWare, and so on. A PL/SQL program will compile and run on any Oracle Server with no modifications required.

CAUTION
The Oracle Server imposes limitations on the size of a PL/SQL module, depending on the operating system. On NetWare 3.x it is limited to 32KB. For most flavors of UNIX and Windows NT, the module size is restricted to 64KB. Violating this limit can crash your database server or the server machine itself.

This portability also extends to 3GL programming languages. PL/SQL provides a standardized interface to various languages such as C and COBOL, via the Oracle-supplied precompilers. The precompilers support the ANSI standard for embedded SQL.

Page 175

Understanding the PL/SQL Engine

Before you look at PL/SQL as a language, you need to understand it in the executing environment.

Fitting into the Client/Server Environment

In a client/server configuration, the real bottleneck is typically the network. Connect a couple hundred users to the Oracle server via your compiled C, C++, Delphi, or COBOL program, and you'll have a very sluggish network system. The solution is to combine complex program segments, especially those performing reiterative or related SQL statements, into PL/SQL blocks. These blocks can be embedded in an OCI (Oracle Call Interface) program, or executed even more efficiently by moving them into the database itself as stored functions, procedures, and packages. Figure 10.1 shows the typical interaction between client applications with the Oracle server.

FIG. 10.1
A typical client/server
environment.


PL/SQL is executed by the PL/SQL engine. This engine is part of the database server. Figure 10.2 illustrates internally how a PL/SQL block is handled.

Whatever tool you use, such as Oracle SQL*Plus, the tool must submit the PL/SQL source text to the Oracle Server. The PL/SQL engine scans, parses, and compiles the code. The compiled code is then ready to be executed. During execution, any SQL statements are passed to the SQL Statement Executor component for execution. The SQL Statement Executor performs the SQL or DML statement. The data set retrieved by the query is then available to the PL/SQL engine for further processing.

One advantage of using a PL/SQL block to perform a set of SQL statements, versus sending them individually, is the reduction in network traffic. Figure 10.3 illustrates this idea.

This alone can substantially improve an application's performance. Additionally, the SQL/DML statements can be treated as a single transaction. If the entire transaction succeeds, then all the

Page 176

modifications to the database can be committed. If any part fails, the entire transaction can be rolled back. Because complex logic can be included in the PL/SQL block, and thereby executed on the server, client program size and complexity is reduced.

FIG. 10.2
The PL/SQL engine is a
component of the
Oracle database server.


FIG. 10.3
Grouping several SQL
statements into one PL/
SQL block reduces network
traffic.


Executing Stored SubprogramsA further refinement involves storing compiled, named PL/SQL blocks in the database. PL/SQL blocks will be referred to collectively in this chapter as stored subprograms or just subprograms. "Named" simply means the name of the subprogram that is included with its code, just like any C function or Pascal subroutine. Figure 10.4 illustrates how the PL/SQL engine calls stored subprograms.

Page 177

FIG. 10.4
The PL/SQL engine
runs stored subpro-
grams.


These subprograms can perform complex logic and error handling. A simple anonymous or unnamed block (a block of PL/SQL code that isn't labeled with a name), embedded in a client application, can invoke these subprograms. This capability is generally referred to as a Remote Procedure Call (RPC). Subprograms can also call other subprograms. Because these subprograms are already compiled, and hopefully well tuned by the developer, they offer a significant performance improvement, as well as reduce application development by providing reusable building blocks for other applications or modules.

Shared SQL Areas Within the System Global AreaThe System Global Area (SGA) is a large chunk of memory allocated by the operating system to the Oracle Server. Within this memory, the Server maintains local copies of table data, cursors, user's local variables, and other sundry items.

When you compile any PL/SQL program, whether a named or unnamed block of code, the source and object code are cached in a shared SQL area. The space allocated to each PL/SQL block is called a cursor. The server keeps the cached program in the shared SQL area until it gets aged out, using a Least Recently Used algorithm. Any SQL statements inside the PL/SQL block are also given their own shared SQL area.

When a named subprogram is compiled, its source code is also stored in the data dictionary.

The code contained in a subprogram is reentrant; that is, it is shareable among connected users. When an unnamed PL/SQL block is submitted to the server for execution, the server determines whether it has the block in cache by comparing the source text. If the text is exactly identical, character for character, including case, the cached, compiled code is executed. The same is true for SQL statements; if the query text is identical, the cached, parsed code can simply be executed. Otherwise, the new statement must be parsed first. By sharing executable code, a server-based application can achieve substantial memory savings, especially when hundreds of clients are connected.

Private SQL AreasIf several users are executing the same block of code, how does the server keep their data separated? Each user's session gets a private SQL area. This hunk of

Previous | Table of Contents | Next