Oracle PL/SQL Best Practices

Name

PKG-02: Provide well-defined interfaces to business data and functional manipulation using packages.

Synopsis

Humans can handle only so much complexity at once. The details and nuances of any decent-sized application overwhelm the human mind. Use packages to hide—or at least attempt to organize—the mind-boggling complexity. Expose the underlying data and business rules in an orderly and manageable fashion through the package specification.

This technique is crucially important when implementing core business rules in your application. Every such rule should be hidden behind a function and defined in the appropriate package.

In addition, hide all the SQL for a given table or business entity behind a package interface (this process is called table encapsulation). Rather than write an INSERT statement in your program, call an insert procedure. See [SQL-15: Encapsulate INSERT, UPDATE, and DELETE statements behind procedure calls.] for more details.

Example

Let’s look at a simple example: building a timing utility. The DBMS_UTILITY.GET_TIME built-in function returns the number of hundredths of seconds that have elapsed since an arbitrary point in time. You call it twice and subtract the difference to calculate elapsed time (down to the hundredth of a second), as in:

DECLARE
   l_start PLS_INTEGER;
   l_end PLS_INTEGER;
BEGIN
   l_start := DBMS_UTILITY.GET_TIME;
   overdue.calculate_fines;
   l_end := DBMS_UTILITY.GET_TIME; pl ('Calculated fines in ' || (l_end - l_start) / 100 || ' seconds'); END; ...