Page 178
memory contains a private copy of the variable data included in the subprogram. A private SQL area is also allocated for any SQL statements within the PL/SQL block. Figure 10.5 represents the scheme.
FIG. 10.5
Shared and private SQL
areas within the SGA.
The first time a stored subprogram is referenced, it must be loaded from the database into the SGA. Once loaded, it is available to every user. As long as it continues to be referenced by any user (not necessarily the one who first called it), the subprogram will remain in memory. Packages work the same way, except that a whole set of subprograms may be included in the package. The initial load may take longer, but now all these subprograms are available; there is no further disk hit. When a set of subprograms are expected to be invoked, you're better off having them all load at once. Packages offer this mechanism.
The PL/SQL engine may also be embedded in certain Oracle tools, such as SQL*Forms 4.5 and other Developer/2000 development tools. The main advantage here is programming in a familiar language. The client program can perform the computations contained in the local PL/SQL block, and send the SQL to the server or invoke stored PL/SQL portions. Again, this supports code reuse and simplifies the client program by handling portions of complex logic directly on the database server.
The developer must consciously decide how much complexity to hide on the server versus how much to keep in the client program. After years of developing client/server applications, the author suggests some guiding principles to follow:
Page 179
NOTE |
Clamage's Rule of Maintainability: A highly maintainable program module is one that requires little or no changes when the requirements for that module changes. An unmaintainable program is one that requires substantial modifications in order to incorporate new requirements. The above principles will help guide you toward the goal of building highly maintainable software. |
Use stored subprograms to implement these principles. By providing standard libraries of stored subprograms, multiple and future client applications can take advantage of earlier, completed development. For example, business rules can be implemented as stored subprograms. If a rule changes, for example, how to compute a sales commission, just modify and recompile the appropriate stored program. Any dependent client programs need not change or even be recompiled, provided the stored subprogram's interface remains unchanged.
Packages are a good way to increase a subprogram's generality by allowing the subprogram name to be overloaded. Overloading simply means giving the same subprogram name to a set of code, usually with different data types or different numbers of arguments in the parameter list. For example, the addition operator is overloaded in nearly all computer languages to handle both integer and floating point arithmetic.
Stop for a minute and think about how you can use PL/SQL in your everyday tasks. You may not be involved in application development. As a DBA, you have to perform steady maintenance on the database. You can use PL/SQL to make your life easier.
Page 180
Some SQL*Plus scripts can actually generate another script, such as listing the number of rows for every table in a tablespace. However, the scripts could be rewritten in PL/SQL, compiled, and stored in the database; thereby running much faster, without writing the intermediate script to a file.
Writing these kinds of scripts is also somewhat difficult; a PL/SQL script is more straightforward and easier to document and maintain. Additionally, a compiled PL/SQL program is readily shared among DBAs (especially if you're in a "24 x 7" shop). Instead of searching the hard drive for scripts, everyone will always know where they are and how to run them.
Stored subprograms can take parameters, which makes them extremely flexible. You can even write SQL*Plus front ends for them, collect user input and pass these values in, without having to know the order and types of the parameters.
You probably have a good set of SQL scripts that give you information about your database in action. But after you understand PL/SQL, you'll envision a whole new set of programs that give you additional information on database performance, storage, user load, locks, and so on. PL/SQL eliminates the restrictions and limitations of plain SQL scripts.
You can automate many tasks, running them at set intervals by using the supplied packages. You can send system or application data to external performance monitorswritten, perhaps, in C++ or Delphiand use their powerful reporting and graphing features to display this information in real-time.
Some tools, such as Visual Basic, are easy to use. With the additional capabilities of PL/SQL, you can develop visual tools to simplify tasks such as running EXPLAIN PLAN and graphically viewing indexes and constraints on tables. Several upscale third-party tools were developed this way.
You can write PL/SQL programs that use the data dictionary, for example, to show detailed index information for any particular table, even if it's in another schema. This is great if you're not logged on as SYS or SYSTEM and you need some data dictionary information, and you don't want to have to reconnect. This is especially helpful to developers, who always need to access that kind of information. You can reverse-engineer any database object using PL/SQL, rebuilding a syntactically correct statement to re-create the object. This will save you the trouble of manually updating your scripts every time you alter a table or grant a new privilege.
If composing a large, complex SQL statement is difficult, you can break it up into smaller parts and run them together in a PL/SQL script. This is preferable to trying to join more than seven tables, after which the SQL Executor seems to bog down. Multi-table joins can be improved dramatically by doing the Master table lookups separately. Plus, it's easier to validate the output of a script when you know the reusable code in it is already working correctly.