16.5 Local Modules

A local module is a procedure or function that is defined in the declaration section of a PL/SQL block (anonymous or named). This module is considered local because it is defined only within the parent PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside that enclosing block.

Figure 16-4 shows how blocks that are external to a procedure definition cannot "cross the line" into the procedure to directly invoke any local procedures or functions.

Figure 16-4. Local modules are hidden and inaccessible outside the program

The syntax for defining the procedure or function is exactly the same as that used for creating standalone modules.

The following anonymous block, for example, declares alocal procedure:

DECLARE

PROCEDURE show_date (date_in IN DATE) IS

BEGIN

DBMS_OUTPUT.PUT_LINE (TO_CHAR (date_in, 'Month DD, YYYY');

END;

BEGIN

...

END;

Local modules must be located after all of the other declaration statements in the declaration section. You must declare your variables, cursors, exceptions, types, records, tables, and so on before you type in the first PROCEDURE or FUNCTION keyword.

The following sections explore the benefits of local modules and offer a number of examples.

16.5.1 Benefits of Local Modularization

There are two central reasons to create local modules:

To reduce the size of the module by stripping it of repetitive code

This is the most common motivation to create a local module; you can see its impact in the next example. The code reduction leads to higher code quality because you have fewer lines to test and fewer potential bugs. It takes less effort to maintain the code because there is less to maintain. And when you do have to make a change, you make it in one place in the local module and the effects are felt immediately throughout the parent module.

To improve the readability of your code

Even if you do not repeat sections of code within a module, you still may want to pull out a set of related statements and package them into a local module. This can make it easier to follow the logic of the main body of the parent module.

The following sections examine these benefits.

16.5.1.1 Reducing code volume

Let's look at an example of reducing code volume. The calc_percentages procedure takes numeric values from the sales package (sales_pkg), calculates the percentage of each sales amount against the total sales provided as a parameter, and then formats the number for display in a report or form. The example you see here has only three calculations, but I extracted it from a production application that actually performed 23 of these computations!

PROCEDURE calc_percentages (tot_sales_in IN NUMBER)

IS

BEGIN

:profile.food_sales_stg :=

TO_CHAR ((sales_pkg.food_sales / tot_sales_in ) * 100,

'$999,999');

:profile.service_sales_stg :=

TO_CHAR ((sales_pkg.service_sales / tot_sales_in ) * 100,

'$999,999');

:profile.toy_sales_stg :=

TO_CHAR ((sales_pkg.toy_sales / tot_sales_in ) * 100,

'$999,999');

END;

This code took a long time (relatively speaking) to write, is larger than necessary, and is maintenance-intensive. What if I need to change the format to which I convert the numbers? What if the calculation of the percentage changes? I will have to change each of the individual calculations.

With local modules, I can concentrate all the common, repeated code into a single function, which is then called repeatedly in calc_percentages. The local module version of this procedure is shown here:

PROCEDURE calc_percentages (tot_sales_in IN NUMBER)

IS

/* Define a function right inside the procedure! */

FUNCTION pct_stg (val_in IN NUMBER) RETURN VARCHAR2

IS

BEGIN

RETURN TO_CHAR ((val_in/tot_sales_in ) * 100, '$999,999');

END;

BEGIN

:profile.food_sales_stg := pct_stg (sales_pkg.food_sales);

:profile.service_sales_stg := pct_stg (sales_pkg.service_sales);

:profile.toy_sales_stg := pct_stg (sales_pkg.toy_sales);

END;

All of the complexities of the calculation, from the division by tot_sales_in to the multiplication by 100 to the formatting with TO_CHAR, have been transferred to the function pct_stg. This function is defined in the declaration section of the procedure. By calling this function from within the body of calc_percentages, the executable statements of the procedure are much more readable and maintainable. Now, if the formula for the calculation changes in any way, I make the change just once in the function and it takes effect in all the assignments.