Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: 15.6 ParametersChapter 15
Procedures and Functions
Next: 15.8 Module Overloading

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:

   PROCEDURE show_date (date_in IN DATE) IS
      DBMS_OUTPUT.PUT_LINE (TO_CHAR (date_in, 'Month DD, YYYY');

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:

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)
   :profile.food_sales_stg :=
      TO_CHAR ((sales_pkg.food_sales / tot_sales_in ) * 100,
   :profile.service_sales_stg :=
      TO_CHAR ((sales_pkg.service_sales / tot_sales_in ) * 100,
   :profile.toy_sales_stg :=
      TO_CHAR ((sales_pkg.toy_sales / tot_sales_in ) * 100,

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)
   /* Define a function right inside the procedure! */
   FUNCTION char_format (val_in IN NUMBER) RETURN VARCHAR2
      RETURN TO_CHAR ((val_in/tot_sales_in ) * 100, '$999,999');
   :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);

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)
   /* 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:
   avg_workload := average_cases (department_in);
   FOR emp_rec IN (SELECT employee_id
                     FROM employee
                    WHERE department_id = department_in)
      /* If workload below average, assign next open case. */
      IF caseload (emp_rec.employee_id) < avg_workload
         assign_next_open_case (emp_rec.employee_id, case_number);
         schedule_case (emp_rec.employee_id,
                        next_appointment (case_number));
      END IF;
END assign_workload;

The assign-workload procedure has five local modules:


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.

Previous: 15.6 ParametersOracle PL/SQL Programming, 2nd EditionNext: 15.8 Module Overloading
15.6 ParametersBook Index15.8 Module Overloading

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference