Deterministic Functions

A function is called deterministic if it returns the same result value whenever it is called with the same values for its arguments. The following function (a simple encapsulation on top of SUBSTR) is such a function:

CREATE OR REPLACE FUNCTION betwnStr ( string_in IN VARCHAR2,start_in IN INTEGER, end_in IN INTEGER ) RETURN VARCHAR2 ISBEGIN RETURN ( SUBSTR ( string_in, start_in, end_in - start_in + 1));END;

As long as I pass in, for example, "abcdef" for the string, 3 for the start, and 5 for the end, betwnStr will always return "cde". Now, if that is the case, why not have Oracle save the results associated with a set of arguments? Then when I next call the function with those arguments, it can return the result without executing the function!

You can achieve this effect by adding the DETERMINISTIC clause to the function's header, as in the following:

CREATE OR REPLACE FUNCTION betwnStr ( string_in IN VARCHAR2,start_in IN INTEGER, end_in IN INTEGER ) RETURN VARCHAR2 DETERMINISTIC ISBEGIN RETURN ( SUBSTR ( string_in, start_in, end_in - start_in + 1));END;

The decision to use a saved copy of the function's return result (if such a copy is available) is made by the Oracle query optimizer. Saved copies can come from a materialized view, a function-based index, or a repetitive call to the same function in the same SQL statement.

You must declare a function DETERMINISTIC in order for it to be called in the expression of a function-based index, or from the query of a materialized view if that view is marked REFRESH FAST or ENABLE QUERY REWRITE.

Oracle has no way of reliably checking to make sure that the function you declare to be deterministic actually is free of any side effects. It is up to you to use this feature responsibly. Your deterministic function should not rely on package variables, nor should it access the database in a way that might affect the result set.

16.9 Go Forth and Modularize!

As the PL/SQL language and Oracle tools mature, you will find that you are being asked to implement increasingly complex applications with this technology. To be quite frank, you don't have much chance of success with such large-scale projects without an intimate familiarity with the modularization techniques available in PL/SQL.

While this book cannot possibly provide a full treatment of modularization in PL/SQL, it should give you some solid pointers and a foundation on which to build. There is still much more for you to learn: the full capabilities of packages, the awesome range of package extensions that Oracle now provides with the tools and database, the various options for code reusability, and more.

Behind all that technology, however, you must develop an instinct, a sixth sense, for modularization. Develop a deep and abiding allergy to code redundancy and to the hardcoding of values and formulas. Apply a fanatic's devotion to the modular construction of true black boxes that easily plug-and-play in and across applications.

You will find yourself spending more time in the design phase and less time in debug mode. Your programs will be more readable and more maintainable. They will stand as elegant testimonies to your intellectual integrity. You will be the most popular kid in the class. But enough already! I am sure you are properly motivated.

Go forth and modularize!

Chapter 17. Packages

A package is a grouping or packaging of PL/SQL code elements. Packages provide a structure (both logically and physically) in which you can organize your programs and other PL/SQL elements such as cursors, TYPEs, and variables. They also offer significant, unique functionality, including the ability to "hide" logic and data from view, and to define and manipulate "global" or session-persistent data.

In the first two editions of this book, I wrote that "Packages are among the least understood and most underutilized features of PL/ SQL. That is a shame, because the package structure is also one of the most useful constructs for building well-designed PL/SQL-based applications." Fortunately, only the second of these statements is still true. Over the years, many developers have come to recognize and take advantage of the power of packages.

17.1 Why Packages?

The package is a powerful and important element of the PL/SQL language. It should be the cornerstone of any complex application development project. What makes the package so powerful and important? Consider the following advantages of the use of packages:

Easier enhancement and maintenance of applications

As more and more of the production PL/SQL code base moves into maintenance mode, the quality of PL/SQL applications will be measured as much by the ease of maintenance as it is by overall performance. Packages can make a big difference in this regard. From data encapsulation (hiding all calls to SQL statements behind a procedural interface to avoid repetition), to enumerating constants for literal or "magic" values, to grouping together logically related functionality, package-driven design and implementation lead to far fewer points of failure in an application.

Improved overall application performance

By using packages, you can improve the performance of your code in a number of ways. Persistent package data can dramatically improve the response time of queries by caching (and not requerying) static data. Oracle's memory management with packages optimizes access to compiled code (see Chapter 20 for more details).

Ability to shore up application or built-in weaknesses

It is quite straightforward to construct a package on top of existing functionality where there are drawbacks. (Consider, for example, the UTL_FILE and DBMS_OUTPUT built-in packages in which crucial functionality is badly or partially implemented.) You don't have to accept these weaknesses; instead, you can build your own package on top of Oracle's to correct as many of the problems as possible. For example, the do.pkg script we'll look at later in this chapter offers a substitute for the DBMS_OUTPUT.PUT_LINE built-in that adds an overloading for the XMLType datatype. Sure, you can get some of the same effect with standalone procedures or functions, but overloading and other package features make this approach vastly preferable

Packages are conceptually very simple. The challenge, I have found, is to figure out how to fully exploit them in an application. As a first step, we'll take a look at a simple package and see how, even in that basic code, we can reap many of the benefits of packages. Then we'll look at the special syntax used to define packages.

Before diving in, however, I would like to make an overall recommendation:

Always construct your application around packages; avoid standalone procedures and functions. Even if today you think that only one procedure is needed for a certain area of functionality, in the future you will almost certainly have two, then three, and then a dozen. At which point you will find yourself saying, "Gee, I should really collect those together in a package!" Which is fine, except that now you have to go back to all the invocations of those unpackaged procedures and functions and add in the "PACKAGE." prefix. So start with a package and save yourself the trouble!