22.3 Take Full Advantage of Local Modularization

A local module is a procedure or function that is defined within the declaration section of another module. The scope of a local module is the module in which it is declared. It is invisible to all other modules, and can be called only from within that defining module.

Few PL/SQL developers are aware of the local module feature, and fewer yet take full advantage of this capability. Yet I can think of few other aspects of the language that are more important to constructing clean, elegant, easily maintained programs. I strongly encourage you to use local modules at every possible opportunity, and offer several examples in this section to highlight their usefulness.

Local modules are very handy ways to "normalize" redundant code inside a program. If you perform the same calculation over and over again in a module, don't hardcode the calculation repeatedly. Instead, place it in its own function or procedure and then call that module. The power of such an approach is clear when you examine the following program. In the format_data procedure, I extract a value from the rg_sales record group, divide it by projected sales, and convert to a formatted string -- over and over again. This code is taken from a production Oracle Forms application, and is actually just a small portion of the full set of more than two dozen repetitive calculations.

PROCEDURE format_data
   (projected_sales_in IN NUMBER, year_in IN INTEGER)
IS
   /* Declare local variables for calculations. */
   total_cost NUMBER (9);
   gross_profit NUMBER (9);
   crew_labor NUMBER (9);
   mgmt_labor NUMBER (9);
BEGIN
   :owner.total_cost_pc :=
      TO_CHAR((net_present_value ('total_sales', year_in) /
               projected_sales_in * 100),'999.99');
   :owner.gross_profit_pc :=
      TO_CHAR((net_present_value ('gross_profit', year_in) /
               projected_sales_in * 100),'999.99');
   :owner.crew_labor_pc :=
      TO_CHAR((net_present_value ('crew_labor', year_in) /
               projected_sales_in * 100),'999.99');
   :owner.mgmt_labor_pc :=
      TO_CHAR((net_present_value ('mgmt_labor', year_in) /
               projected_sales_in * 100),999.99');
END;

I suppose that when you work in a Windows environment, it isn't necessarily such a big deal to write code like this. You cut and paste, cut and paste ... who knows? You might even use Microsoft Recorder or some other utility to automate the process. However you manage it, though, you still end up with lots of repetitions of the same fragment of code. You still end up with code that is hard to maintain.

Because I have exposed the way I perform the calculation, I must upgrade each distinct calculation whenever a change is required (different numeric format, different numeric formula, etc.). If, on the other hand, I hide the calculation behind the interface of a callable module, then the calculation is coded only once. With the help of a local module, the format_data procedure is transformed as shown in this example:

PROCEDURE format_data
   (projected_sales_in IN NUMBER, year_in IN INTEGER)
IS
   total_cost     NUMBER (9);
   gross_profit   NUMBER (9);
   crew_labor     NUMBER (9);
   mgmt_labor     NUMBER (9);

   /*----------------------- Local Module ----------------------*/
   FUNCTION npv (column_in IN VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      RETURN
         TO_CHAR((net_present_value ('' || column_in, year_in) /
                  projected_sales_in * 100),'999.99');
   END;

BEGIN
   /* Perform direct, readable assignments using the function. */
   :owner.total_cost_pc     := npv ('total_cost');
   :owner.gross_profit_pc := npv ('gross_profit');
   :owner.crew_labor_pc   := npv ('crew_labor');
   :owner.mgmt_labor_pc   := npv ('mgmt_labor');
END;

The total amount of code is less than before and the body of format_data is so much more readable. Notice how the name of the procedure actually helps make the code self-documenting. The procedure name states precisely the nature of the calculation performed. Any additional documentation would in itself be redundant. With npv, if I ever need to change the format mask or the formula, I simply make a change to the npv function and then recompile. No fuss, no muss.

Notice that it doesn't make any sense to create npv as a standalone module outside of the format_data procedure. This calculation is very specific to the format_data program. Because no other module would ever call it, you needn't clutter up your stored procedure environment or your Oracle Forms program unit listing with this module-specific utility.

I have found that few developers are aware of the ability to create local modules. I have also found that these modules-within-a-module play an important role in allowing me to write well-structured, elegant programs.

These days it seems that whenever I write a program with more than 20 lines, and with any complexity whatsoever, I end up creating several local modules. It helps me see my way through to a solution in the following ways:

• Conceptualizing my code at a higher level of abstraction by assigning a name to a whole sequence of statements.

• Performing top-down design and step-wise refinement of my requirements.

• Extracting local modules at a later date, thus making truly independent, reusable procedures or functions if the need arises.

Take a look at any of your more complex programs and I guarantee you will quickly identify segments of the code that would serve you better bundled into a local module.