Oracle PL/SQL Best Practices

Name

MOD-01: Encapsulate and name business rules and formulas behind function headers.

Synopsis

This is one of the most important best practices you will ever read—and, I hope, follow. The one aspect of any software project that never changes is that stuff always changes. Business requirements, data structures, user interfaces: all these things change and change frequently. Your job as a programmer is to write code that adapts easily to these changes.

So whenever you need to express a business rule (such as, “Is this string a valid ISBN?”), put it inside a subroutine that hides the individual steps (which might change) and returns the results (if any).

And whenever you need a formula (such as, “the total fine for an overdue book is the number of days overdue times $.50”), express that formula inside its own function.

Example

Suppose that you must be at least 10 years old to borrow books from the library. This is a simple formula and very unlikely to change. I set about building the application by creating the following trigger:

CREATE OR REPLACE TRIGGER are_you_too_young
   AFTER insert OR update 
   ON borrower FOR EACH ROW
BEGIN
   IF :new.date_of_birth > 
         ADD_MONTHS (SYSDATE, -12 * 10)
   THEN
      RAISE_APPLICATION_ERROR (
         -20703, 
        'Borrower must be at least 10 yrs old.');
   END IF;
END;
/

Later, while building a batch-processing script that checks and loads over 10,000 borrower applications, I include the following check in the program:

BEGIN ... IF ADD_MONTHS (SYSDATE, -122) > rec.date_of_birth THEN ...