#

| Chapter 15 Procedures and Functions |

## 15.7 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 only defined within the parent PL/SQL block. It cannot be called by any other PL/SQL blocks defined outside of that enclosing block.

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 a local 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.

You can define both procedures and functions in the declaration section of any kind of PL/SQL block, be it anonymous, procedure, or function. You may not, however, define anonymous blocks (how would you call them?) or packages (maybe in a future release) in a declaration section.

The rest of this section explores the benefits of local modules and offers a number of examples.

### 15.7.1 Benefits of Local Modularization

There are two central reasons to create local modules:

- 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 the impact by 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. 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.

- 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 to make it easier to follow the logic of the main body of the parent module.

### 15.7.2 Reducing Code Volume

Let's take a look at an example. 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 in code size 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 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 below:

>     PROCEDURE calc_percentages (tot_sales_in IN NUMBER)
>     IS
>        /* Define a function right inside the procedure! */
>        FUNCTION char_format (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 := char_format (sales_pkg.food_sales);
>        :profile.service_sales_stg := char_format (sales_pkg.service_sales);
>        :profile.toy_sales_stg := char_format (sales_pkg.toy_sales);
>     END;

All 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.

### 15.7.3 Improving Readability

Suppose I have a series of WHILE loops (some nested) whose bodies contain a series of complex calculations and deep nestings of conditional logic. Even with extensive commenting, it can be difficult for a person to follow the program flow over several pages, particularly when the END IF or END LOOP of a given construct is not even on the same page as the IF or LOOP statement that began the construct.

If you pull out sequences of related statements, place them in one or more local modules, and then call those modules in the body of the program, the result is a program that can literally document itself. The assign_workload procedure offers a simplified version of this scenario, which still makes clear the gains offered by local modules:

>     PROCEDURE assign_workload (department_in IN NUMBER)
>     IS
>        /* Declare local variables first. */
>        avg_workload NUMBER;
>        case_number  NUMBER;

>        /*-----------------------Local Modules--------------------------*/

>        /* Define all the major tasks for workload assignment. */
>        PROCEDURE assign_next_open_case
>           (employee_id_in IN NUMBER, case_out OUT NUMBER)
>        IS BEGIN ... END;

>        FUNCTION average_cases (department_id_in IN NUMBER) RETURN NUMBER
>        IS BEGIN ... END;

>        FUNCTION caseload (employee_id_in IN NUMBER) RETURN NUMBER
>        IS BEGIN ... END;

>        FUNCTION next_appointment (case_id_in IN NUMBER) RETURN DATE
>        IS BEGIN ... END;

>        PROCEDURE schedule_case
>           (employee_id_in IN NUMBER, case_in IN NUMBER, date_in IN DATE)
>        IS BEGIN ... END;
>     /*
>     || Now the execution section of assign_workload:
>     */
>     BEGIN
>        avg_workload := average_cases (department_in);
>        FOR emp_rec IN (SELECT employee_id
>                          FROM employee
>                         WHERE department_id = department_in)
>        LOOP
>           /* If workload below average, assign next open case. */
>           IF caseload (emp_rec.employee_id) < avg_workload
>           THEN
>              assign_next_open_case (emp_rec.employee_id, case_number);
>              schedule_case (emp_rec.employee_id,
>                             case_number,
>                             next_appointment (case_number));
>           END IF;
>        END LOOP
>     END assign_workload;

The assign-workload procedure has five local modules:

>     assign_next_open_case
>     average_cases
>     caseload
>     next_appointment
>     schedule_case

For the purposes of understanding the logic behind assign_workload, it doesn't really matter what code is executed in each of them. I can rely simply on the names of those modules to read through the main body of this program. Even without any comments, a reader can still gain a clear understanding of what each module is doing. Of course, if you want to rely on named objects to self-document your code, you'd better come up with very good names for the functions and procedures.

### 15.7.4 Bottom-Up Reading

Although it is not evident from the preceding example, one drawback of the extensive use of local modules is that the execution section of the program is pushed all the way to the bottom of the module's body. Most programmers are used to scanning a program's code from top to bottom. In trying to understand code with local modules, you should move to the execution section of the parent module and read that first to gain an understanding of the big picture. You can then move back up to dive into the details of the local module.

### 15.7.5 Scope of Local Modules

This modularized declaration section looks a lot like the body of a package, as you will see in Chapter 16. A package body also contains definitions of modules. The big difference between local modules and package modules is their scope: local modules can be called only from within the block in which they are defined. Package modules can -- at a minimum -- be called from anywhere in the package. If those modules are also listed in the package specification, they can be called by any other program in your application.

You should use local modules only to encapsulate code that does not need to be called outside of the current program. Otherwise, go ahead and create a package!

### 15.7.6 Spruce Up Your Code with Local Modules!

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 much more easily. I can conceptualize my code at a higher level of abstraction by assigning a name to a whole sequence of statements. I can perform top-down design and stepwise refinement of my requirements. Finally, by modularizing my code even within a single program, I make it very easy, if the need arises, to later extract a local module and make it a truly independent, reusable procedure or function.

I hope that as you read this, a program you have written (or one with which you are currently wrestling) comes to mind. Perhaps, you are thinking, you can go back and consolidate some repetitive code, clean up the logic, make the program actually understandable to another human being. Don't fight the urge. Go ahead and modularize your code.

15.6 Parameters |  | 15.8 Module Overloading

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.