Page 261
I use very few global variables beyond constants and record variables for various tables. I also like to put the declarations in a standard order, as shown in Listing 11.12. That way, I always know precisely where to look for things.
Packages offer several advantages over stored subprograms.
Use packages when you need programming constructs to be persistent; that is, continue to exist after an application finishes. When an application completes, all global data continues to be available to the next application until the user disconnects from the server. If you were debugging a series of programs, you could turn debugging on and leave it on for the duration. If several packages are working in concert, such as when you are compelled to break up a very large application, you absolutely want the data to continue its existence when the program context changes to a different package module.
Use packages to gather similar subprograms together, especially those with high coupling and cohesiveness. If a cursor is used repeatedly among several routines, definitely put them all together into one package. If one subprogram calls another, it's best to load them together into memory as part of a package.
Use packages to build large PL/SQL programs to run from a client. At one company, I built
an entire series of report applications to generate unnormalized data to be pulled into a
client-based, enterprise-wide reporting application. The reports ran much faster than the
COBOL programs they replaced. Another series of packages performed an entire suite of related,
complex calculations. One package was run after another as the data was massaged into
various intermediate forms, depending on where in the process it was. This was a critical
application that took hours to run. The set of PL/SQL packages were much more maintainable and
verifiable than the original COBOL programs, which took 10 times longer to run. I built in
automated debugging, error dumping, and report handling right from the start. Once these features
were designed and coded, using them was a snap, resulting in smaller overall code sizes.
Use packages to overload functionality to make it appear more generic. You'll still have to code separate routines, one for each flavor, but when the developers have to actually use them, they'll have an easier time remembering the names of things.
Use packages to standardize application features, such as debugging, error handling, data verification, and validation. Provide your developers with a standard set of master table lookup routines or table interface routines (for example, insert, update, delete). Be sure to carefully tune the implicit and explicit SQL statements; they'll likely be referenced quite heavily. For example, in the enterprise-wide reporting application, I discovered that the program error dumping routines had been invoked around 40,000 times in the course of just one month.
Page 262
I would generally avoid packaged subprograms for functions called from SQL statements because these have to guarantee they don't have side effects affecting the database or package state. Because of their added complexity, if something can be done more simply as a stored subprogram (outside a package), then keep things simple, in those situations where the additional effort is not justified.
Package contents are referenced by PL/SQL code outside the package using dot notation, as shown in Listing 11.14. Remember, dot notation is required to remove any ambiguity of an object's scope. In order to reference a packaged object, the package name must also be supplied, in the format <package name>.<package object>. If the code referencing an object is within the same scope, such as within the same package, then the qualifier is unnecessary.
Listing 11.14 testlib.sqlTesting the Execution of the Proposed Library Package
BEGIN DBMS_OUTPUT.put_line(`DEBUG status is initially: ` || LIB.bool_to_char(LIB.debug_status)); LIB.debug_on; DBMS_OUTPUT.put_line(`DEBUG status is now: ` || LIB.bool_to_char(LIB.debug_status)); LIB.debug_off; DBMS_OUTPUT.put_line(`DEBUG status is now: ` || LIB.bool_to_char(LIB.debug_status)); LIB.debug_toggle; DBMS_OUTPUT.put_line(`DEBUG status is finally: ` || LIB.bool_to_char(LIB.debug_status)); END; /
In order to make this a real test, first get completely out of your SQL*Plus session and then go back in. This will make absolutely sure that your session is truly fresh. Then SET SERVEROUTPUT ON and run the unnamed block shown in Listing 11.14. Notice that this block doesn't enable output; that bit of trivia is performed once in the package initialization code. The debug flag, whose value would normally default to NULL, is also reset initially. The server responds with the following:
DEBUG status is initially: FALSE DEBUG status is now: TRUE DEBUG status is now: FALSE DEBUG status is finally: TRUE PL/SQL procedure successfully completed. Now run it again. DEBUG status is initially: TRUE DEBUG status is now: TRUE DEBUG status is now: FALSE DEBUG status is finally: TRUE PL/SQL procedure successfully completed.
Page 263
Notice now that the initial debug flag value is TRUE. The value was carried over from one program run to the next. But if you reconnect, you effectively have a new session and the package initialization is performed again. Remember, the code is static in memory but each user gets a fresh, local set of private variables. Run a second SQL*Plus session and verify this behavior.
I leave to you as an exercise the specifying and coding of interface routines for the other private global variables.
As a hard-core, real-world example of the use of packages, let's examine a rather large, complex one. This package, rev_eng.sql, contains routines for reverse-engineering a schema. It's too big to list here, so I must rely on your innate curiosity to go pull it into a text editor or print it out.
This package is used to reverse-engineer a schema, types of database objects within a schema, or just one particular object with a schema. Its output is syntactically correct DDL statements for creating tables, indexes, constraints, views, synonyms, and grants on these objects. You run it from a SQL*Plus session. You can capture its output to an operating-system file by spooling to a file name. For more information on the SQL*Plus SPOOL command, see Chapter 8.
NOTE |
The first thing you might notice about this package is its size. It just barely fits under the UNIX/NT constraint of 64KB. If you're still running NetWare 3.x, don't bother trying to compile it in this form; it'll blow up. You'll have to break it up. The first obvious thing would be to put the specification in one file and the implementation in another. The other idea would be to put the grants in a separate package, which you could subsequently fill with a rev_users routine, rev_roles, and so on. |
TIP |
Develop PL/SQL coding standards (if you haven't already), especially documentation standards, and use them. These standards should be in writing and all developers should be compelled to use them. While it may take more time to develop code using these standards, the maintenance of the code is made much easier. |
The package starts out with a module, author description, and history comment. This is basic information that should be common to all packages. It is important to add some thoughtful commentary on the overall design and use of the package here.